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
| Column | Type |
|---|---|
| earning_id | int |
| driver_id | int |
| earned_on | date |
| amount | int |
Primary key: earning_id.
One row per credited trip fare or incentive, in rupees.
Examples
Example 1
DriverEarning
| earning_id | driver_id | earned_on | amount |
|---|---|---|---|
| 1 | 1 | 2024-01-10 | 4000 |
| 2 | 1 | 2024-01-20 | 3500 |
| 3 | 1 | 2024-01-20 | 2500 |
| 4 | 1 | 2024-01-28 | 1500 |
| 5 | 1 | 2024-02-01 | 6000 |
| 6 | 1 | 2024-02-15 | 3000 |
| 7 | 2 | 2024-01-31 | 9000 |
| 8 | 2 | 2024-02-01 | 2000 |
| 9 | 2 | 2024-02-02 | 9000 |
| 10 | 3 | 2024-02-05 | 12000 |
Output
| driver_id | month | target_hit_on | month_total |
|---|---|---|---|
| 1 | 2024-01 | 2024-01-20 | 11500 |
| 2 | 2024-02 | 2024-02-02 | 11000 |
| 3 | 2024-02 | 2024-02-05 | 12000 |
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, monthAnother 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 →