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
| Column | Type |
|---|---|
| match_id | int |
| home_team | varchar |
| away_team | varchar |
Primary key: match_id.
One row per match of the league.
Table: OverSnapshot
| Column | Type |
|---|---|
| match_id | int |
| innings | int |
| over_no | int |
| viewers | bigint |
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_id | home_team | away_team |
|---|---|---|
| 1 | Mumbai Mariners | Chennai Chargers |
| 2 | Delhi Dynamos | Kolkata Knights |
| 3 | Bengaluru Blasters | Hyderabad Hawks |
| 4 | Punjab Panthers | Jaipur Jaguars |
OverSnapshot
| match_id | innings | over_no | viewers |
|---|---|---|---|
| 1 | 1 | 10 | 18500000 |
| 1 | 2 | 18 | 24300000 |
| 1 | 2 | 20 | 23900000 |
| 2 | 1 | 20 | 12000000 |
| 2 | 2 | 19 | 19999999 |
| 3 | 1 | 6 | 15000000 |
| 3 | 2 | 20 | 20000000 |
Output
| match_id | home_team | away_team | peak_viewers |
|---|---|---|---|
| 1 | Mumbai Mariners | Chennai Chargers | 24300000 |
| 3 | Bengaluru Blasters | Hyderabad Hawks | 20000000 |
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_idAnother 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 →