Ola: OTA Update Adoption in Its First Week, by City
When the software team pushes over-the-air release 4.0.0, it tracks how quickly scooters in each city take it.
- Difficulty: Medium
- Topics: Joins, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #838
Problem statement
When the software team pushes over-the-air release 4.0.0, it tracks how quickly scooters in each city take it.
For every city that has scooters, return city, fleet (its scooters), within_week — scooters whose first install of release 4.0.0 happened before 7 days had passed since its released_on date (released on 2024-09-02 means installed before 2024-09-09 00:00:00) — and not_installed, scooters with no install of 4.0.0 at all. Installs of other releases do not count, and a scooter that retried the same install is still one scooter. Order the rows by city.
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: Scooter
| Column | Type |
|---|---|
| scooter_id | int |
| model | varchar |
| city | varchar |
Primary key: scooter_id.
One row per scooter in the fleet.
Table: OtaRelease
| Column | Type |
|---|---|
| release_id | int |
| version | varchar |
| released_on | date |
Primary key: release_id.
One row per release; versions are unique.
Table: OtaInstall
| Column | Type |
|---|---|
| install_id | int |
| scooter_id | int |
| release_id | int |
| installed_at | datetime |
Primary key: install_id.
One row per successful install, never before the release date; a scooter can install the same release again after a reset.
Examples
Example 1
Scooter
| scooter_id | model | city |
|---|---|---|
| 1 | S1 Pro | Bengaluru |
| 2 | S1 Air | Bengaluru |
| 3 | S1 X | Bengaluru |
| 4 | S1 Pro | Pune |
| 5 | S1 Air | Pune |
OtaRelease
| release_id | version | released_on |
|---|---|---|
| 1 | 3.2.1 | 2024-07-15 |
| 2 | 4.0.0 | 2024-09-02 |
OtaInstall
| install_id | scooter_id | release_id | installed_at |
|---|---|---|---|
| 1 | 1 | 2 | 2024-09-02 21:00:00 |
| 2 | 2 | 2 | 2024-09-09 00:00:00 |
| 3 | 2 | 2 | 2024-09-12 08:00:00 |
| 4 | 3 | 1 | 2024-07-20 10:00:00 |
| 5 | 4 | 2 | 2024-09-08 23:59:59 |
| 6 | 4 | 2 | 2024-09-10 09:00:00 |
| 7 | 5 | 1 | 2024-07-18 10:00:00 |
Output
| city | fleet | within_week | not_installed |
|---|---|---|---|
| Bengaluru | 3 | 1 | 1 |
| Pune | 2 | 1 | 1 |
How to solve Ola: OTA Update Adoption in Its First Week, by City
Every scooter must be counted in its city's fleet, so the query starts from Scooter and keeps it whole. The install history is first reduced to what matters: for release 4.0.0 only, each scooter's first install time (MIN(installed_at)). That collapses retries, so a scooter that installed twice is one scooter, and it ignores installs of other versions.
A LEFT JOIN of that per-scooter time onto every scooter leaves NULL for scooters that never installed. Two conditional sums then count per city: first_at before released_on + 7 days counts as within the week (NULL fails the comparison and adds 0), and a NULL first_at counts as not installed. Comparing a datetime against the date DATE_ADD(released_on, INTERVAL 7 DAY) means against its midnight, so an install at 23:59:59 on day 6 is in and one at 00:00:00 on day 7 is out. Testing "some install within the week" gives the same answer, because the earliest install is within the week whenever any install is.
The release date is read once from a one-row CTE joined with CROSS JOIN. The alternative tests each scooter with two EXISTS subqueries and measures the delay in seconds.
Reference solution (MySQL)
WITH rel AS (
SELECT release_id, released_on FROM OtaRelease WHERE version = '4.0.0'
), first_install AS (
SELECT i.scooter_id, MIN(i.installed_at) AS first_at
FROM OtaInstall i JOIN rel ON rel.release_id = i.release_id
GROUP BY i.scooter_id
)
SELECT s.city,
COUNT(*) AS fleet,
SUM(CASE WHEN f.first_at < DATE_ADD(rel.released_on, INTERVAL 7 DAY) THEN 1 ELSE 0 END) AS within_week,
SUM(CASE WHEN f.first_at IS NULL THEN 1 ELSE 0 END) AS not_installed
FROM Scooter s
CROSS JOIN rel
LEFT JOIN first_install f ON f.scooter_id = s.scooter_id
GROUP BY s.city
ORDER BY s.cityAnother way
SELECT s.city, COUNT(*) AS fleet,
SUM(CASE WHEN EXISTS (SELECT 1 FROM OtaInstall i JOIN OtaRelease r ON r.release_id = i.release_id
WHERE i.scooter_id = s.scooter_id AND r.version = '4.0.0'
AND TIMESTAMPDIFF(SECOND, r.released_on, i.installed_at) < 7 * 86400) THEN 1 ELSE 0 END) AS within_week,
SUM(CASE WHEN NOT EXISTS (SELECT 1 FROM OtaInstall i JOIN OtaRelease r ON r.release_id = i.release_id
WHERE i.scooter_id = s.scooter_id AND r.version = '4.0.0') THEN 1 ELSE 0 END) AS not_installed
FROM Scooter s
GROUP BY s.city
ORDER BY s.city← Ola: Driver-Partners Cleared on All Four Documents · Ola: Riders Whose Main Ride Moved From Auto to Cabs →