Agent CSAT Rank Within Each Support Team

Medium
339.

Agent CSAT Rank Within Each Support Team

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

ColumnType
agent_idint
agent_namevarchar
teamvarchar

Primary key: agent_id.

One row per support agent. Names are unique.

Table: CsatSurvey

ColumnType
survey_idint
agent_idint
scoreint

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.

Example

The rows must come back in the order shown.

Agent

agent_idagent_nameteam
1AishaPayments
2FarhanPayments
3PoojaPayments
4RiyaLogistics
5VivaanLogistics

CsatSurvey

survey_idagent_idscore
115
214
313
424
524
624
72NULL
835
935
1042
6 more rows

Output

teamagent_nameresponsesavg_csatteam_rank
LogisticsRiya43.51
LogisticsVivaan33.332
PaymentsAisha341
PaymentsFarhan341
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
agent_idint
agent_namevarchar
teamvarchar

Primary key: agent_id.

One row per support agent. Names are unique.

Table: CsatSurvey

ColumnType
survey_idint
agent_idint
scoreint

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_idagent_nameteam
1AishaPayments
2FarhanPayments
3PoojaPayments
4RiyaLogistics
5VivaanLogistics

CsatSurvey

survey_idagent_idscore
115
214
313
424
524
624
72NULL
835
935
1042
1145
1244
1343
1453
1553
1654

Output

teamagent_nameresponsesavg_csatteam_rank
LogisticsRiya43.51
LogisticsVivaan33.332
PaymentsAisha341
PaymentsFarhan341

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_name

Another 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 →