Ola: Scooters Due for Their First Free Service — SQL Problem
A new scooter's first free service falls due 90 days after delivery or at 1,000 km, whichever comes first.
- Difficulty: Easy
- Topics: Dates, Conditional Logic
- Dialect: MySQL
- Problem: #830
Problem statement
A new scooter's first free service falls due 90 days after delivery or at 1,000 km, whichever comes first. On 2024-10-01 the after-sales team calls owners whose scooter is due and has not been serviced yet.
Return every scooter with no first service recorded (first_service_on is NULL) that is due on 2024-10-01: at least 90 days since delivered_on (DATEDIFF('2024-10-01', delivered_on) >= 90) or an odometer_km of at least 1000. Return scooter_id, owner_name, days_since_delivery and due_reason — both when both limits are reached, distance when only the kilometres are, time when only the days are. Order the rows by days_since_delivery from most to fewest, then by scooter_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: ScooterOwner
| Column | Type |
|---|---|
| scooter_id | int |
| owner_name | varchar |
| city | varchar |
| delivered_on | date |
| odometer_km | int |
| first_service_on | date |
Primary key: scooter_id.
One row per delivered scooter, with its latest odometer reading. first_service_on is NULL until the first free service is done. Every delivery is on or before 2024-10-01.
Examples
Example 1
ScooterOwner
| scooter_id | owner_name | city | delivered_on | odometer_km | first_service_on |
|---|---|---|---|---|---|
| 1 | Aarav | Bengaluru | 2024-06-20 | 640 | NULL |
| 2 | Diya | Pune | 2024-08-28 | 1000 | NULL |
| 3 | Kabir | Chennai | 2024-07-03 | 999 | NULL |
| 4 | Meera | Delhi | 2024-07-04 | 820 | NULL |
| 5 | Rohan | Hyderabad | 2024-05-30 | 1450 | NULL |
| 6 | Zara | Bengaluru | 2024-05-12 | 2100 | 2024-08-01 |
| 7 | Ishaan | Mumbai | 2024-09-25 | 1210 | NULL |
Output
| scooter_id | owner_name | days_since_delivery | due_reason |
|---|---|---|---|
| 5 | Rohan | 124 | both |
| 1 | Aarav | 103 | time |
| 3 | Kabir | 90 | time |
| 2 | Diya | 34 | distance |
| 7 | Ishaan | 6 | distance |
How to solve Ola: Scooters Due for Their First Free Service
Two independent triggers make a scooter due — elapsed time and distance — and the scooter qualifies as soon as either fires, so the filter is days >= 90 OR odometer_km >= 1000, together with first_service_on IS NULL for scooters that have not been serviced. The parentheses around the OR matter: without them AND binds tighter and serviced scooters with high mileage would slip in.
DATEDIFF('2024-10-01', delivered_on) counts whole days, so a scooter delivered on 2024-07-03 is exactly 90 days old and due, while one delivered a day later is not. The reason label is a CASE whose branches are tried in order: both limits reached gives both; otherwise, since the row passed the filter, exactly one limit fired, and testing the distance next leaves time for the ELSE.
The alternative computes the two triggers as 0/1 flags in a derived table — the time test written as delivered_on <= DATE_SUB('2024-10-01', INTERVAL 90 DAY) — and builds the label with nested IFs. Both are a single scan; the date comparison form can use an index on delivered_on.
Reference solution (MySQL)
SELECT scooter_id, owner_name,
DATEDIFF('2024-10-01', delivered_on) AS days_since_delivery,
CASE WHEN DATEDIFF('2024-10-01', delivered_on) >= 90 AND odometer_km >= 1000 THEN 'both'
WHEN odometer_km >= 1000 THEN 'distance'
ELSE 'time' END AS due_reason
FROM ScooterOwner
WHERE first_service_on IS NULL
AND (DATEDIFF('2024-10-01', delivered_on) >= 90 OR odometer_km >= 1000)
ORDER BY days_since_delivery DESC, scooter_idAnother way
SELECT scooter_id, owner_name, days_since_delivery,
IF(by_time = 1, IF(by_km = 1, 'both', 'time'), 'distance') AS due_reason
FROM (
SELECT scooter_id, owner_name, first_service_on,
TIMESTAMPDIFF(DAY, delivered_on, '2024-10-01') AS days_since_delivery,
IF(delivered_on <= DATE_SUB('2024-10-01', INTERVAL 90 DAY), 1, 0) AS by_time,
IF(odometer_km >= 1000, 1, 0) AS by_km
FROM ScooterOwner
) s
WHERE first_service_on IS NULL AND by_time + by_km > 0
ORDER BY days_since_delivery DESC, scooter_id← Ola: Registration Plates Cleaned for the Compliance Upload · Ola: Scooters With Outlier Energy Use Within Their Model →