Drivers Without a Single Completed Trip in March 2024
The driver-success team wants to call every driver who went idle in March 2024.
- Difficulty: Easy
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #299
Problem statement
The driver-success team wants to call every driver who went idle in March 2024. A driver counts as idle when they have no completed trip that started in March 2024 (from 2024-03-01 00:00:00 up to and including 2024-03-31 23:59:59). Cancelled trips in March, and completed trips in other months, do not count.
Return the columns driver_id and name of every idle driver, including drivers with no trips at all. Return the rows in any order.
Tables
Table: Driver
| Column | Type |
|---|---|
| driver_id | int |
| name | varchar |
| city | varchar |
Primary key: driver_id.
One row per onboarded driver.
Table: Trip
| Column | Type |
|---|---|
| trip_id | int |
| driver_id | int |
| started_at | datetime |
| status | enum(completed, cancelled) |
Primary key: trip_id.
driver_id always names a row of Driver.
Examples
Example 1
Driver
| driver_id | name | city |
|---|---|---|
| 1 | Ramesh | Bengaluru |
| 2 | Suresh | Bengaluru |
| 3 | Imran | Mumbai |
| 4 | Gurpreet | Delhi |
| 5 | Lakshmi | Chennai |
Trip
| trip_id | driver_id | started_at | status |
|---|---|---|---|
| 101 | 1 | 2024-03-05 09:12:00 | completed |
| 102 | 2 | 2024-03-14 18:40:00 | cancelled |
| 103 | 2 | 2024-02-29 23:50:00 | completed |
| 104 | 3 | 2024-03-31 23:59:59 | completed |
| 105 | 4 | 2024-04-01 00:00:00 | completed |
| 106 | 1 | 2024-03-20 11:00:00 | completed |
Output
| driver_id | name |
|---|---|
| 2 | Suresh |
| 4 | Gurpreet |
| 5 | Lakshmi |
How to solve Drivers Without a Single Completed Trip in March 2024
This is an anti join with a filtered right side: we want drivers who have no trip satisfying three conditions — same driver, completed, started in March 2024.
With a LEFT JOIN all three conditions belong in the ON clause. Then a driver with a qualifying trip is matched, and a driver without one — whether they had no trips, only cancelled ones, or only trips in February or April — comes out once with NULLs on the trip side, which WHERE t.trip_id IS NULL keeps. Putting the status or date tests in WHERE instead would throw away exactly those NULL rows and lose the idle drivers who had trips in other months.
The month boundary is written as a half-open range, >= '2024-03-01 00:00:00' and < '2024-04-01 00:00:00', which includes 23:59:59 on the 31st and nothing of April 1st. YEAR()/MONTH() or DATE_FORMAT(…, '%Y-%m') say the same thing but cannot use an index on started_at. NOT EXISTS and NOT IN work too; NOT IN is safe here only because Trip.driver_id is never NULL.
Reference solution (MySQL)
SELECT d.driver_id, d.name
FROM Driver d
LEFT JOIN Trip t
ON t.driver_id = d.driver_id
AND t.status = 'completed'
AND t.started_at >= '2024-03-01 00:00:00'
AND t.started_at < '2024-04-01 00:00:00'
WHERE t.trip_id IS NULLAnother way
SELECT driver_id, name FROM Driver d WHERE NOT EXISTS (SELECT 1 FROM Trip t WHERE t.driver_id = d.driver_id AND t.status = 'completed' AND YEAR(t.started_at) = 2024 AND MONTH(t.started_at) = 3)Another way
SELECT driver_id, name FROM Driver WHERE driver_id NOT IN (SELECT driver_id FROM Trip WHERE status = 'completed' AND DATE_FORMAT(started_at, '%Y-%m') = '2024-03')← Airport Drop Trips Charged With Surge Pricing · Shipments Booked at Each Courier Hub →