Ola: Scooters With Outlier Energy Use Within Their Model

Medium
831.

Ola: Scooters With Outlier Energy Use Within Their Model

Every month the fleet-efficiency report gives each scooter's average energy use in watt-hours per kilometre. A scooter far from the rest of its model may have a dragging brake, a weak pack or an unusual rider, so the battery team flags statistical outliers.

Use only the October 2024 report (report_month = '2024-10') and only scooters that rode that month (wh_per_km is not NULL). Within each model, let mean be the average wh_per_km and sd the population standard deviation, sd² = AVG(x²) − AVG(x)². A scooter is an outlier when it lies more than 2 standard deviations from its model's mean: (x − mean)² > 4·sd² (exactly 2 is not). Return scooter_id, model, wh_per_km and direction (high above the mean, low below it), ordered by model, then scooter_id. Compare in squared form — no square roots, so the test stays exact.

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: EfficiencyReport

ColumnType
scooter_idint
report_monthchar
modelvarchar
wh_per_kmint

Primary key (together): scooter_id, report_month.

One row per scooter per month, report_month as YYYY-MM. wh_per_km is NULL when the scooter was not ridden that month.

Example

The rows must come back in the order shown.

EfficiencyReport

scooter_idreport_monthmodelwh_per_km
12024-10S1 Pro30
22024-10S1 Pro31
32024-10S1 Pro29
42024-10S1 Pro30
52024-10S1 Pro32
62024-10S1 Pro30
72024-10S1 Pro48
82024-10S1 ProNULL
92024-10S1 X25
102024-10S1 X40
3 more rows

Output

scooter_idmodelwh_per_kmdirection
7S1 Pro48high
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 With Outlier Energy Use Within Their Model

Every month the fleet-efficiency report gives each scooter's average energy use in watt-hours per kilometre.

  • Difficulty: Medium
  • Topics: Aggregation, Joins, Conditional Logic
  • Dialect: MySQL
  • Problem: #831

Problem statement

Every month the fleet-efficiency report gives each scooter's average energy use in watt-hours per kilometre. A scooter far from the rest of its model may have a dragging brake, a weak pack or an unusual rider, so the battery team flags statistical outliers.

Use only the October 2024 report (report_month = '2024-10') and only scooters that rode that month (wh_per_km is not NULL). Within each model, let mean be the average wh_per_km and sd the population standard deviation, sd² = AVG(x²) − AVG(x)². A scooter is an outlier when it lies more than 2 standard deviations from its model's mean: (x − mean)² > 4·sd² (exactly 2 is not). Return scooter_id, model, wh_per_km and direction (high above the mean, low below it), ordered by model, then scooter_id. Compare in squared form — no square roots, so the test stays exact.

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: EfficiencyReport

ColumnType
scooter_idint
report_monthchar
modelvarchar
wh_per_kmint

Primary key: scooter_id, report_month.

One row per scooter per month, report_month as YYYY-MM. wh_per_km is NULL when the scooter was not ridden that month.

Examples

Example 1

EfficiencyReport

scooter_idreport_monthmodelwh_per_km
12024-10S1 Pro30
22024-10S1 Pro31
32024-10S1 Pro29
42024-10S1 Pro30
52024-10S1 Pro32
62024-10S1 Pro30
72024-10S1 Pro48
82024-10S1 ProNULL
92024-10S1 X25
102024-10S1 X40
112024-10S1 X33
72024-09S1 Pro31
122024-09S1 X90

Output

scooter_idmodelwh_per_kmdirection
7S1 Pro48high

How to solve Ola: Scooters With Outlier Energy Use Within Their Model

The mean ± 2 standard deviations rule needs the mean and the standard deviation of each model's values. Both come from three aggregates: the count n, the sum sx and the sum of squares sxx. The mean is sx / n, and the population variance is the mean of the squares minus the square of the mean, sxx / n − (sx / n)².

Taking a square root and comparing decimals invites rounding: MySQL keeps only four extra decimal places in a division, so a value near the boundary could flip. Squaring both sides of |x − mean| > 2·sd gives (x − mean)² > 4·sd², and multiplying by n² clears every fraction: (n·x − sx)² > 4·(n·sxx − sx²). Everything left is integer arithmetic, so the test is exact on any engine, and the strict > leaves a value at exactly two standard deviations unflagged. The sign of n·x − sx gives the direction.

The statistics must use exactly the rows being judged, so the October filter and the NULL filter go inside the aggregate (COUNT(wh_per_km) would also skip NULLs, but COUNT(*) would not). The solution aggregates per model and joins the statistics back; the alternative carries the same three sums onto every row with window functions and compares with POW. Note that with very few scooters no value can be an outlier: with n values none can lie more than √(n − 1) standard deviations out, so a model needs at least six scooters for the rule to fire.

Reference solution (MySQL)

WITH stats AS (
  SELECT model, COUNT(wh_per_km) AS n, SUM(wh_per_km) AS sx, SUM(wh_per_km * wh_per_km) AS sxx
  FROM EfficiencyReport
  WHERE report_month = '2024-10' AND wh_per_km IS NOT NULL
  GROUP BY model
)
SELECT e.scooter_id, e.model, e.wh_per_km,
       CASE WHEN e.wh_per_km * s.n > s.sx THEN 'high' ELSE 'low' END AS direction
FROM EfficiencyReport e
JOIN stats s ON s.model = e.model
WHERE e.report_month = '2024-10' AND e.wh_per_km IS NOT NULL
  AND (e.wh_per_km * s.n - s.sx) * (e.wh_per_km * s.n - s.sx) > 4 * (s.n * s.sxx - s.sx * s.sx)
ORDER BY e.model, e.scooter_id

Another way

WITH w AS (
  SELECT scooter_id, model, wh_per_km,
         COUNT(*) OVER (PARTITION BY model) AS n,
         SUM(wh_per_km) OVER (PARTITION BY model) AS sx,
         SUM(wh_per_km * wh_per_km) OVER (PARTITION BY model) AS sxx
  FROM EfficiencyReport
  WHERE report_month = '2024-10' AND wh_per_km IS NOT NULL
)
SELECT scooter_id, model, wh_per_km, IF(wh_per_km * n > sx, 'high', 'low') AS direction
FROM w
WHERE POW(n * wh_per_km - sx, 2) > 4 * (n * sxx - POW(sx, 2))
ORDER BY model, scooter_id

← Ola: Scooters Due for Their First Free Service · Ola: Busiest Hour of the Day at Each Hypercharger →