Top Two Agents per City by Commission, Ties Included
A national brokerage rewards the top two commission totals in each city for financial year 2024–25 (2024-04-01 to 2025-03-31, inclusive).
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #371
Problem statement
A national brokerage rewards the top two commission totals in each city for financial year 2024–25 (2024-04-01 to 2025-03-31, inclusive). Agents with equal totals share a place, and places are not skipped: if two agents tie for first, the next total is second.
Return city, agent_name, total_commission and city_rank (1 or 2) for every agent in the first two places of their city. Agents with no commission in the year are not ranked. Order by city, city_rank, agent_name.
Tables
Table: Agent
| Column | Type |
|---|---|
| agent_id | int |
| agent_name | varchar |
| city | varchar |
Primary key: agent_id.
Agent names are unique.
Table: CommissionEarned
| Column | Type |
|---|---|
| commission_id | int |
| agent_id | int |
| earned_on | date |
| amount | int |
Primary key: commission_id.
One row per commission credited, in rupees.
Examples
Example 1
Agent
| agent_id | agent_name | city |
|---|---|---|
| 1 | Aditi Verma | Pune |
| 2 | Rohan Shah | Pune |
| 3 | Neha Iyer | Pune |
| 4 | Kabir Menon | Pune |
| 5 | Simran Rao | Mumbai |
| 6 | David Khan | Mumbai |
CommissionEarned
| commission_id | agent_id | earned_on | amount |
|---|---|---|---|
| 1 | 1 | 2024-05-10 | 150000 |
| 2 | 1 | 2024-11-02 | 90000 |
| 3 | 2 | 2024-08-19 | 240000 |
| 4 | 3 | 2024-06-30 | 120000 |
| 5 | 3 | 2025-04-02 | 400000 |
| 6 | 4 | 2024-03-31 | 500000 |
| 7 | 4 | 2024-12-12 | 80000 |
| 8 | 5 | 2025-03-31 | 310000 |
Output
| city | agent_name | total_commission | city_rank |
|---|---|---|---|
| Mumbai | Simran Rao | 310000 | 1 |
| Pune | Aditi Verma | 240000 | 1 |
| Pune | Rohan Shah | 240000 | 1 |
| Pune | Neha Iyer | 120000 | 2 |
How to solve Top Two Agents per City by Commission, Ties Included
First reduce the ledger to one total per agent for the financial year. The date filter goes before the sum: Neha's ₹4 lakh credited on 2 April 2025 belongs to the next year, and Kabir's ₹5 lakh on 31 March 2024 to the previous one, which changes both their places. The inner join leaves out agents with no commission in the year.
Then rank within each city with DENSE_RANK() OVER (PARTITION BY city ORDER BY total_commission DESC) and keep places 1 and 2. In Pune, Aditi (1.5 + 0.9 lakh) and Rohan both total ₹2.4 lakh and share first place; with DENSE_RANK Neha's ₹1.2 lakh is second, so three Pune agents are rewarded. RANK would give Neha 3 and drop her, and ROW_NUMBER would separate the tied pair arbitrarily — the statement's "places are not skipped" picks DENSE_RANK.
Without windows, an agent's dense place is one plus the number of distinct higher totals in the city, computed by a correlated COUNT(DISTINCT …).
Reference solution (MySQL)
WITH totals AS (
SELECT a.city, a.agent_name, SUM(c.amount) AS total_commission
FROM Agent a
JOIN CommissionEarned c ON c.agent_id = a.agent_id
WHERE c.earned_on BETWEEN '2024-04-01' AND '2025-03-31'
GROUP BY a.agent_id, a.city, a.agent_name
), ranked AS (
SELECT city, agent_name, total_commission,
DENSE_RANK() OVER (PARTITION BY city ORDER BY total_commission DESC) AS city_rank
FROM totals
)
SELECT city, agent_name, total_commission, city_rank
FROM ranked
WHERE city_rank <= 2
ORDER BY city, city_rank, agent_nameAnother way
WITH totals AS (SELECT a.city, a.agent_name, SUM(c.amount) AS total_commission FROM Agent a JOIN CommissionEarned c ON c.agent_id = a.agent_id WHERE c.earned_on >= '2024-04-01' AND c.earned_on < '2025-04-01' GROUP BY a.city, a.agent_name) SELECT t.city, t.agent_name, t.total_commission, (SELECT COUNT(DISTINCT u.total_commission) FROM totals u WHERE u.city = t.city AND u.total_commission >= t.total_commission) AS city_rank FROM totals t WHERE (SELECT COUNT(DISTINCT u.total_commission) FROM totals u WHERE u.city = t.city AND u.total_commission > t.total_commission) < 2 ORDER BY t.city, city_rank, t.agent_name← Occupied Flats on the First of Each Month · Six-Month Renter Retention by Move-In Cohort →