Hotstar: Worst Rebuffering CDN and Device in Each City
After a high-traffic match, Hotstar's CDN team looks city by city for the CDN and device combination that rebuffered the most, to decide where to shift…
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #710
Problem statement
After a high-traffic match, Hotstar's CDN team looks city by city for the CDN and device combination that rebuffered the most, to decide where to shift traffic before the next one.
For each city, add up rebuffer_events per (cdn, device_type) pair and return the pair or pairs with the highest total as city, cdn, device_type, rebuffer_events. Return every pair tied for the top. A city whose highest total is 0 is not listed. Return the rows in any order.
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: StreamQuality
| Column | Type |
|---|---|
| session_id | int |
| city | varchar |
| cdn | varchar |
| device_type | varchar |
| rebuffer_events | int |
Primary key: session_id.
One row per viewing session, with the number of times playback stalled to rebuffer (0 or more).
Examples
Example 1
StreamQuality
| session_id | city | cdn | device_type | rebuffer_events |
|---|---|---|---|---|
| 1 | Mumbai | EdgeNorth | mobile | 4 |
| 2 | Mumbai | EdgeNorth | mobile | 3 |
| 3 | Mumbai | SkyRelay | tv | 7 |
| 4 | Mumbai | SkyRelay | mobile | 2 |
| 5 | Pune | MetroCache | web | 5 |
| 6 | Pune | EdgeSouth | tv | 1 |
| 7 | Indore | EdgeNorth | tv | 0 |
| 8 | Indore | SkyRelay | mobile | 0 |
Output
| city | cdn | device_type | rebuffer_events |
|---|---|---|---|
| Mumbai | EdgeNorth | mobile | 7 |
| Mumbai | SkyRelay | tv | 7 |
| Pune | MetroCache | web | 5 |
How to solve Hotstar: Worst Rebuffering CDN and Device in Each City
The ranking is over totals, not sessions, so aggregate first: one row per city, CDN and device with the summed rebuffer events. Then rank those rows inside each city with RANK() OVER (PARTITION BY city ORDER BY rebuffer_events DESC). RANK (unlike ROW_NUMBER) gives every pair sharing the highest total the rank 1, which is how ties are all returned, as the statement asks.
A city where no session stalled has a top total of 0 — every pair there would tie at rank 1, but none of them is a problem worth reporting — so the final filter also requires rebuffer_events > 0. Indore in the example is dropped this way.
The alternative computes each city's maximum total in a derived table and joins back to keep the pairs equal to it; ties come out naturally because every pair equal to the maximum matches. Both aggregate the sessions once; the window version avoids the second pass over the totals.
Reference solution (MySQL)
WITH totals AS (
SELECT city, cdn, device_type, SUM(rebuffer_events) AS rebuffer_events
FROM StreamQuality
GROUP BY city, cdn, device_type
),
ranked AS (
SELECT city, cdn, device_type, rebuffer_events,
RANK() OVER (PARTITION BY city ORDER BY rebuffer_events DESC) AS rnk
FROM totals
)
SELECT city, cdn, device_type, rebuffer_events
FROM ranked
WHERE rnk = 1 AND rebuffer_events > 0Another way
WITH totals AS (
SELECT city, cdn, device_type, SUM(rebuffer_events) AS rebuffer_events
FROM StreamQuality GROUP BY city, cdn, device_type
)
SELECT t.city, t.cdn, t.device_type, t.rebuffer_events
FROM totals t
JOIN (SELECT city, MAX(rebuffer_events) AS top FROM totals GROUP BY city) m
ON m.city = t.city AND m.top = t.rebuffer_events
WHERE m.top > 0← Hotstar: Subscribers Who Only Ever Watch Live Sport · Hotstar: Accounts Streaming From Three Cities in a Day →