Ola: Scooters Still Below MoveOS 4.2

Easy
823.

Ola: Scooters Still Below MoveOS 4.2

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

ColumnType
scooter_idint
modelvarchar
cityvarchar
os_majorint
os_minorint

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.

Example

The rows must come back in the order shown.

Scooter

scooter_idmodelcityos_majoros_minor
1S1 ProBengaluru42
2S1 AirPune41
3S1 XChennai39
4S1 ProDelhi50
5S1 XPuneNULLNULL
6S1 AirBengaluru43
7S1 ProHyderabad40

Output

scooter_idmodelcitycurrent_version
2S1 AirPune4.1
3S1 XChennai3.9
5S1 XPuneNULL
7S1 ProHyderabad4.0
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 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

ColumnType
scooter_idint
modelvarchar
cityvarchar
os_majorint
os_minorint

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_idmodelcityos_majoros_minor
1S1 ProBengaluru42
2S1 AirPune41
3S1 XChennai39
4S1 ProDelhi50
5S1 XPuneNULLNULL
6S1 AirBengaluru43
7S1 ProHyderabad40

Output

scooter_idmodelcitycurrent_version
2S1 AirPune4.1
3S1 XChennai3.9
5S1 XPuneNULL
7S1 ProHyderabad4.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_id

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

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