Agent CSAT Rank Within Each Support Team — SQL Problem
Each team lead ranks their agents by customer satisfaction. An agent's CSAT is the average of their answered survey scores (score not NULL); only agents…
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #339
Problem statement
Each team lead ranks their agents by customer satisfaction. An agent's CSAT is the average of their answered survey scores (score not NULL); only agents with at least 3 answered responses are ranked. Within a team the agent with the highest average is rank 1; agents with equal averages share a rank and the next rank follows without a gap (1, 1, 2).
Return the columns team, agent_name, responses (answered responses), avg_csat (the average rounded to 2 decimals; rank on the unrounded value) and team_rank, ordered by team, then team_rank, then agent_name.
Tables
Table: Agent
| Column | Type |
|---|---|
| agent_id | int |
| agent_name | varchar |
| team | varchar |
Primary key: agent_id.
One row per support agent. Names are unique.
Table: CsatSurvey
| Column | Type |
|---|---|
| survey_id | int |
| agent_id | int |
| score | int |
Primary key: survey_id.
One survey per closed ticket, credited to the agent who closed it. score is 1–5, or NULL if the customer never answered.
Examples
Example 1
Agent
| agent_id | agent_name | team |
|---|---|---|
| 1 | Aisha | Payments |
| 2 | Farhan | Payments |
| 3 | Pooja | Payments |
| 4 | Riya | Logistics |
| 5 | Vivaan | Logistics |
CsatSurvey
| survey_id | agent_id | score |
|---|---|---|
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 1 | 3 |
| 4 | 2 | 4 |
| 5 | 2 | 4 |
| 6 | 2 | 4 |
| 7 | 2 | NULL |
| 8 | 3 | 5 |
| 9 | 3 | 5 |
| 10 | 4 | 2 |
| 11 | 4 | 5 |
| 12 | 4 | 4 |
| 13 | 4 | 3 |
| 14 | 5 | 3 |
| 15 | 5 | 3 |
| 16 | 5 | 4 |
Output
| team | agent_name | responses | avg_csat | team_rank |
|---|---|---|---|---|
| Logistics | Riya | 4 | 3.5 | 1 |
| Logistics | Vivaan | 3 | 3.33 | 2 |
| Payments | Aisha | 3 | 4 | 1 |
| Payments | Farhan | 3 | 4 | 1 |
How to solve Agent CSAT Rank Within Each Support Team
First collapse the surveys to one row per agent: join Agent to CsatSurvey, drop unanswered surveys, and compute COUNT(*) and AVG(score). The HAVING clause keeps agents with at least three answered responses — a filter on the group, so it cannot go in WHERE. (Without the WHERE, COUNT(s.score) and AVG(s.score) would still skip NULLs, as the alternative shows, but COUNT(*) would not.)
Then rank within each team: DENSE_RANK() OVER (PARTITION BY team ORDER BY avg_raw DESC). Of the three ranking functions it is the one the statement describes — equal averages share a rank and the next one follows without a gap. RANK would leave a gap (1, 1, 3) and ROW_NUMBER would break the tie arbitrarily. Ranking on the raw average, not the rounded one, matters when two averages differ only in the third decimal.
The alternative computes the dense rank by hand: one plus the number of distinct higher averages in the team, comparing averages as integers scaled by a million so that no division result is compared for equality. It is quadratic per team; the window needs one sort of the per-agent rows.
Reference solution (MySQL)
WITH scored AS (
SELECT a.team, a.agent_name, COUNT(*) AS responses, AVG(s.score) AS avg_raw
FROM Agent a
JOIN CsatSurvey s ON s.agent_id = a.agent_id
WHERE s.score IS NOT NULL
GROUP BY a.agent_id, a.team, a.agent_name
HAVING COUNT(*) >= 3
)
SELECT team, agent_name, responses, ROUND(avg_raw, 2) AS avg_csat,
DENSE_RANK() OVER (PARTITION BY team ORDER BY avg_raw DESC) AS team_rank
FROM scored
ORDER BY team, team_rank, agent_nameAnother way
WITH scored AS (
SELECT a.team, a.agent_name, COUNT(s.score) AS responses, SUM(s.score) AS total
FROM Agent a JOIN CsatSurvey s ON s.agent_id = a.agent_id
GROUP BY a.agent_id, a.team, a.agent_name
HAVING COUNT(s.score) >= 3
)
SELECT x.team, x.agent_name, x.responses, ROUND(x.total / x.responses, 2) AS avg_csat,
1 + (SELECT COUNT(*) FROM (SELECT DISTINCT y.team, y.total * 1000000 DIV y.responses AS key6 FROM scored y) d
WHERE d.team = x.team AND d.key6 > x.total * 1000000 DIV x.responses) AS team_rank
FROM scored x
ORDER BY x.team, team_rank, x.agent_name← Error Counts by Region and Environment From Hostnames · Peak Ticket Hour for Each Day of the Week →