Uber: Drivers Gone Quiet Before the Festive Rush
Before the festive season the driver-supply team calls drivers who used to drive regularly but have stopped, with a reactivation bonus.
- Difficulty: Medium
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #984
Problem statement
Before the festive season the driver-supply team calls drivers who used to drive regularly but have stopped, with a reactivation bonus. The trip log runs up to the report date, 2024-10-15.
A driver is gone quiet when they had at least 3 completed trips dated before 2024-09-01 and no completed trip on or after 2024-09-16 (the last 30 days). Cancelled trips count for nothing either way. Return driver_id, driver_name, last_trip_date (their latest completed trip) and days_idle (days from that trip to 2024-10-15), ordered by days_idle descending, then driver_id.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: Driver
| Column | Type |
|---|---|
| driver_id | int |
| driver_name | varchar |
| city | varchar |
Primary key: driver_id.
One row per driver partner.
Table: DriverTrip
| Column | Type |
|---|---|
| trip_id | int |
| driver_id | int |
| trip_date | date |
| status | enum(completed, cancelled) |
Primary key: trip_id.
One row per trip a driver accepted; no row is dated after 2024-10-15.
Examples
Example 1
Driver
| driver_id | driver_name | city |
|---|---|---|
| 1 | Aarav | Pune |
| 2 | Diya | Pune |
| 3 | Kabir | Mumbai |
| 4 | Meera | Pune |
| 5 | Rohan | Mumbai |
DriverTrip
| trip_id | driver_id | trip_date | status |
|---|---|---|---|
| 1 | 1 | 2024-07-02 | completed |
| 2 | 1 | 2024-07-19 | completed |
| 3 | 1 | 2024-08-31 | completed |
| 4 | 1 | 2024-09-10 | completed |
| 5 | 1 | 2024-10-01 | cancelled |
| 6 | 2 | 2024-08-01 | completed |
| 7 | 2 | 2024-08-02 | completed |
| 8 | 2 | 2024-08-03 | completed |
| 9 | 2 | 2024-09-16 | completed |
| 10 | 3 | 2024-06-11 | completed |
| 11 | 3 | 2024-07-14 | completed |
| 12 | 3 | 2024-08-20 | cancelled |
| 13 | 3 | 2024-09-01 | completed |
| 14 | 4 | 2024-05-05 | completed |
| 15 | 4 | 2024-06-06 | completed |
| 16 | 4 | 2024-07-07 | completed |
Output
| driver_id | driver_name | last_trip_date | days_idle |
|---|---|---|---|
| 4 | Meera | 2024-07-07 | 100 |
| 1 | Aarav | 2024-09-10 | 35 |
How to solve Uber: Drivers Gone Quiet Before the Festive Rush
A dormant account is defined by two facts about the same history: it was active before a cut-off, and it has been silent recently. Both are aggregates over a driver's completed trips, so the query filters to completed trips in the join, groups by driver, and tests both in HAVING.
"At least 3 completed trips before 2024-09-01" is a conditional count, SUM(CASE WHEN trip_date < '2024-09-01' THEN 1 ELSE 0 END) >= 3. "No completed trip on or after 2024-09-16" needs no second count: it is equivalent to the latest completed trip being earlier than that day, MAX(trip_date) < '2024-09-16'. The same MAX is the last_trip_date, and DATEDIFF('2024-10-15', …) turns it into days idle.
The edges in the example: Aarav's 31 August trip counts as early, his 10 September trip is inside the gap but before the silent window, and his October trip was cancelled — he is quiet, idle 35 days. Diya drove on 16 September, the first day of the window, so she is active. Kabir has only two early completed trips (1 September is not before the cut-off, and the August trip was cancelled). Drivers with no completed trip never form a group.
The alternative states each condition separately — a correlated count of early trips and a NOT EXISTS for recent ones — over a derived table of last trip dates. The aggregate version reads each driver's trips once.
Reference solution (MySQL)
SELECT d.driver_id, d.driver_name, MAX(t.trip_date) AS last_trip_date,
DATEDIFF('2024-10-15', MAX(t.trip_date)) AS days_idle
FROM Driver d
JOIN DriverTrip t ON t.driver_id = d.driver_id AND t.status = 'completed'
GROUP BY d.driver_id, d.driver_name
HAVING SUM(CASE WHEN t.trip_date < '2024-09-01' THEN 1 ELSE 0 END) >= 3
AND MAX(t.trip_date) < '2024-09-16'
ORDER BY days_idle DESC, d.driver_idAnother way
SELECT d.driver_id, d.driver_name, x.last_trip_date, DATEDIFF('2024-10-15', x.last_trip_date) AS days_idle
FROM Driver d
JOIN (SELECT driver_id, MAX(trip_date) AS last_trip_date FROM DriverTrip WHERE status = 'completed' GROUP BY driver_id) x
ON x.driver_id = d.driver_id
WHERE (SELECT COUNT(*) FROM DriverTrip e WHERE e.driver_id = d.driver_id AND e.status = 'completed' AND e.trip_date < '2024-09-01') >= 3
AND NOT EXISTS (SELECT 1 FROM DriverTrip r WHERE r.driver_id = d.driver_id AND r.status = 'completed' AND r.trip_date >= '2024-09-16')
ORDER BY days_idle DESC, d.driver_id← Uber: Pickup ETA Accuracy by City · Uber: Promo Abuse — One Device, Many Accounts, Same Campaign →