Hotstar: Matches That Crossed Two Crore Concurrent Viewers

Easy
704.

Hotstar: Matches That Crossed Two Crore Concurrent Viewers

Hotstar's live-ops dashboard samples concurrent viewers at the end of every over. For the season review, the analytics team lists the matches whose concurrency peaked at two crore (20,000,000) or more.

Return match_id, home_team, away_team and peak_viewers (the highest sampled concurrency of the match) for every such match. Matches with no samples are not listed. Order by peak_viewers descending, then match_id 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: Fixture

ColumnType
match_idint
home_teamvarchar
away_teamvarchar

Primary key: match_id.

One row per match of the league.

Table: OverSnapshot

ColumnType
match_idint
inningsint
over_noint
viewersbigint

Primary key (together): match_id, innings, over_no.

Concurrent viewers sampled at the end of over over_no of an innings.

Example

The rows must come back in the order shown.

Fixture

match_idhome_teamaway_team
1Mumbai MarinersChennai Chargers
2Delhi DynamosKolkata Knights
3Bengaluru BlastersHyderabad Hawks
4Punjab PanthersJaipur Jaguars

OverSnapshot

match_idinningsover_noviewers
111018500000
121824300000
122023900000
212012000000
221919999999
31615000000
322020000000

Output

match_idhome_teamaway_teampeak_viewers
1Mumbai MarinersChennai Chargers24300000
3Bengaluru BlastersHyderabad Hawks20000000
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: Matches That Crossed Two Crore Concurrent Viewers

Hotstar's live-ops dashboard samples concurrent viewers at the end of every over.

  • Difficulty: Easy
  • Topics: Aggregation, Joins
  • Dialect: MySQL
  • Problem: #704

Problem statement

Hotstar's live-ops dashboard samples concurrent viewers at the end of every over. For the season review, the analytics team lists the matches whose concurrency peaked at two crore (20,000,000) or more.

Return match_id, home_team, away_team and peak_viewers (the highest sampled concurrency of the match) for every such match. Matches with no samples are not listed. Order by peak_viewers descending, then match_id 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: Fixture

ColumnType
match_idint
home_teamvarchar
away_teamvarchar

Primary key: match_id.

One row per match of the league.

Table: OverSnapshot

ColumnType
match_idint
inningsint
over_noint
viewersbigint

Primary key: match_id, innings, over_no.

Concurrent viewers sampled at the end of over over_no of an innings.

Examples

Example 1

Fixture

match_idhome_teamaway_team
1Mumbai MarinersChennai Chargers
2Delhi DynamosKolkata Knights
3Bengaluru BlastersHyderabad Hawks
4Punjab PanthersJaipur Jaguars

OverSnapshot

match_idinningsover_noviewers
111018500000
121824300000
122023900000
212012000000
221919999999
31615000000
322020000000

Output

match_idhome_teamaway_teampeak_viewers
1Mumbai MarinersChennai Chargers24300000
3Bengaluru BlastersHyderabad Hawks20000000

How to solve Hotstar: Matches That Crossed Two Crore Concurrent Viewers

Each match has many samples and the question is about the largest one, so group the snapshots by match and take MAX(viewers). The threshold is a condition on that aggregate, which is what HAVING is for: WHERE viewers >= 20000000 would instead keep individual samples, and while it happens to select the same matches here, it would break the moment you wanted another aggregate of the match (say, its average) in the same row.

The boundary is inclusive — a match peaking at exactly two crore is listed, one at 19,999,999 is not. Writing the threshold as 20000000 rather than 2e7 keeps the comparison in integers.

The inner join drops fixtures with no samples. The alternative computes each match's peak in a derived table first and filters it with WHERE. The order is by peak descending, ties broken by match_id. Both read the snapshots once.

Reference solution (MySQL)

SELECT f.match_id, f.home_team, f.away_team, MAX(s.viewers) AS peak_viewers
FROM Fixture f
JOIN OverSnapshot s ON s.match_id = f.match_id
GROUP BY f.match_id, f.home_team, f.away_team
HAVING MAX(s.viewers) >= 20000000
ORDER BY peak_viewers DESC, f.match_id

Another way

SELECT f.match_id, f.home_team, f.away_team, p.peak_viewers
FROM Fixture f
JOIN (SELECT match_id, MAX(viewers) AS peak_viewers FROM OverSnapshot GROUP BY match_id) p ON p.match_id = f.match_id
WHERE p.peak_viewers >= 20000000
ORDER BY p.peak_viewers DESC, f.match_id

← Hotstar: Watchlist Titles the Profile Never Played · Hotstar: Playback Errors by App Platform →