Rank support agents within their team

Question

A support operations lead publishes a leaderboard per team without losing the individual rows.

From `agent_performance`, return `agent_name`, `team`, `tickets_closed` and `team_rank`, where `team_rank` is the agent's position within their own team ordered by `tickets_closed` descending. Agents tied

Accepted Answer

Step 1: The requirement keeps every agent row while adding a per-team ranking, which is exactly what a window function does — unlike `GROUP BY`, it does not collapse rows. Step 2: `PARTITION BY team` restarts the numbering for each team. Step 3: `ORDER BY tickets_closed DESC` decides the ranking direction inside each partition. Step 4: Choose `DENSE_RANK()` because ties must share a rank **and** the following rank must not be skipped. `RANK()` would jump (1,1,3) and `ROW_NUMBER()` would break ties arbitrarily. Step 5: Alias the window expression as `team_rank`.

Rank support agents within their team

More Window Functions questions

Asked at EXL