Ola: Scooters Covered by a Care Plan on Each Month End

Hard
844.

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. 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

ColumnType
plan_idint
scooter_idint
cityvarchar
starts_ondate
ends_ondate

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.

Example

The rows must come back in the order shown.

CarePlan

plan_idscooter_idcitystarts_onends_on
1901Bengaluru2023-12-152024-03-14
2901Bengaluru2024-03-012024-08-31
3902Bengaluru2024-01-312024-02-29
4903Bengaluru2024-04-012024-04-29
5904Pune2024-02-012024-05-31
6905Pune2024-06-302024-12-29

Output

citymonth_endcovered_scooters
Bengaluru2024-01-312
Bengaluru2024-02-292
Bengaluru2024-03-311
Bengaluru2024-04-301
Bengaluru2024-05-311
Bengaluru2024-06-301
Pune2024-01-310
Pune2024-02-291
Pune2024-03-311
Pune2024-04-301
2 more rows
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 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

ColumnType
plan_idint
scooter_idint
cityvarchar
starts_ondate
ends_ondate

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_idscooter_idcitystarts_onends_on
1901Bengaluru2023-12-152024-03-14
2901Bengaluru2024-03-012024-08-31
3902Bengaluru2024-01-312024-02-29
4903Bengaluru2024-04-012024-04-29
5904Pune2024-02-012024-05-31
6905Pune2024-06-302024-12-29

Output

citymonth_endcovered_scooters
Bengaluru2024-01-312
Bengaluru2024-02-292
Bengaluru2024-03-311
Bengaluru2024-04-301
Bengaluru2024-05-311
Bengaluru2024-06-301
Pune2024-01-310
Pune2024-02-291
Pune2024-03-311
Pune2024-04-301
Pune2024-05-311
Pune2024-06-301

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_end

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