The Day Each Driver Hit the Monthly ₹10,000 Target

Hard
318.

The Day Each Driver Hit the Monthly ₹10,000 Target

Drivers earn a bonus tier once their earnings in a calendar month reach ₹10,000. The running total resets on the first of every month, and a driver can have several earnings on one day.

For every driver and month in which the driver's month-to-date total reached at least 10,000, return driver_id, month ('YYYY-MM'), target_hit_on (the first date on which the month-to-date total was ≥ 10,000) and month_total (the driver's total for the whole month). Months that never reach the target are left out. Order by driver_id, then month.

Tables

Table: DriverEarning

ColumnType
earning_idint
driver_idint
earned_ondate
amountint

Primary key: earning_id.

One row per credited trip fare or incentive, in rupees.

Example

The rows must come back in the order shown.

DriverEarning

earning_iddriver_idearned_onamount
112024-01-104000
212024-01-203500
312024-01-202500
412024-01-281500
512024-02-016000
612024-02-153000
722024-01-319000
822024-02-012000
922024-02-029000
1032024-02-0512000

Output

driver_idmonthtarget_hit_onmonth_total
12024-012024-01-2011500
22024-022024-02-0211000
32024-022024-02-0512000
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.

The Day Each Driver Hit the Monthly ₹10,000 Target

Drivers earn a bonus tier once their earnings in a calendar month reach ₹10,000.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Aggregation
  • Dialect: MySQL
  • Problem: #318

Problem statement

Drivers earn a bonus tier once their earnings in a calendar month reach ₹10,000. The running total resets on the first of every month, and a driver can have several earnings on one day.

For every driver and month in which the driver's month-to-date total reached at least 10,000, return driver_id, month ('YYYY-MM'), target_hit_on (the first date on which the month-to-date total was ≥ 10,000) and month_total (the driver's total for the whole month). Months that never reach the target are left out. Order by driver_id, then month.

Tables

Table: DriverEarning

ColumnType
earning_idint
driver_idint
earned_ondate
amountint

Primary key: earning_id.

One row per credited trip fare or incentive, in rupees.

Examples

Example 1

DriverEarning

earning_iddriver_idearned_onamount
112024-01-104000
212024-01-203500
312024-01-202500
412024-01-281500
512024-02-016000
612024-02-153000
722024-01-319000
822024-02-012000
922024-02-029000
1032024-02-0512000

Output

driver_idmonthtarget_hit_onmonth_total
12024-012024-01-2011500
22024-022024-02-0211000
32024-022024-02-0512000

How to solve The Day Each Driver Hit the Monthly ₹10,000 Target

Start by rolling earnings up to one row per driver per day, tagged with the month (DATE_FORMAT(earned_on, '%Y-%m')). Without that step, two credits on the same day would be two rows of a running total, and "the first date the total reached 10,000" would still be right — but a window ordered only by date would then have tied rows, which is exactly where frame semantics get murky.

Next, a running total with a reset: SUM(day_amount) OVER (PARTITION BY driver_id, month ORDER BY earned_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Putting the month in the partition is what resets it on the 1st — 31 January's ₹9,000 does not help on 1 February. The same SUM without an ORDER BY is the month's full total on every row.

Finally keep the days where the month-to-date is at least 10,000 and take the earliest per (driver, month); the month total is constant within the group, so MAX simply carries it out. A month that never reaches the target has no qualifying day and vanishes. The alternative computes the running total with a correlated sum — quadratic per driver-month, but window-free.

Reference solution (MySQL)

WITH daily AS (
  SELECT driver_id, DATE_FORMAT(earned_on, '%Y-%m') AS month, earned_on, SUM(amount) AS day_amount
  FROM DriverEarning
  GROUP BY driver_id, DATE_FORMAT(earned_on, '%Y-%m'), earned_on
), running AS (
  SELECT driver_id, month, earned_on,
         SUM(day_amount) OVER (PARTITION BY driver_id, month ORDER BY earned_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS mtd,
         SUM(day_amount) OVER (PARTITION BY driver_id, month) AS month_total
  FROM daily
)
SELECT driver_id, month, MIN(earned_on) AS target_hit_on, MAX(month_total) AS month_total
FROM running
WHERE mtd >= 10000
GROUP BY driver_id, month
ORDER BY driver_id, month

Another way

WITH daily AS (
  SELECT driver_id, LEFT(earned_on, 7) AS month, earned_on, SUM(amount) AS day_amount
  FROM DriverEarning GROUP BY driver_id, LEFT(earned_on, 7), earned_on
), hits AS (
  SELECT a.driver_id, a.month, a.earned_on
  FROM daily a
  WHERE (SELECT SUM(b.day_amount) FROM daily b WHERE b.driver_id = a.driver_id AND b.month = a.month AND b.earned_on <= a.earned_on) >= 10000
)
SELECT h.driver_id, h.month, MIN(h.earned_on) AS target_hit_on,
       (SELECT SUM(e.amount) FROM DriverEarning e WHERE e.driver_id = h.driver_id AND LEFT(e.earned_on, 7) = h.month) AS month_total
FROM hits h
GROUP BY h.driver_id, h.month
ORDER BY h.driver_id, h.month

← Rider App Sessions and Booking Conversion · Median Pickup-to-Drop Minutes in Each Delivery Zone →