Hotstar: Top Two Shows per Language by Unique Viewers
Each language home page on Hotstar leads with its most-watched shows.
- Difficulty: Medium
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #714
Problem statement
Each language home page on Hotstar leads with its most-watched shows. The editorial team measures a show by unique viewers (distinct profiles that played it), and wants the top two values per language — with ties, so a third show that ties the second also makes the row.
Return language, show_name and unique_viewers for every show whose unique-viewer count is among the two highest distinct counts of its language. Shows nobody played are never returned. 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: TvShow
| Column | Type |
|---|---|
| show_id | int |
| show_name | varchar |
| language | varchar |
Primary key: show_id.
One row per show in the catalogue, in its original language.
Table: ShowView
| Column | Type |
|---|---|
| view_id | int |
| show_id | int |
| profile_id | int |
Primary key: view_id.
One row per play of a show; a profile can play a show many times.
Examples
Example 1
TvShow
| show_id | show_name | language |
|---|---|---|
| 1 | Pyaar Ki Patang | Hindi |
| 2 | Sindoor Ki Saugandh | Hindi |
| 3 | Second Innings | Hindi |
| 4 | Kitchen Kings | Hindi |
| 5 | Neer Kolam | Tamil |
| 6 | Kadal Kaatru | Tamil |
ShowView
| view_id | show_id | profile_id |
|---|---|---|
| 1 | 1 | 801 |
| 2 | 1 | 802 |
| 3 | 1 | 803 |
| 4 | 1 | 801 |
| 5 | 2 | 801 |
| 6 | 2 | 804 |
| 7 | 3 | 805 |
| 8 | 3 | 806 |
| 9 | 4 | 807 |
| 10 | 5 | 808 |
| 11 | 5 | 808 |
Output
| language | show_name | unique_viewers |
|---|---|---|
| Hindi | Pyaar Ki Patang | 3 |
| Hindi | Sindoor Ki Saugandh | 2 |
| Hindi | Second Innings | 2 |
| Tamil | Neer Kolam | 1 |
How to solve Hotstar: Top Two Shows per Language by Unique Viewers
Two steps. First the measure: COUNT(DISTINCT profile_id) per show, so profile 801's two plays of the first show count once (and the Tamil show watched twice by one profile has one unique viewer). The inner join leaves out shows nobody played, as required.
Then the cut. The statement asks for the top two values, with every show holding them — that is DENSE_RANK(), which numbers distinct values 1, 2, 3 with no gaps, so all shows at the highest count get 1 and all shows at the second-highest get 2. RANK() would skip to 3 after a tie at the top and drop the second value; ROW_NUMBER() would cut ties arbitrarily. In the example, two Hindi shows tie at 2 viewers and both are returned beside the 3-viewer leader.
The alternative states the same rule as a correlated count: a show qualifies when fewer than two distinct higher counts exist in its language. It is quadratic in shows per language; the window version sorts once.
Reference solution (MySQL)
WITH reach AS (
SELECT s.language, s.show_name, COUNT(DISTINCT v.profile_id) AS unique_viewers
FROM TvShow s
JOIN ShowView v ON v.show_id = s.show_id
GROUP BY s.show_id, s.language, s.show_name
),
ranked AS (
SELECT language, show_name, unique_viewers,
DENSE_RANK() OVER (PARTITION BY language ORDER BY unique_viewers DESC) AS dr
FROM reach
)
SELECT language, show_name, unique_viewers
FROM ranked
WHERE dr <= 2Another way
WITH reach AS (
SELECT s.language, s.show_name, COUNT(DISTINCT v.profile_id) AS unique_viewers
FROM TvShow s JOIN ShowView v ON v.show_id = s.show_id
GROUP BY s.show_id, s.language, s.show_name
)
SELECT r.language, r.show_name, r.unique_viewers
FROM reach r
WHERE (SELECT COUNT(DISTINCT o.unique_viewers) FROM reach o
WHERE o.language = r.language AND o.unique_viewers > r.unique_viewers) < 2← Hotstar: Unsold Ad Slots by Innings Phase · Hotstar: Final-Day Paid Sign-Ups by Hour With a Running Total →