Ola: Scooters Still Below MoveOS 4.2 — SQL Basics Problem
Before a battery-safety fix ships, the electric fleet team at Ola wants every scooter that is not yet on software 4.2 or later, so it can be nudged to update.
- Difficulty: Easy
- Topics: Basics, Conditional Logic
- Dialect: MySQL
- Problem: #823
Problem statement
Before a battery-safety fix ships, the electric fleet team at Ola wants every scooter that is not yet on software 4.2 or later, so it can be nudged to update.
A version is os_major.os_minor: 3.9 is older than 4.0, 4.1 is older than 4.2, and 5.0 is newer. A scooter that has never reported a version (os_major is NULL) must be listed too, since nobody can confirm it is safe.
Return scooter_id, model, city and current_version — the version written as os_major, a dot and os_minor (for example 4.1), or NULL when it was never reported — ordered 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: Scooter
| Column | Type |
|---|---|
| scooter_id | int |
| model | varchar |
| city | varchar |
| os_major | int |
| os_minor | int |
Primary key: scooter_id.
One row per scooter sold. os_major and os_minor are both NULL until the scooter first reports its software version; os_minor is always below 100.
Examples
Example 1
Scooter
| scooter_id | model | city | os_major | os_minor |
|---|---|---|---|---|
| 1 | S1 Pro | Bengaluru | 4 | 2 |
| 2 | S1 Air | Pune | 4 | 1 |
| 3 | S1 X | Chennai | 3 | 9 |
| 4 | S1 Pro | Delhi | 5 | 0 |
| 5 | S1 X | Pune | NULL | NULL |
| 6 | S1 Air | Bengaluru | 4 | 3 |
| 7 | S1 Pro | Hyderabad | 4 | 0 |
Output
| scooter_id | model | city | current_version |
|---|---|---|---|
| 2 | S1 Air | Pune | 4.1 |
| 3 | S1 X | Chennai | 3.9 |
| 5 | S1 X | Pune | NULL |
| 7 | S1 Pro | Hyderabad | 4.0 |
How to solve Ola: Scooters Still Below MoveOS 4.2
A two-part version is compared like a word in a dictionary: the major number decides, and only on a tie does the minor number count. So "older than 4.2" is os_major < 4 OR (os_major = 4 AND os_minor < 2). Writing just os_minor < 2 would wrongly drop 3.9 and wrongly keep 5.0.
Scooters that never reported have both parts NULL. Every comparison with NULL is unknown, which WHERE treats as false, so they need their own os_major IS NULL branch. The display column is a CONCAT of the two parts with a dot; MySQL's CONCAT returns NULL when any argument is NULL, which gives the NULL the statement asks for without a CASE.
The first alternative packs the version into one number, major * 100 + minor (safe because the minor part is below 100), and maps a missing version to 0 with COALESCE so it falls under 402. The second negates "4.2 or later" and adds the NULL branch back. All of them are one scan of the table.
Reference solution (MySQL)
SELECT scooter_id, model, city, CONCAT(os_major, '.', os_minor) AS current_version
FROM Scooter
WHERE os_major IS NULL
OR os_major < 4
OR (os_major = 4 AND os_minor < 2)
ORDER BY scooter_idAnother way
SELECT scooter_id, model, city, CONCAT(os_major, '.', os_minor) AS current_version FROM Scooter WHERE COALESCE(os_major * 100 + os_minor, 0) < 402 ORDER BY scooter_idAnother way
SELECT scooter_id, model, city, CASE WHEN os_major IS NULL THEN NULL ELSE CONCAT_WS('.', os_major, os_minor) END AS current_version FROM Scooter WHERE NOT (os_major > 4 OR (os_major = 4 AND os_minor >= 2)) OR os_major IS NULL ORDER BY scooter_id← Myntra: Serial Returners by Their Last Five Delivered Orders · Ola: Hypercharger Stations Idle Through a Whole Week →