Top-Earning Driver in Each City for Q1 2024 — SQL Problem
Drivers are paid out several times a week. For the quarterly city awards, ops needs the driver with the highest total payout in Q1 2024 (payouts dated…
- Difficulty: Medium
- Topics: Window Functions, Joins, Dates
- Dialect: MySQL
- Problem: #308
Problem statement
Drivers are paid out several times a week. For the quarterly city awards, ops needs the driver with the highest total payout in Q1 2024 (payouts dated 2024-01-01 to 2024-03-31) in each city. If drivers tie for the top total in a city, all of them win.
Return city, driver_id, name and q1_earnings (the driver's Q1 payout total) for every winner. Drivers with no Q1 payout are not considered, and a city whose drivers had none does not appear. Return the rows in any order.
Tables
Table: Driver
| Column | Type |
|---|---|
| driver_id | int |
| name | varchar |
| city | varchar |
Primary key: driver_id.
One row per driver; a driver works in one city.
Table: Payout
| Column | Type |
|---|---|
| payout_id | int |
| driver_id | int |
| payout_date | date |
| amount | int |
Primary key: payout_id.
A transfer to the driver's bank account, in rupees. driver_id always names a row of Driver.
Examples
Example 1
Driver
| driver_id | name | city |
|---|---|---|
| 1 | Ramesh | Bengaluru |
| 2 | Suresh | Bengaluru |
| 3 | Imran | Mumbai |
| 4 | Anil | Mumbai |
| 5 | Lakshmi | Chennai |
| 6 | Farida | Chennai |
Payout
| payout_id | driver_id | payout_date | amount |
|---|---|---|---|
| 1 | 1 | 2024-01-15 | 18000 |
| 2 | 1 | 2024-02-15 | 21000 |
| 3 | 2 | 2024-03-31 | 42000 |
| 4 | 3 | 2024-02-01 | 25000 |
| 5 | 4 | 2024-01-20 | 15000 |
| 6 | 4 | 2024-03-10 | 10000 |
| 7 | 5 | 2024-04-01 | 50000 |
| 8 | 6 | 2023-12-31 | 30000 |
| 9 | 1 | 2024-04-02 | 9000 |
Output
| city | driver_id | name | q1_earnings |
|---|---|---|---|
| Bengaluru | 2 | Suresh | 42000 |
| Mumbai | 3 | Imran | 25000 |
| Mumbai | 4 | Anil | 25000 |
How to solve Top-Earning Driver in Each City for Q1 2024
The question is a top-1 per group with ties, on a value that has to be aggregated first. Step one joins drivers to their payouts, keeps only payouts dated in Q1 2024 (BETWEEN '2024-01-01' AND '2024-03-31' on a date column is inclusive at both ends) and sums per driver — the city comes along in the GROUP BY.
Step two ranks those totals within each city: RANK() OVER (PARTITION BY city ORDER BY q1_earnings DESC). Drivers sharing the top total both get rank 1, so WHERE rnk = 1 keeps every winner; ROW_NUMBER() would drop one of them arbitrarily, which is wrong and non-deterministic.
Because the ranking reads the result of the aggregation, it lives in a second CTE (or a derived table). The alternatives compare each total with the city's maximum — via a correlated MAX, or the window MAX(SUM(amount)) OVER (PARTITION BY city), which nests an aggregate inside a window in one query. An inner join means drivers with no Q1 payout never enter, as the statement requires.
Reference solution (MySQL)
WITH q1 AS (
SELECT d.city, d.driver_id, d.name, SUM(p.amount) AS q1_earnings
FROM Driver d
JOIN Payout p ON p.driver_id = d.driver_id
WHERE p.payout_date BETWEEN '2024-01-01' AND '2024-03-31'
GROUP BY d.city, d.driver_id, d.name
), ranked AS (
SELECT q1.*, RANK() OVER (PARTITION BY city ORDER BY q1_earnings DESC) AS rnk
FROM q1
)
SELECT city, driver_id, name, q1_earnings
FROM ranked
WHERE rnk = 1Another way
WITH q1 AS (
SELECT d.city, d.driver_id, d.name, SUM(p.amount) AS q1_earnings
FROM Driver d JOIN Payout p ON p.driver_id = d.driver_id
WHERE p.payout_date >= '2024-01-01' AND p.payout_date < '2024-04-01'
GROUP BY d.city, d.driver_id, d.name
)
SELECT city, driver_id, name, q1_earnings FROM q1 a
WHERE q1_earnings = (SELECT MAX(b.q1_earnings) FROM q1 b WHERE b.city = a.city)Another way
SELECT city, driver_id, name, q1_earnings FROM (
SELECT d.city, d.driver_id, d.name, SUM(p.amount) AS q1_earnings,
MAX(SUM(p.amount)) OVER (PARTITION BY d.city) AS city_best
FROM Driver d JOIN Payout p ON p.driver_id = d.driver_id
WHERE QUARTER(p.payout_date) = 1 AND YEAR(p.payout_date) = 2024
GROUP BY d.city, d.driver_id, d.name
) t WHERE q1_earnings = city_best← Couriers Who Missed the Delivery SLA on Over 20% of May Drops · Drivers Qualifying for City Trip Incentives →