Median Session Length by Device — SQL Problem
Product wants the median minutes watched per session on each device type — averages are skewed by people who leave the TV on overnight.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #494
Problem statement
Product wants the median minutes watched per session on each device type — averages are skewed by people who leave the TV on overnight. For an odd number of sessions the median is the middle value; for an even number it is the average of the two middle values. Equal values are ordinary values in the ordering.
Return one row per device that has sessions, with the columns device, sessions and median_minutes (rounded to 2 decimal places). Order by device.
Tables
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| device | enum(tv, mobile, web, tablet) |
| watched_minutes | int |
Primary key: session_id.
One row per playback session.
Examples
Example 1
WatchSession
| session_id | profile_id | device | watched_minutes |
|---|---|---|---|
| 1 | 1 | tv | 42 |
| 2 | 2 | tv | 300 |
| 3 | 3 | tv | 55 |
| 4 | 1 | mobile | 12 |
| 5 | 4 | mobile | 25 |
| 6 | 2 | mobile | 9 |
| 7 | 3 | mobile | 31 |
| 8 | 4 | web | 18 |
| 9 | 1 | web | 18 |
Output
| device | sessions | median_minutes |
|---|---|---|
| mobile | 4 | 18.5 |
| tv | 3 | 55 |
| web | 2 | 18 |
How to solve Median Session Length by Device
SQL has no portable MEDIAN, so build it from positions. ROW_NUMBER() OVER (PARTITION BY device ORDER BY watched_minutes, session_id) numbers each device's sessions from shortest to longest (the session_id tie-breaker only fixes which of two equal values is first — the values themselves are the same either way), and COUNT(*) OVER (PARTITION BY device) puts the device's total on every row.
For n rows the middle positions are (n + 1) DIV 2 and n DIV 2 + 1: for n = 3 both are 2; for n = 4 they are 2 and 3. Keeping those rows and averaging them gives the median in both cases. With integer minutes the median is a whole number or ends in .5, so the rounding never sits on a boundary. MAX(cnt) returns the count, which is the same on every row of the group.
The window-free alternative uses the definition: a value is a median candidate when at least half the sessions are ≤ it and at least half are ≥ it. Only the one or two middle values pass, so AVG(DISTINCT …) of the survivors is the median even when equal values repeat. It is quadratic per device; the window plan is one sort.
Reference solution (MySQL)
WITH ranked AS (
SELECT device, watched_minutes,
ROW_NUMBER() OVER (PARTITION BY device ORDER BY watched_minutes, session_id) AS rn,
COUNT(*) OVER (PARTITION BY device) AS cnt
FROM WatchSession
)
SELECT device, MAX(cnt) AS sessions, ROUND(AVG(watched_minutes), 2) AS median_minutes
FROM ranked
WHERE rn IN ((cnt + 1) DIV 2, cnt DIV 2 + 1)
GROUP BY device
ORDER BY deviceAnother way
SELECT a.device, (SELECT COUNT(*) FROM WatchSession c WHERE c.device = a.device) AS sessions,
ROUND(AVG(DISTINCT a.watched_minutes), 2) AS median_minutes
FROM WatchSession a
WHERE 2 * (SELECT COUNT(*) FROM WatchSession b WHERE b.device = a.device AND b.watched_minutes <= a.watched_minutes)
>= (SELECT COUNT(*) FROM WatchSession c WHERE c.device = a.device)
AND 2 * (SELECT COUNT(*) FROM WatchSession b WHERE b.device = a.device AND b.watched_minutes >= a.watched_minutes)
>= (SELECT COUNT(*) FROM WatchSession c WHERE c.device = a.device)
GROUP BY a.device
ORDER BY a.device← Next-Month Viewing Retention of Streaming Sign-Up Cohorts · Accounts Streaming Past Their Screen Limit →