Ola: Driver Licences Expiring Within 30 Days

Easy
827.

Ola: Driver Licences Expiring Within 30 Days

EasyJoinsDates

MySQL

On 2024-07-01 the driver-partner operations team sends renewal reminders to active drivers whose driving licence is about to run out.

Return every active driver whose driving_licence document expires from 2024-07-01 to 30 days after it, both days included. Licences already expired before 2024-07-01 go to a different team and are not listed; other document types are ignored. Return driver_id, full_name, expires_on and days_left — the days from 2024-07-01 to the expiry date — ordered by days_left, then driver_id.

A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.

Tables

Table: Driver

ColumnTypeValues
driver_idint
full_namevarchar
cityvarchar
statusenumactive, suspended, offboarded

Primary key: driver_id.

One row per driver-partner.

Table: DriverDocument

ColumnTypeValues
doc_idint
driver_idint
doc_typeenumdriving_licence, vehicle_rc, insurance, pan
expires_ondate

Primary key: doc_id.

A driver has at most one current document of each type. expires_on is NULL for documents that never expire (PAN).

Example

The rows must come back in the order shown.

Driver

driver_idfull_namecitystatus
1Ravi KumarBengaluruactive
2Suresh PatilPuneactive
3Imran ShaikhMumbaisuspended
4Gopal RaoHyderabadactive
5Manoj YadavDelhiactive

DriverDocument

doc_iddriver_iddoc_typeexpires_on
111driving_licence2024-07-31
121insurance2024-07-05
132driving_licence2024-07-01
143driving_licence2024-07-10
154driving_licence2024-08-01
165driving_licence2024-06-30
175panNULL
184vehicle_rc2024-07-15

Output

driver_idfull_nameexpires_ondays_left
2Suresh Patil2024-07-010
1Ravi Kumar2024-07-3130
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.

Ola: Driver Licences Expiring Within 30 Days — SQL Problem

On 2024-07-01 the driver-partner operations team sends renewal reminders to active drivers whose driving licence is about to run out.

  • Difficulty: Easy
  • Topics: Joins, Dates
  • Dialect: MySQL
  • Problem: #827

Problem statement

On 2024-07-01 the driver-partner operations team sends renewal reminders to active drivers whose driving licence is about to run out.

Return every active driver whose driving_licence document expires from 2024-07-01 to 30 days after it, both days included. Licences already expired before 2024-07-01 go to a different team and are not listed; other document types are ignored. Return driver_id, full_name, expires_on and days_left — the days from 2024-07-01 to the expiry date — ordered by days_left, then driver_id.

A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.

Tables

Table: Driver

ColumnType
driver_idint
full_namevarchar
cityvarchar
statusenum(active, suspended, offboarded)

Primary key: driver_id.

One row per driver-partner.

Table: DriverDocument

ColumnType
doc_idint
driver_idint
doc_typeenum(driving_licence, vehicle_rc, insurance, pan)
expires_ondate

Primary key: doc_id.

A driver has at most one current document of each type. expires_on is NULL for documents that never expire (PAN).

Examples

Example 1

Driver

driver_idfull_namecitystatus
1Ravi KumarBengaluruactive
2Suresh PatilPuneactive
3Imran ShaikhMumbaisuspended
4Gopal RaoHyderabadactive
5Manoj YadavDelhiactive

DriverDocument

doc_iddriver_iddoc_typeexpires_on
111driving_licence2024-07-31
121insurance2024-07-05
132driving_licence2024-07-01
143driving_licence2024-07-10
154driving_licence2024-08-01
165driving_licence2024-06-30
175panNULL
184vehicle_rc2024-07-15

Output

driver_idfull_nameexpires_ondays_left
2Suresh Patil2024-07-010
1Ravi Kumar2024-07-3130

How to solve Ola: Driver Licences Expiring Within 30 Days

Each driver's status lives in Driver and the expiry dates live in DriverDocument, so the query joins the two and filters: the driver must be active, the document must be the driving licence, and the expiry must fall in the 30-day window starting 2024-07-01.

DATE_ADD('2024-07-01', INTERVAL 30 DAY) is 2024-07-31, and BETWEEN includes both ends, so a licence expiring on the 1st (0 days left) and one expiring on the 31st (30 days left) are both reminded, while one that lapsed on 30 June and one expiring on 1 August are not. A NULL expiry never satisfies BETWEEN, but only PAN rows have one and they are filtered by type anyway.

days_left is DATEDIFF(expires_on, '2024-07-01'); the first alternative uses TIMESTAMPDIFF(DAY, …), which gives the same whole days for plain dates. The second avoids the join by looking the name up with a scalar subquery and the status with IN. Ties on days_left go by driver_id.

Reference solution (MySQL)

SELECT d.driver_id, d.full_name, x.expires_on, DATEDIFF(x.expires_on, '2024-07-01') AS days_left
FROM Driver d
JOIN DriverDocument x ON x.driver_id = d.driver_id
WHERE d.status = 'active'
  AND x.doc_type = 'driving_licence'
  AND x.expires_on BETWEEN '2024-07-01' AND DATE_ADD('2024-07-01', INTERVAL 30 DAY)
ORDER BY days_left, d.driver_id

Another way

SELECT d.driver_id, d.full_name, x.expires_on, TIMESTAMPDIFF(DAY, '2024-07-01', x.expires_on) AS days_left FROM Driver d JOIN DriverDocument x ON x.driver_id = d.driver_id AND x.doc_type = 'driving_licence' WHERE d.status = 'active' AND TIMESTAMPDIFF(DAY, '2024-07-01', x.expires_on) BETWEEN 0 AND 30 ORDER BY days_left, d.driver_id

Another way

SELECT x.driver_id, (SELECT full_name FROM Driver d WHERE d.driver_id = x.driver_id) AS full_name, x.expires_on, DATEDIFF(x.expires_on, '2024-07-01') AS days_left FROM DriverDocument x WHERE x.doc_type = 'driving_licence' AND x.expires_on >= '2024-07-01' AND x.expires_on <= '2024-07-31' AND x.driver_id IN (SELECT driver_id FROM Driver WHERE status = 'active') ORDER BY days_left, x.driver_id

← Ola: Wallets Topped Up Over ₹10,000 in May 2024 · Ola: Test Ride Bookings and No-Shows per Experience Centre →