Ola: OTA Update Adoption in Its First Week, by City

Medium
838.

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.

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

ColumnType
scooter_idint
modelvarchar
cityvarchar

Primary key: scooter_id.

One row per scooter in the fleet.

Table: OtaRelease

ColumnType
release_idint
versionvarchar
released_ondate

Primary key: release_id.

One row per release; versions are unique.

Table: OtaInstall

ColumnType
install_idint
scooter_idint
release_idint
installed_atdatetime

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.

Example

The rows must come back in the order shown.

Scooter

scooter_idmodelcity
1S1 ProBengaluru
2S1 AirBengaluru
3S1 XBengaluru
4S1 ProPune
5S1 AirPune

OtaRelease

release_idversionreleased_on
13.2.12024-07-15
24.0.02024-09-02

OtaInstall

install_idscooter_idrelease_idinstalled_at
1122024-09-02 21:00:00
2222024-09-09 00:00:00
3222024-09-12 08:00:00
4312024-07-20 10:00:00
5422024-09-08 23:59:59
6422024-09-10 09:00:00
7512024-07-18 10:00:00

Output

cityfleetwithin_weeknot_installed
Bengaluru311
Pune211
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: 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

ColumnType
scooter_idint
modelvarchar
cityvarchar

Primary key: scooter_id.

One row per scooter in the fleet.

Table: OtaRelease

ColumnType
release_idint
versionvarchar
released_ondate

Primary key: release_id.

One row per release; versions are unique.

Table: OtaInstall

ColumnType
install_idint
scooter_idint
release_idint
installed_atdatetime

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_idmodelcity
1S1 ProBengaluru
2S1 AirBengaluru
3S1 XBengaluru
4S1 ProPune
5S1 AirPune

OtaRelease

release_idversionreleased_on
13.2.12024-07-15
24.0.02024-09-02

OtaInstall

install_idscooter_idrelease_idinstalled_at
1122024-09-02 21:00:00
2222024-09-09 00:00:00
3222024-09-12 08:00:00
4312024-07-20 10:00:00
5422024-09-08 23:59:59
6422024-09-10 09:00:00
7512024-07-18 10:00:00

Output

cityfleetwithin_weeknot_installed
Bengaluru311
Pune211

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

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