I Asked ChatGPT for Intermediate SQL Challenges: Here's How I Solved Them

Last time I had to use any SQL was approximately a year ago at my data engineering internship at the Sheriff's office. I have SQL on my resume as one of my top skills, so I started to feel a little bit like a fraud and decided to solve some problems a couple days ago. I have failed, miserably. But I practiced and I think I remembered it enough that I am ready to challenge myself now. I will ask ChatGPT to generate me 3 intermediate SQL coding questions that I can get in a technical interview for a position like Data Analyst, Business Analyst, Data Engineer etc. I will show you EVERYTHING that is going through my mind while I am solving these questions, even the embarrassing ones so you all know that you are not alone. the question is formatted in this way:

• a table or multiple tables for the question,

• three parts which involves joins, grouping by, aggregating, and some window functions

• the first two parts will be easy to intermediate, and the last one will be more of an intermediate to advanced question type

I hope you are excited!!!! because I am about to be crushed by these questions. Here is the database schema:

Customer Support Performance Report Database

For the questions, we will assume that I work for a SaaS company. They track support tickets, agents, and ticket status changes.

Part 1:

For each agent, show:

agent_name | team | total_tickets

1. Only include agents who handled at least 5 tickets.

2. Sort by total_tickets descending.

Pre-thoughts: Now, without even looking at the tables, I know that there are agents, and there are tickets. The end result will be probably found by grouping by agents and counting the rows for all the tickets, and only including the rows that have a count over 5.

Looking at the table: I see that agents and tickets are not in the same table unfortunately, so we will have to use some type of join. But do I need all the tables or just two of them for this question? the agent name info is in agents and all tickets are in the tickets: therefore I only need the two tables. But do they have a common column that I can do a merge on? Yes, agent_id is also in tickets which correlates to id in agents. Fantastic. I don't need the ticket_events table at all.

What kind of join do I need: Well, I can't risk missing agents for a ticket, but I also don't care about an agent that doesn't have any tickets since I'm only concerned with the ones that have at least 5. So I will use inner join.

Do I need to do any magic on the columns when selecting:

• I know that I will need to use concat for getting both the first and last name for agent,

• team is given so no magic,

• total tickets will be a count that gets all the ticket counts per - agent, so I will have to use the count function

  
  

Let's start some silly actions:

select concat(first_name," ",last_name) as agent_name, team, count(*) as total_tickets
from ( select * from agents a
		join tickets t on a.id = t.agent_id)
group by a.id
having count(*) >= 5
order by total_tickets desc

This looks right to me. Make sure to check all the requirements after you finish. The question asked only include agents that handled at least five tickets where we did on line 5, and it asked us to order by total_tickets by descending order which we also did.

YAYY. ok this part was not bad tbh.

Let's Continue.

Part 2:

Find the top agent in each team based on number of resolved tickets.

Output:

team | agent_name | resolved_tickets

1. If two agents tie, include both.

First Thoughts: There will be a ranking based on the number of resolved tickets among all teams. Therefore, I shall use the holy window function: rank. Of course the result will need to be grouped by team. Looking at the Tables: agent name and team will come from agents, resolved_tickets are a count of tickets which needs to come from the tickets table, but we specifically need the resolved ones which we get the information from ticket_event. Therefore a join need to be made among all the tables.

What kind of Join do I need: I am not interested in any null values in these columns, There shouldn't be any mismatches between none of them. Therefore... INNER JOIN!

Any magic on the columns:

• team comes from agent directly, so no

• agent_name is a concatenation like part1, but and there can be multiple top agents

• resolved tickets will be a count, which is the total number of tickets that are resolved for each agent

• I need a rank column since there will be a ranking among the agents within different teams

Notes: Because I need a rank column to solve the question with a condition on the rank column, I know beforehand that I need to use a cte table to not repeat the same code over and over again. I also don't need the rank column in my output so I will only get the columns I need from the cte for the final table.

Let's begin the shenaningas (coding):

with cte as (
select team, concat(first_name, ' ', last_name) as agent_name, count(*) as resolved_tickets, rank() over (partition by team order by count(*) desc) as rnk
from agents a
	join tickets t on a.id=t.agent_id 
	join ticket_events e on e.ticket_id = t.id
where event_type = 'resolved'
group by team, a.id
)

I want to explain my thought process in making this cte. I ranked over team because agent_ids need to be ranked within their team, not as a whole. Then I joined all tables at once. I grouped by team, to see each team's agent ranking separately. And of course also grouped by agents since I have an aggregated value for each agent (which is the total number of tickets). Lastly, I needed a where clause to only include tickets that were resolved. My next steps are only getting the necessary columns for the output from this cte, and only including the top agents

select team, agent_name, resolved_tickets
from cte
where rnk =1

This should give the necessary output. Since the count of resolved tickets for each agent was already calculated in cte, they were ranked, and I only included the ones with a ranking of 1. Top agent and the total resolved tickets are displayed within each team! YAYYY

For the next part, I think it will be a bit more challenging.

Part 3: Create a report showing, for each team, the agent with:

1. the most resolved tickets

2. the most escalated tickets

Output:

team | top_resolver | resolved_total | top_escalator | escalated_total

Off the top of my head: I hate this question. And of course I am not joking. I know how to do it tho ;)

First Thoughts: On the other question, it was asking the top_resolver and the resolved_total for each team. Now, there are two rankings going on because we also need to know the top_escalator. Agent_name is normally one column, and here we see that two columns will include agent names, which means there is splitting. I know how to do this: use case when clauses to find top for each category, and then aggregate it so that no null values are shown (just by using max). I will show you in a bit.

Looking at Tables: We need the same columns from part 2, so yes, we also need a join for this question

What kind of Join do I need: we don't wan't any mismatches like part 2, so inner join once again.

Any magic on the columns:

• team comes from agent directly, so, again, nope

• top_resolver only exists for cases where ranking is 1 amongst resolved ranking. concatenation of first-last name again

• resolved_total will be a count, which is the total number of tickets that are resolved for each agent

• top_escalator is the same situation as as top_resolver but for escalating tickets

• escalated_total will be a count as well for the total number of tickets that are escalated for each agent

• I need a rank column for resolved tickets only

• I need a rank column for escalated tickets only

• I will need the event_type column as well in order to know how to split columns at the end based on event_type

Notes: Because I need rank columns to solve the question with conditions on the rank columns, I know beforehand that I need to use a cte table to not repeat the same code over and over again. Same situation as part 2 basically. Another reason is I need to have event_type on my cte, but I do not for the output.

This question is really similar to part 2, but I think the most challenging part is to just know how and where to use the case-when clause. Important thing is to also aggregate it so it doesn't fill the non_matching values as null in the case.

Let's begin the shenaningas (coding):

with cte as (
select team, event_type, concat(first_name, ' ', last_name) as agent_name, count(*) as total_tickets, rank() over (partition by team, event_type order by count(*) desc) as rnk
from agents a
	join tickets t on a.id=t.agent_id 
	join ticket_events e on e.ticket_id = t.id
where event_type='resolved' or event_type='escalated'
group by team, event_type, a.id
)

My thought process on creating this cte: The logic is the same as part 2, but this time, when I created the ranking, I also partitioned by event type. The reason is because I don't care about how many tickets a person has done overall, I am concerned with how many tickets a person has handled within each type of tickets. This time, we are not just concerned with the total tickets resolved, but also escalated. Then we need a separate ranking for each.

I was not so sure about this one because I forgot if I need to add rnk=1 as a condition in the case clause or whether if it's okay since I'm including only the columns with rnk=1 anyways with the where clause. I don't remember which implementation comes first in the order (the column operation within max or the filter function where). So I looked it up, and filtering with where comes before any aggregate function on SQL.

Let's continue then:

select team, max(
case when event_type='resolved' then agent_name end) as top_resolver,
max(
case when event_type='resolved' then total_tickets end) as resolved_total,
max(
case when event_type='escalated' then agent_name end) as top_escalator,
max(
case when event_type='escalated' then total_tickets end) as escalated_total
from cte
where rnk=1
group by team

I checked if I did anything wrong and realized that I used group by before where in some parts, which I had to go back and change. Remember that where ALWAYS comes before groupby. Note for Part 3: For part 3, I just assumed no ties. If there is a tie, max will choose the agent name alphabetically. So, if I wanted to display all the top agents, I need to use group_concat instead of max. I used max because it is generally preferred for when you need a single value as a result of the case clause. Final note: remember that in situations where the question does not care about ties and asks for only the top n, it is easier to use rownumber, since ranking can have duplicates whereas rownumber will not.

Anyways, this should give us the result!

If you think I made any mistakes, feel free to leave a comment! I am most definitely not a SQL expert. But I hope that this was helpful for you to at least see how a regular person thinks when approaching these kinds of questions. Final Verdict (Asking ChatGPT is he is proud)

ALRIGHT ALRIGHT. I did use groupby instead of group by by accident and had to go back and fix it ok? I also did have an equal sign after as, because why are we declaring a variable and not having an equal sign??? It's ok, I am proud of myself. Thank you so much for reading!