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
| Column | Type |
|---|---|
| scooter_id | int |
| report_month | char |
| model | varchar |
| wh_per_km | int |
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_id | report_month | model | wh_per_km |
|---|---|---|---|
| 1 | 2024-10 | S1 Pro | 30 |
| 2 | 2024-10 | S1 Pro | 31 |
| 3 | 2024-10 | S1 Pro | 29 |
| 4 | 2024-10 | S1 Pro | 30 |
| 5 | 2024-10 | S1 Pro | 32 |
| 6 | 2024-10 | S1 Pro | 30 |
| 7 | 2024-10 | S1 Pro | 48 |
| 8 | 2024-10 | S1 Pro | NULL |
| 9 | 2024-10 | S1 X | 25 |
| 10 | 2024-10 | S1 X | 40 |
| 11 | 2024-10 | S1 X | 33 |
| 7 | 2024-09 | S1 Pro | 31 |
| 12 | 2024-09 | S1 X | 90 |
Output
| scooter_id | model | wh_per_km | direction |
|---|---|---|---|
| 7 | S1 Pro | 48 | high |
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_idAnother 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 →