Ola: Scooters Covered by a Care Plan on Each Month End
Owners can buy a paid service-and-warranty plan for their scooter and renew it, sometimes before the old plan runs out.
- Difficulty: Hard
- Topics: Dates, Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #844
Problem statement
Owners can buy a paid service-and-warranty plan for their scooter and renew it, sometimes before the old plan runs out. Finance reports the covered base as a snapshot on the last day of every month.
A scooter is covered on a date when at least one of its plans has starts_on on or before that date and ends_on on or after it (both days included); a scooter with two overlapping plans still counts once. For every city that appears in CarePlan and every month end from 2024-01-31 to 2024-06-30 (the last day of each month, January to June), return city, month_end and covered_scooters — 0 when nothing was covered, so each city has six rows. Order the rows by city, then month_end.
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: CarePlan
| Column | Type |
|---|---|
| plan_id | int |
| scooter_id | int |
| city | varchar |
| starts_on | date |
| ends_on | date |
Primary key: plan_id.
One row per plan bought; a renewal is a new row. A scooter's city never changes, and ends_on is never before starts_on.
Examples
Example 1
CarePlan
| plan_id | scooter_id | city | starts_on | ends_on |
|---|---|---|---|---|
| 1 | 901 | Bengaluru | 2023-12-15 | 2024-03-14 |
| 2 | 901 | Bengaluru | 2024-03-01 | 2024-08-31 |
| 3 | 902 | Bengaluru | 2024-01-31 | 2024-02-29 |
| 4 | 903 | Bengaluru | 2024-04-01 | 2024-04-29 |
| 5 | 904 | Pune | 2024-02-01 | 2024-05-31 |
| 6 | 905 | Pune | 2024-06-30 | 2024-12-29 |
Output
| city | month_end | covered_scooters |
|---|---|---|
| Bengaluru | 2024-01-31 | 2 |
| Bengaluru | 2024-02-29 | 2 |
| Bengaluru | 2024-03-31 | 1 |
| Bengaluru | 2024-04-30 | 1 |
| Bengaluru | 2024-05-31 | 1 |
| Bengaluru | 2024-06-30 | 1 |
| Pune | 2024-01-31 | 0 |
| Pune | 2024-02-29 | 1 |
| Pune | 2024-03-31 | 1 |
| Pune | 2024-04-30 | 1 |
| Pune | 2024-05-31 | 1 |
| Pune | 2024-06-30 | 1 |
How to solve Ola: Scooters Covered by a Care Plan on Each Month End
A month-end snapshot asks for the state of the data on a set of dates that are not in the data. So the dates come first: a recursive CTE starts at 2024-01-31 and repeatedly steps one day past the previous month end and takes LAST_DAY of that, giving 01-31, 02-29, 03-31, 04-30, 05-31 and 06-30. Adding a month to 01-31 and hoping for the right day is fragile; going to the first of the next month and taking its last day is not.
The answer has a row for every city and every date, so the grid is the CROSS JOIN of the distinct cities with the six dates. Plans are then attached with a LEFT JOIN whose ON holds the whole coverage test — same city, started on or before the date, ending on or after it. Keeping the test in ON (not WHERE) is what lets an uncovered city-date survive with NULLs, and COUNT(DISTINCT p.scooter_id) turns those into 0 while counting a scooter with an overlapping renewal once. A plan ending exactly on a month end still covers it; one starting the next day does not.
The alternative lists the six month ends literally with LAST_DAY in a UNION ALL derived table and counts with a correlated subquery per grid cell. The join version touches each plan once per snapshot date it is compared with; with an index on (city, starts_on) either is cheap.
Reference solution (MySQL)
WITH RECURSIVE month_ends AS (
SELECT CAST('2024-01-31' AS DATE) AS month_end
UNION ALL
SELECT LAST_DAY(DATE_ADD(month_end, INTERVAL 1 DAY)) FROM month_ends WHERE month_end < '2024-06-30'
), cities AS (
SELECT DISTINCT city FROM CarePlan
)
SELECT c.city, m.month_end, COUNT(DISTINCT p.scooter_id) AS covered_scooters
FROM cities c
CROSS JOIN month_ends m
LEFT JOIN CarePlan p ON p.city = c.city AND p.starts_on <= m.month_end AND p.ends_on >= m.month_end
GROUP BY c.city, m.month_end
ORDER BY c.city, m.month_endAnother way
SELECT c.city, m.month_end,
(SELECT COUNT(DISTINCT p.scooter_id) FROM CarePlan p
WHERE p.city = c.city AND m.month_end BETWEEN p.starts_on AND p.ends_on) AS covered_scooters
FROM (SELECT DISTINCT city FROM CarePlan) c
CROSS JOIN (
SELECT LAST_DAY('2024-01-01') AS month_end UNION ALL SELECT LAST_DAY('2024-02-01')
UNION ALL SELECT LAST_DAY('2024-03-01') UNION ALL SELECT LAST_DAY('2024-04-01')
UNION ALL SELECT LAST_DAY('2024-05-01') UNION ALL SELECT LAST_DAY('2024-06-01')
) m
ORDER BY c.city, m.month_end← Ola: Driver-Partner Activation and Retention by Approval Month · Ola: Peak Simultaneous Charging Sessions at Each Station →