Top Two Agencies by Commission Each Quarter

Medium
535.

Top Two Agencies by Commission Each Quarter

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

ColumnType
agency_idint
agency_namevarchar
commission_pctint

Primary key: agency_id.

One row per partner; commission_pct is a whole percentage.

Table: TicketSale

ColumnTypeValues
sale_idint
agency_idint
sale_datedate
ticket_valueint
statusenumticketed, refunded

Primary key: sale_id.

ticket_value is in rupees; agency_id always names an agency.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Agency

agency_idagency_namecommission_pct
1Yatra Tours5
2Sahil Holidays8
3Blue Lotus Travel4
4Northstar Journeys6

TicketSale

sale_idagency_idsale_dateticket_valuestatus
112024-07-0420000ticketed
222024-07-1912500ticketed
332024-08-0225000ticketed
442024-09-119000ticketed
542024-09-2815000refunded
612024-10-058000ticketed
722024-11-214000ticketed
832024-12-306000ticketed

Output

quarteragency_namecommissionquarter_rank
2024-Q3Yatra Tours10001
2024-Q3Sahil Holidays10001
2024-Q3Blue Lotus Travel10001
2024-Q4Yatra Tours4001
2024-Q4Sahil Holidays3202
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 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

ColumnType
agency_idint
agency_namevarchar
commission_pctint

Primary key: agency_id.

One row per partner; commission_pct is a whole percentage.

Table: TicketSale

ColumnType
sale_idint
agency_idint
sale_datedate
ticket_valueint
statusenum(ticketed, refunded)

Primary key: sale_id.

ticket_value is in rupees; agency_id always names an agency.

Examples

Example 1

Agency

agency_idagency_namecommission_pct
1Yatra Tours5
2Sahil Holidays8
3Blue Lotus Travel4
4Northstar Journeys6

TicketSale

sale_idagency_idsale_dateticket_valuestatus
112024-07-0420000ticketed
222024-07-1912500ticketed
332024-08-0225000ticketed
442024-09-119000ticketed
542024-09-2815000refunded
612024-10-058000ticketed
722024-11-214000ticketed
832024-12-306000ticketed

Output

quarteragency_namecommissionquarter_rank
2024-Q3Yatra Tours10001
2024-Q3Sahil Holidays10001
2024-Q3Blue Lotus Travel10001
2024-Q4Yatra Tours4001
2024-Q4Sahil Holidays3202

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 <= 2

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