Hotstar: Buffering Share of Watch Time by CDN

Easy
701.

Hotstar: Buffering Share of Watch Time by CDN

EasyAggregation

MySQL

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

ColumnType
session_idint
cdnvarchar
device_typevarchar
watch_secsint
buffer_secsint

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.

Example

The rows must come back in the order shown.

PlaybackSession

session_idcdndevice_typewatch_secsbuffer_secs
1EdgeNorthmobile120030
2EdgeNorthtv60015
3SkyRelaymobile90090
4SkyRelayweb300NULL
5MetroCachetv15000
6MetroCachemobile50020
7EdgeSouthtablet100010
8Originweb700NULL

Output

cdnsessionsbuffer_pct
SkyRelay110
EdgeNorth22.5
EdgeSouth11
MetroCache21
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: 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

ColumnType
session_idint
cdnvarchar
device_typevarchar
watch_secsint
buffer_secsint

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_idcdndevice_typewatch_secsbuffer_secs
1EdgeNorthmobile120030
2EdgeNorthtv60015
3SkyRelaymobile90090
4SkyRelayweb300NULL
5MetroCachetv15000
6MetroCachemobile50020
7EdgeSouthtablet100010
8Originweb700NULL

Output

cdnsessionsbuffer_pct
SkyRelay110
EdgeNorth22.5
EdgeSouth11
MetroCache21

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

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