Hotstar: Subscribers Who Only Ever Watch Live Sport
Hotstar's growth team is designing a cheaper sports-only pack and wants to size the audience that would never miss the rest of the catalogue: subscribers…
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Joins
- Dialect: MySQL
- Problem: #709
Problem statement
Hotstar's growth team is designing a cheaper sports-only pack and wants to size the audience that would never miss the rest of the catalogue: subscribers who have watched only live sport and nothing else.
Return user_id, plan and sport_plays for every subscriber with at least 3 plays, all of whose plays have content_type = live_sport. Subscribers with fewer than 3 plays, or any play of another type, are left out. 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: Subscriber
| Column | Type |
|---|---|
| user_id | int |
| plan | enum(mobile, super, premium) |
| joined_on | date |
Primary key: user_id.
One row per paying subscriber.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| user_id | int |
| content_type | enum(live_sport, sport_highlights, movie, serial, news) |
Primary key: play_id.
One row per play. user_id always names a subscriber.
Examples
Example 1
Subscriber
| user_id | plan | joined_on |
|---|---|---|
| 1 | mobile | 2025-03-01 |
| 2 | super | 2025-03-02 |
| 3 | premium | 2025-03-05 |
| 4 | mobile | 2025-03-07 |
Play
| play_id | user_id | content_type |
|---|---|---|
| 1 | 1 | live_sport |
| 2 | 1 | live_sport |
| 3 | 1 | live_sport |
| 4 | 2 | live_sport |
| 5 | 2 | live_sport |
| 6 | 2 | live_sport |
| 7 | 2 | sport_highlights |
| 8 | 3 | live_sport |
| 9 | 3 | live_sport |
| 10 | 1 | live_sport |
Output
| user_id | plan | sport_plays |
|---|---|---|
| 1 | mobile | 4 |
How to solve Hotstar: Subscribers Who Only Ever Watch Live Sport
"Only live sport" is a statement about all of a user's plays, so it cannot be checked row by row in WHERE — filtering to live_sport first would hide the movie that disqualifies someone and wrongly keep them. Group each subscriber's plays and test the group: SUM(CASE WHEN content_type <> 'live_sport' THEN 1 ELSE 0 END) = 0 says no play of another type exists, and COUNT(*) >= 3 enforces the minimum. Note that sport highlights are not live sport, which is why user 2 is out.
Because every play in a qualifying group is live sport, COUNT(*) is also the number of sport plays to report. Subscribers with no plays never join and are not listed — they would fail the minimum anyway.
The first alternative states the rule as an anti join — NOT EXISTS a non-sport play — plus a correlated count for the minimum. The second uses MIN(content_type = 'live_sport') = 1: the boolean is 1 for sport and 0 otherwise, so its minimum is 1 only when every play is sport. All three read the plays once per subscriber.
Reference solution (MySQL)
SELECT s.user_id, s.plan, COUNT(*) AS sport_plays
FROM Subscriber s
JOIN Play p ON p.user_id = s.user_id
GROUP BY s.user_id, s.plan
HAVING COUNT(*) >= 3
AND SUM(CASE WHEN p.content_type <> 'live_sport' THEN 1 ELSE 0 END) = 0Another way
SELECT s.user_id, s.plan,
(SELECT COUNT(*) FROM Play p WHERE p.user_id = s.user_id) AS sport_plays
FROM Subscriber s
WHERE NOT EXISTS (SELECT 1 FROM Play p WHERE p.user_id = s.user_id AND p.content_type <> 'live_sport')
AND (SELECT COUNT(*) FROM Play p WHERE p.user_id = s.user_id) >= 3Another way
SELECT s.user_id, s.plan, COUNT(*) AS sport_plays
FROM Subscriber s JOIN Play p ON p.user_id = s.user_id
GROUP BY s.user_id, s.plan
HAVING COUNT(*) >= 3 AND MIN(p.content_type = 'live_sport') = 1← Hotstar: Biggest Over-on-Over Jump in Viewers · Hotstar: Worst Rebuffering CDN and Device in Each City →