Median EV Charging Session Length by City

Hard
270.

Median EV Charging Session Length by City

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

ColumnType
charge_point_idint
cityvarchar
power_kwint

Primary key: charge_point_id.

Table: PlugSession

ColumnType
session_idint
charge_point_idint
plugged_indatetime
plugged_outdatetime

Primary key: session_id.

Times are whole minutes; plugged_out is NULL while the car is still connected.

Example

The rows must come back in the order shown.

ChargePoint

charge_point_idcitypower_kw
1Bengaluru60
2Bengaluru30
3Pune60
4Chennai120

PlugSession

session_idcharge_point_idplugged_inplugged_out
112024-12-01 09:00:002024-12-01 09:40:00
222024-12-01 10:00:002024-12-01 11:30:00
312024-12-01 20:00:002024-12-02 07:00:00
422024-12-02 08:00:002024-12-02 08:25:00
532024-12-02 09:10:002024-12-02 10:05:00
632024-12-02 18:00:002024-12-02 18:30:00
732024-12-03 07:45:002024-12-03 08:30:00
842024-12-03 09:00:00NULL

Output

citysessionsmedian_minutes
Bengaluru465
Pune345
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.

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

ColumnType
charge_point_idint
cityvarchar
power_kwint

Primary key: charge_point_id.

Table: PlugSession

ColumnType
session_idint
charge_point_idint
plugged_indatetime
plugged_outdatetime

Primary key: session_id.

Times are whole minutes; plugged_out is NULL while the car is still connected.

Examples

Example 1

ChargePoint

charge_point_idcitypower_kw
1Bengaluru60
2Bengaluru30
3Pune60
4Chennai120

PlugSession

session_idcharge_point_idplugged_inplugged_out
112024-12-01 09:00:002024-12-01 09:40:00
222024-12-01 10:00:002024-12-01 11:30:00
312024-12-01 20:00:002024-12-02 07:00:00
422024-12-02 08:00:002024-12-02 08:25:00
532024-12-02 09:10:002024-12-02 10:05:00
632024-12-02 18:00:002024-12-02 18:30:00
732024-12-03 07:45:002024-12-03 08:30:00
842024-12-03 09:00:00NULL

Output

citysessionsmedian_minutes
Bengaluru465
Pune345

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 city

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