Hotstar: Watchlist Titles the Profile Never Played
Hotstar's personalisation team sends a nudge for titles a profile saved to its watchlist but has never started.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #703
Problem statement
Hotstar's personalisation team sends a nudge for titles a profile saved to its watchlist but has never started. A play of the same title by another profile on the account does not count — the question is per profile.
Return profile_id and title_name for every watchlist entry whose profile has no play of that title at any time (before or after adding it). 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: Title
| Column | Type |
|---|---|
| title_id | int |
| title_name | varchar |
Primary key: title_id.
One row per movie, series or show in the catalogue.
Table: Watchlist
| Column | Type |
|---|---|
| profile_id | int |
| title_id | int |
| added_on | date |
Primary key: profile_id, title_id.
One row per title a profile saved to its watchlist.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| profile_id | int |
| title_id | int |
| played_at | datetime |
Primary key: play_id.
One row per time a profile pressed play on a title.
Examples
Example 1
Title
| title_id | title_name |
|---|---|
| 1 | Midnight at Marine Drive |
| 2 | The Spin Doctors |
| 3 | Monsoon Mehfil |
| 4 | Desert Rally |
Watchlist
| profile_id | title_id | added_on |
|---|---|---|
| 201 | 1 | 2025-01-05 |
| 201 | 2 | 2025-01-06 |
| 202 | 2 | 2025-01-07 |
| 202 | 3 | 2025-01-07 |
| 203 | 4 | 2025-01-09 |
Play
| play_id | profile_id | title_id | played_at |
|---|---|---|---|
| 1 | 201 | 1 | 2025-01-08 21:10:00 |
| 2 | 202 | 2 | 2025-01-03 20:00:00 |
| 3 | 201 | 3 | 2025-01-10 22:30:00 |
| 4 | 203 | 1 | 2025-01-11 19:45:00 |
Output
| profile_id | title_name |
|---|---|
| 201 | The Spin Doctors |
| 202 | Monsoon Mehfil |
| 203 | Desert Rally |
How to solve Hotstar: Watchlist Titles the Profile Never Played
Each watchlist row asks one question: has this profile ever played this title? That is an anti join on a two-column key. NOT EXISTS with both p.profile_id = w.profile_id and p.title_id = w.title_id in the subquery answers it exactly; a play by another profile matches on the title only and therefore does not rule the entry out, which is the trap in the first example: profile 201 played title 3, but that does not clear profile 202's watchlist entry for it.
No date condition is needed: the statement says any play at any time counts, so a play before added_on still removes the entry. The Title join only supplies the name.
The alternative is the LEFT JOIN … IS NULL form of the same anti join; it would duplicate rows if it were not filtered to the NULL side, but rows with a match are discarded entirely, so each surviving watchlist entry appears once. With an index on Play(profile_id, title_id) either form is a lookup per watchlist row.
Reference solution (MySQL)
SELECT w.profile_id, t.title_name
FROM Watchlist w
JOIN Title t ON t.title_id = w.title_id
WHERE NOT EXISTS (
SELECT 1 FROM Play p
WHERE p.profile_id = w.profile_id AND p.title_id = w.title_id
)Another way
SELECT w.profile_id, t.title_name
FROM Watchlist w
JOIN Title t ON t.title_id = w.title_id
LEFT JOIN Play p ON p.profile_id = w.profile_id AND p.title_id = w.title_id
WHERE p.play_id IS NULL← Hotstar: Next-Day Views of Regional Serials · Hotstar: Matches That Crossed Two Crore Concurrent Viewers →