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
| Column | Type |
|---|---|
| driver_id | int |
| full_name | varchar |
| city | varchar |
| status | enum(active, suspended, offboarded) |
Primary key: driver_id.
One row per driver-partner.
Table: DriverDocument
| Column | Type |
|---|---|
| doc_id | int |
| driver_id | int |
| doc_type | enum(driving_licence, vehicle_rc, insurance, pan) |
| expires_on | date |
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_id | full_name | city | status |
|---|---|---|---|
| 1 | Ravi Kumar | Bengaluru | active |
| 2 | Suresh Patil | Pune | active |
| 3 | Imran Shaikh | Mumbai | suspended |
| 4 | Gopal Rao | Hyderabad | active |
| 5 | Manoj Yadav | Delhi | active |
DriverDocument
| doc_id | driver_id | doc_type | expires_on |
|---|---|---|---|
| 11 | 1 | driving_licence | 2024-07-31 |
| 12 | 1 | insurance | 2024-07-05 |
| 13 | 2 | driving_licence | 2024-07-01 |
| 14 | 3 | driving_licence | 2024-07-10 |
| 15 | 4 | driving_licence | 2024-08-01 |
| 16 | 5 | driving_licence | 2024-06-30 |
| 17 | 5 | pan | NULL |
| 18 | 4 | vehicle_rc | 2024-07-15 |
Output
| driver_id | full_name | expires_on | days_left |
|---|---|---|---|
| 2 | Suresh Patil | 2024-07-01 | 0 |
| 1 | Ravi Kumar | 2024-07-31 | 30 |
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_idAnother 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_idAnother 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 →