Ola: Scooters Due for Their First Free Service

Easy
830.

Ola: Scooters Due for Their First Free Service

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

ColumnType
scooter_idint
owner_namevarchar
cityvarchar
delivered_ondate
odometer_kmint
first_service_ondate

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.

Example

The rows must come back in the order shown.

ScooterOwner

scooter_idowner_namecitydelivered_onodometer_kmfirst_service_on
1AaravBengaluru2024-06-20640NULL
2DiyaPune2024-08-281000NULL
3KabirChennai2024-07-03999NULL
4MeeraDelhi2024-07-04820NULL
5RohanHyderabad2024-05-301450NULL
6ZaraBengaluru2024-05-1221002024-08-01
7IshaanMumbai2024-09-251210NULL

Output

scooter_idowner_namedays_since_deliverydue_reason
5Rohan124both
1Aarav103time
3Kabir90time
2Diya34distance
7Ishaan6distance
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: 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

ColumnType
scooter_idint
owner_namevarchar
cityvarchar
delivered_ondate
odometer_kmint
first_service_ondate

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_idowner_namecitydelivered_onodometer_kmfirst_service_on
1AaravBengaluru2024-06-20640NULL
2DiyaPune2024-08-281000NULL
3KabirChennai2024-07-03999NULL
4MeeraDelhi2024-07-04820NULL
5RohanHyderabad2024-05-301450NULL
6ZaraBengaluru2024-05-1221002024-08-01
7IshaanMumbai2024-09-251210NULL

Output

scooter_idowner_namedays_since_deliverydue_reason
5Rohan124both
1Aarav103time
3Kabir90time
2Diya34distance
7Ishaan6distance

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_id

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