Hotstar: Worst Rebuffering CDN and Device in Each City

Medium
710.

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

ColumnType
session_idint
cityvarchar
cdnvarchar
device_typevarchar
rebuffer_eventsint

Primary key: session_id.

One row per viewing session, with the number of times playback stalled to rebuffer (0 or more).

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

StreamQuality

session_idcitycdndevice_typerebuffer_events
1MumbaiEdgeNorthmobile4
2MumbaiEdgeNorthmobile3
3MumbaiSkyRelaytv7
4MumbaiSkyRelaymobile2
5PuneMetroCacheweb5
6PuneEdgeSouthtv1
7IndoreEdgeNorthtv0
8IndoreSkyRelaymobile0

Output

citycdndevice_typerebuffer_events
MumbaiEdgeNorthmobile7
MumbaiSkyRelaytv7
PuneMetroCacheweb5
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.

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

ColumnType
session_idint
cityvarchar
cdnvarchar
device_typevarchar
rebuffer_eventsint

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_idcitycdndevice_typerebuffer_events
1MumbaiEdgeNorthmobile4
2MumbaiEdgeNorthmobile3
3MumbaiSkyRelaytv7
4MumbaiSkyRelaymobile2
5PuneMetroCacheweb5
6PuneEdgeSouthtv1
7IndoreEdgeNorthtv0
8IndoreSkyRelaymobile0

Output

citycdndevice_typerebuffer_events
MumbaiEdgeNorthmobile7
MumbaiSkyRelaytv7
PuneMetroCacheweb5

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 > 0

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