Hotstar: Buffering Share of Watch Time by CDN — SQL Problem
Hotstar spreads a big match across several content delivery networks, and the video-quality team ranks them by how much of the viewing time was spent…
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #701
Problem statement
Hotstar spreads a big match across several content delivery networks, and the video-quality team ranks them by how much of the viewing time was spent staring at a spinner. Some player builds fail to report buffering, leaving buffer_secs NULL; those sessions are left out entirely — from the session count and from both sums.
For every CDN with at least one reported session, return cdn, sessions (reported sessions) and buffer_pct = 100 × total buffer_secs ÷ total watch_secs, rounded to 2 decimal places. Order by buffer_pct descending, then cdn ascending.
A practice scenario set at Hotstar: the tables and data are invented, and this is not a question from, or endorsed by, Hotstar.
Tables
Table: PlaybackSession
| Column | Type |
|---|---|
| session_id | int |
| cdn | varchar |
| device_type | varchar |
| watch_secs | int |
| buffer_secs | int |
Primary key: session_id.
One row per playback session. watch_secs is always at least 60; buffer_secs is NULL when the player did not report it.
Examples
Example 1
PlaybackSession
| session_id | cdn | device_type | watch_secs | buffer_secs |
|---|---|---|---|---|
| 1 | EdgeNorth | mobile | 1200 | 30 |
| 2 | EdgeNorth | tv | 600 | 15 |
| 3 | SkyRelay | mobile | 900 | 90 |
| 4 | SkyRelay | web | 300 | NULL |
| 5 | MetroCache | tv | 1500 | 0 |
| 6 | MetroCache | mobile | 500 | 20 |
| 7 | EdgeSouth | tablet | 1000 | 10 |
| 8 | Origin | web | 700 | NULL |
Output
| cdn | sessions | buffer_pct |
|---|---|---|
| SkyRelay | 1 | 10 |
| EdgeNorth | 2 | 2.5 |
| EdgeSouth | 1 | 1 |
| MetroCache | 2 | 1 |
How to solve Hotstar: Buffering Share of Watch Time by CDN
The buffering share of a CDN is total buffering time over total watch time — a ratio of sums. Averaging each session's own percentage would weight a two-minute session as heavily as a two-hour one, which is not what the team is measuring.
Sessions with buffer_secs NULL must vanish completely. Filtering them in WHERE does that in one place: they are not counted in sessions and their watch time does not dilute the denominator. (Simply relying on SUM skipping NULLs would still add their watch_secs to the denominator and their row to COUNT(*).) A CDN whose every session was unreported forms no group and is not listed.
The alternative keeps every row and makes each aggregate ignore the unreported sessions itself: COUNT(buffer_secs) counts non-NULLs, and the denominator only adds watch_secs where buffering was reported; HAVING drops CDNs left with nothing. Multiplying by 100 before dividing keeps the arithmetic exact, and the final order is by the rounded value, then the name for ties. One scan and a small sort.
Reference solution (MySQL)
SELECT cdn,
COUNT(*) AS sessions,
ROUND(100 * SUM(buffer_secs) / SUM(watch_secs), 2) AS buffer_pct
FROM PlaybackSession
WHERE buffer_secs IS NOT NULL
GROUP BY cdn
ORDER BY buffer_pct DESC, cdnAnother way
SELECT cdn, COUNT(buffer_secs) AS sessions,
ROUND(SUM(buffer_secs) * 100 / SUM(CASE WHEN buffer_secs IS NOT NULL THEN watch_secs END), 2) AS buffer_pct
FROM PlaybackSession
GROUP BY cdn
HAVING COUNT(buffer_secs) > 0
ORDER BY buffer_pct DESC, cdn← Hotstar: Plans With No Active Subscriber · Hotstar: Next-Day Views of Regional Serials →