Top-Earning Driver in Each City for Q1 2024

Medium
308.

Top-Earning Driver in Each City for Q1 2024

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

ColumnType
driver_idint
namevarchar
cityvarchar

Primary key: driver_id.

One row per driver; a driver works in one city.

Table: Payout

ColumnType
payout_idint
driver_idint
payout_datedate
amountint

Primary key: payout_id.

A transfer to the driver's bank account, in rupees. driver_id always names a row of Driver.

Example

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

Driver

driver_idnamecity
1RameshBengaluru
2SureshBengaluru
3ImranMumbai
4AnilMumbai
5LakshmiChennai
6FaridaChennai

Payout

payout_iddriver_idpayout_dateamount
112024-01-1518000
212024-02-1521000
322024-03-3142000
432024-02-0125000
542024-01-2015000
642024-03-1010000
752024-04-0150000
862023-12-3130000
912024-04-029000

Output

citydriver_idnameq1_earnings
Bengaluru2Suresh42000
Mumbai3Imran25000
Mumbai4Anil25000
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-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

ColumnType
driver_idint
namevarchar
cityvarchar

Primary key: driver_id.

One row per driver; a driver works in one city.

Table: Payout

ColumnType
payout_idint
driver_idint
payout_datedate
amountint

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_idnamecity
1RameshBengaluru
2SureshBengaluru
3ImranMumbai
4AnilMumbai
5LakshmiChennai
6FaridaChennai

Payout

payout_iddriver_idpayout_dateamount
112024-01-1518000
212024-02-1521000
322024-03-3142000
432024-02-0125000
542024-01-2015000
642024-03-1010000
752024-04-0150000
862023-12-3130000
912024-04-029000

Output

citydriver_idnameq1_earnings
Bengaluru2Suresh42000
Mumbai3Imran25000
Mumbai4Anil25000

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

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