Median EV Charging Session Length by City — SQL Problem
Averages of plug-in time are skewed by cars left plugged in overnight, so the operator plans charger capacity on the median session length.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Dates
- Dialect: MySQL
- Problem: #270
Problem statement
Averages of plug-in time are skewed by cars left plugged in overnight, so the operator plans charger capacity on the median session length. A session's length is plugged_out minus plugged_in in whole minutes; a session still in progress (plugged_out NULL) is left out.
For every city with at least one finished session, return city, sessions (finished sessions) and median_minutes — the middle length when the city's lengths are sorted, or the average of the two middle lengths when the count is even, rounded to one decimal. Order by city.
Tables
Table: ChargePoint
| Column | Type |
|---|---|
| charge_point_id | int |
| city | varchar |
| power_kw | int |
Primary key: charge_point_id.
Table: PlugSession
| Column | Type |
|---|---|
| session_id | int |
| charge_point_id | int |
| plugged_in | datetime |
| plugged_out | datetime |
Primary key: session_id.
Times are whole minutes; plugged_out is NULL while the car is still connected.
Examples
Example 1
ChargePoint
| charge_point_id | city | power_kw |
|---|---|---|
| 1 | Bengaluru | 60 |
| 2 | Bengaluru | 30 |
| 3 | Pune | 60 |
| 4 | Chennai | 120 |
PlugSession
| session_id | charge_point_id | plugged_in | plugged_out |
|---|---|---|---|
| 1 | 1 | 2024-12-01 09:00:00 | 2024-12-01 09:40:00 |
| 2 | 2 | 2024-12-01 10:00:00 | 2024-12-01 11:30:00 |
| 3 | 1 | 2024-12-01 20:00:00 | 2024-12-02 07:00:00 |
| 4 | 2 | 2024-12-02 08:00:00 | 2024-12-02 08:25:00 |
| 5 | 3 | 2024-12-02 09:10:00 | 2024-12-02 10:05:00 |
| 6 | 3 | 2024-12-02 18:00:00 | 2024-12-02 18:30:00 |
| 7 | 3 | 2024-12-03 07:45:00 | 2024-12-03 08:30:00 |
| 8 | 4 | 2024-12-03 09:00:00 | NULL |
Output
| city | sessions | median_minutes |
|---|---|---|
| Bengaluru | 4 | 65 |
| Pune | 3 | 45 |
How to solve Median EV Charging Session Length by City
MySQL has no MEDIAN aggregate, so the median is built from window functions. First compute each finished session's length with TIMESTAMPDIFF(MINUTE, plugged_in, plugged_out) and attach its city through the charge point; in-progress sessions are filtered out so they cannot shift the middle.
Then ROW_NUMBER() OVER (PARTITION BY city ORDER BY mins) gives each length its position and COUNT(*) OVER (PARTITION BY city) the city's total n. The middle positions are FLOOR((n+1)/2) and FLOOR((n+2)/2): for n = 3 both are 2; for n = 4 they are 2 and 3. Averaging the one or two rows at those positions gives the median in both cases, and duplicates are harmless because positions, not values, are picked. MAX(cnt) just carries the per-city count through the GROUP BY.
The alternative uses the definition instead: a value is a median candidate when at least half the city's lengths are ≤ it and at least half are ≥ it. Those candidates are exactly the one or two middle values, so AVG(DISTINCT …) of them is the median. It is quadratic per city; the window version is one sort.
Reference solution (MySQL)
WITH lengths AS (
SELECT cp.city, TIMESTAMPDIFF(MINUTE, s.plugged_in, s.plugged_out) AS mins
FROM PlugSession s
JOIN ChargePoint cp ON cp.charge_point_id = s.charge_point_id
WHERE s.plugged_out IS NOT NULL
), ranked AS (
SELECT city, mins,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY mins) AS rn,
COUNT(*) OVER (PARTITION BY city) AS cnt
FROM lengths
)
SELECT city, MAX(cnt) AS sessions, ROUND(AVG(mins), 1) AS median_minutes
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY city
ORDER BY cityAnother way
WITH lengths AS (
SELECT s.session_id, cp.city, TIMESTAMPDIFF(MINUTE, s.plugged_in, s.plugged_out) AS mins
FROM PlugSession s JOIN ChargePoint cp ON cp.charge_point_id = s.charge_point_id
WHERE s.plugged_out IS NOT NULL
)
SELECT a.city, (SELECT COUNT(*) FROM lengths c WHERE c.city = a.city) AS sessions, ROUND(AVG(DISTINCT a.mins), 1) AS median_minutes
FROM lengths a
WHERE 2 * (SELECT COUNT(*) FROM lengths b WHERE b.city = a.city AND b.mins <= a.mins) >= (SELECT COUNT(*) FROM lengths c WHERE c.city = a.city)
AND 2 * (SELECT COUNT(*) FROM lengths b WHERE b.city = a.city AND b.mins >= a.mins) >= (SELECT COUNT(*) FROM lengths c WHERE c.city = a.city)
GROUP BY a.city
ORDER BY a.city← Sustained Vibration Breach Episodes · Solar Daily Output With Missing Days Filled →