Top Two Agencies by Commission Each Quarter — SQL Problem
An airline pays each partner agency commission_pct percent of the value of every ticket it sells that is still ticketed (refunded tickets earn nothing).
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Joins
- Dialect: MySQL
- Problem: #535
Problem statement
An airline pays each partner agency commission_pct percent of the value of every ticket it sells that is still ticketed (refunded tickets earn nothing). A quarter is labelled like 2024-Q3.
For every quarter, return the agencies whose total commission ranks in the top 2 of that quarter, with columns quarter, agency_name, commission (rounded to 2 decimals) and quarter_rank. Agencies with equal commission share a rank, and the next rank is skipped (1, 1, 3), so a tie can put more than two agencies in. Agencies with no ticketed sale in a quarter are not ranked in it. Return the rows in any order.
Tables
Table: Agency
| Column | Type |
|---|---|
| agency_id | int |
| agency_name | varchar |
| commission_pct | int |
Primary key: agency_id.
One row per partner; commission_pct is a whole percentage.
Table: TicketSale
| Column | Type |
|---|---|
| sale_id | int |
| agency_id | int |
| sale_date | date |
| ticket_value | int |
| status | enum(ticketed, refunded) |
Primary key: sale_id.
ticket_value is in rupees; agency_id always names an agency.
Examples
Example 1
Agency
| agency_id | agency_name | commission_pct |
|---|---|---|
| 1 | Yatra Tours | 5 |
| 2 | Sahil Holidays | 8 |
| 3 | Blue Lotus Travel | 4 |
| 4 | Northstar Journeys | 6 |
TicketSale
| sale_id | agency_id | sale_date | ticket_value | status |
|---|---|---|---|---|
| 1 | 1 | 2024-07-04 | 20000 | ticketed |
| 2 | 2 | 2024-07-19 | 12500 | ticketed |
| 3 | 3 | 2024-08-02 | 25000 | ticketed |
| 4 | 4 | 2024-09-11 | 9000 | ticketed |
| 5 | 4 | 2024-09-28 | 15000 | refunded |
| 6 | 1 | 2024-10-05 | 8000 | ticketed |
| 7 | 2 | 2024-11-21 | 4000 | ticketed |
| 8 | 3 | 2024-12-30 | 6000 | ticketed |
Output
| quarter | agency_name | commission | quarter_rank |
|---|---|---|---|
| 2024-Q3 | Yatra Tours | 1000 | 1 |
| 2024-Q3 | Sahil Holidays | 1000 | 1 |
| 2024-Q3 | Blue Lotus Travel | 1000 | 1 |
| 2024-Q4 | Yatra Tours | 400 | 1 |
| 2024-Q4 | Sahil Holidays | 320 | 2 |
How to solve Top Two Agencies by Commission Each Quarter
There are two steps, and they must happen in this order: aggregate, then rank. The first CTE turns sales into one row per (quarter, agency): it drops refunded tickets, computes each ticket's commission as ticket_value * commission_pct / 100, labels the quarter with CONCAT(YEAR(d), '-Q', QUARTER(d)), and sums. An agency with no ticketed sale in a quarter simply has no row there, so it is never ranked.
The second step ranks those totals inside each quarter with RANK() OVER (PARTITION BY quarter ORDER BY commission DESC). RANK is the function the statement describes — equal totals share a rank and the next one is skipped — so a tie for first place yields ranks 1, 1, 3 and both tied agencies appear while the third does not. Because a window value cannot be used in the WHERE of its own query, the filter quarter_rank <= 2 goes one level out.
The commission is rounded only for display, after ranking, so rounding never creates or breaks a tie. The alternative computes the rank as 1 + the number of agencies with strictly more commission in the quarter — the definition of RANK — with a correlated count.
Reference solution (MySQL)
WITH q AS (
SELECT CONCAT(YEAR(s.sale_date), '-Q', QUARTER(s.sale_date)) AS quarter, a.agency_id, a.agency_name,
SUM(s.ticket_value * a.commission_pct / 100) AS commission
FROM TicketSale s
JOIN Agency a ON a.agency_id = s.agency_id
WHERE s.status = 'ticketed'
GROUP BY CONCAT(YEAR(s.sale_date), '-Q', QUARTER(s.sale_date)), a.agency_id, a.agency_name
), r AS (
SELECT quarter, agency_name, commission, RANK() OVER (PARTITION BY quarter ORDER BY commission DESC) AS quarter_rank
FROM q
)
SELECT quarter, agency_name, ROUND(commission, 2) AS commission, quarter_rank
FROM r
WHERE quarter_rank <= 2Another way
WITH q AS (
SELECT CONCAT(YEAR(s.sale_date), '-Q', QUARTER(s.sale_date)) AS quarter, a.agency_id, a.agency_name,
SUM(s.ticket_value * a.commission_pct) / 100 AS commission
FROM TicketSale s JOIN Agency a ON a.agency_id = s.agency_id
WHERE s.status = 'ticketed'
GROUP BY CONCAT(YEAR(s.sale_date), '-Q', QUARTER(s.sale_date)), a.agency_id, a.agency_name
)
SELECT x.quarter, x.agency_name, ROUND(x.commission, 2) AS commission,
1 + (SELECT COUNT(*) FROM q y WHERE y.quarter = x.quarter AND y.commission > x.commission) AS quarter_rank
FROM q x
WHERE (SELECT COUNT(*) FROM q y WHERE y.quarter = x.quarter AND y.commission > x.commission) < 2← Waitlist Positions on a Train per Class · Hotel Cancellation Refunds Under a Tiered Policy →