Hotstar: Subscribers Who Only Ever Watch Live Sport

Medium
709.

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

ColumnTypeValues
user_idint
planenummobile, super, premium
joined_ondate

Primary key: user_id.

One row per paying subscriber.

Table: Play

ColumnTypeValues
play_idint
user_idint
content_typeenumlive_sport, sport_highlights, movie, serial, news

Primary key: play_id.

One row per play. user_id always names a subscriber.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Subscriber

user_idplanjoined_on
1mobile2025-03-01
2super2025-03-02
3premium2025-03-05
4mobile2025-03-07

Play

play_iduser_idcontent_type
11live_sport
21live_sport
31live_sport
42live_sport
52live_sport
62live_sport
72sport_highlights
83live_sport
93live_sport
101live_sport

Output

user_idplansport_plays
1mobile4
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: 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

ColumnType
user_idint
planenum(mobile, super, premium)
joined_ondate

Primary key: user_id.

One row per paying subscriber.

Table: Play

ColumnType
play_idint
user_idint
content_typeenum(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_idplanjoined_on
1mobile2025-03-01
2super2025-03-02
3premium2025-03-05
4mobile2025-03-07

Play

play_iduser_idcontent_type
11live_sport
21live_sport
31live_sport
42live_sport
52live_sport
62live_sport
72sport_highlights
83live_sport
93live_sport
101live_sport

Output

user_idplansport_plays
1mobile4

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) = 0

Another 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) >= 3

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