Uber: Drivers Gone Quiet Before the Festive Rush

Medium
984.

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. 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

ColumnType
driver_idint
driver_namevarchar
cityvarchar

Primary key: driver_id.

One row per driver partner.

Table: DriverTrip

ColumnTypeValues
trip_idint
driver_idint
trip_datedate
statusenumcompleted, cancelled

Primary key: trip_id.

One row per trip a driver accepted; no row is dated after 2024-10-15.

Example

The rows must come back in the order shown.

Driver

driver_iddriver_namecity
1AaravPune
2DiyaPune
3KabirMumbai
4MeeraPune
5RohanMumbai

DriverTrip

trip_iddriver_idtrip_datestatus
112024-07-02completed
212024-07-19completed
312024-08-31completed
412024-09-10completed
512024-10-01cancelled
622024-08-01completed
722024-08-02completed
822024-08-03completed
922024-09-16completed
1032024-06-11completed
6 more rows

Output

driver_iddriver_namelast_trip_datedays_idle
4Meera2024-07-07100
1Aarav2024-09-1035
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.

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

ColumnType
driver_idint
driver_namevarchar
cityvarchar

Primary key: driver_id.

One row per driver partner.

Table: DriverTrip

ColumnType
trip_idint
driver_idint
trip_datedate
statusenum(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_iddriver_namecity
1AaravPune
2DiyaPune
3KabirMumbai
4MeeraPune
5RohanMumbai

DriverTrip

trip_iddriver_idtrip_datestatus
112024-07-02completed
212024-07-19completed
312024-08-31completed
412024-09-10completed
512024-10-01cancelled
622024-08-01completed
722024-08-02completed
822024-08-03completed
922024-09-16completed
1032024-06-11completed
1132024-07-14completed
1232024-08-20cancelled
1332024-09-01completed
1442024-05-05completed
1542024-06-06completed
1642024-07-07completed

Output

driver_iddriver_namelast_trip_datedays_idle
4Meera2024-07-07100
1Aarav2024-09-1035

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_id

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