Drivers Without a Single Completed Trip in March 2024

Easy
299.

Drivers Without a Single Completed Trip in March 2024

EasyJoinsDates

MySQL

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

ColumnType
driver_idint
namevarchar
cityvarchar

Primary key: driver_id.

One row per onboarded driver.

Table: Trip

ColumnTypeValues
trip_idint
driver_idint
started_atdatetime
statusenumcompleted, cancelled

Primary key: trip_id.

driver_id always names a row of Driver.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Driver

driver_idnamecity
1RameshBengaluru
2SureshBengaluru
3ImranMumbai
4GurpreetDelhi
5LakshmiChennai

Trip

trip_iddriver_idstarted_atstatus
10112024-03-05 09:12:00completed
10222024-03-14 18:40:00cancelled
10322024-02-29 23:50:00completed
10432024-03-31 23:59:59completed
10542024-04-01 00:00:00completed
10612024-03-20 11:00:00completed

Output

driver_idname
2Suresh
4Gurpreet
5Lakshmi
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.

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

ColumnType
driver_idint
namevarchar
cityvarchar

Primary key: driver_id.

One row per onboarded driver.

Table: Trip

ColumnType
trip_idint
driver_idint
started_atdatetime
statusenum(completed, cancelled)

Primary key: trip_id.

driver_id always names a row of Driver.

Examples

Example 1

Driver

driver_idnamecity
1RameshBengaluru
2SureshBengaluru
3ImranMumbai
4GurpreetDelhi
5LakshmiChennai

Trip

trip_iddriver_idstarted_atstatus
10112024-03-05 09:12:00completed
10222024-03-14 18:40:00cancelled
10322024-02-29 23:50:00completed
10432024-03-31 23:59:59completed
10542024-04-01 00:00:00completed
10612024-03-20 11:00:00completed

Output

driver_idname
2Suresh
4Gurpreet
5Lakshmi

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 NULL

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