Top Two Agents per City by Commission, Ties Included

Hard
371.

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). 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

ColumnType
agent_idint
agent_namevarchar
cityvarchar

Primary key: agent_id.

Agent names are unique.

Table: CommissionEarned

ColumnType
commission_idint
agent_idint
earned_ondate
amountint

Primary key: commission_id.

One row per commission credited, in rupees.

Example

The rows must come back in the order shown.

Agent

agent_idagent_namecity
1Aditi VermaPune
2Rohan ShahPune
3Neha IyerPune
4Kabir MenonPune
5Simran RaoMumbai
6David KhanMumbai

CommissionEarned

commission_idagent_idearned_onamount
112024-05-10150000
212024-11-0290000
322024-08-19240000
432024-06-30120000
532025-04-02400000
642024-03-31500000
742024-12-1280000
852025-03-31310000

Output

cityagent_nametotal_commissioncity_rank
MumbaiSimran Rao3100001
PuneAditi Verma2400001
PuneRohan Shah2400001
PuneNeha Iyer1200002
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.

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

ColumnType
agent_idint
agent_namevarchar
cityvarchar

Primary key: agent_id.

Agent names are unique.

Table: CommissionEarned

ColumnType
commission_idint
agent_idint
earned_ondate
amountint

Primary key: commission_id.

One row per commission credited, in rupees.

Examples

Example 1

Agent

agent_idagent_namecity
1Aditi VermaPune
2Rohan ShahPune
3Neha IyerPune
4Kabir MenonPune
5Simran RaoMumbai
6David KhanMumbai

CommissionEarned

commission_idagent_idearned_onamount
112024-05-10150000
212024-11-0290000
322024-08-19240000
432024-06-30120000
532025-04-02400000
642024-03-31500000
742024-12-1280000
852025-03-31310000

Output

cityagent_nametotal_commissioncity_rank
MumbaiSimran Rao3100001
PuneAditi Verma2400001
PuneRohan Shah2400001
PuneNeha Iyer1200002

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_name

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