Hotstar: Watchlist Titles the Profile Never Played

Easy
703.

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

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per movie, series or show in the catalogue.

Table: Watchlist

ColumnType
profile_idint
title_idint
added_ondate

Primary key (together): profile_id, title_id.

One row per title a profile saved to its watchlist.

Table: Play

ColumnType
play_idint
profile_idint
title_idint
played_atdatetime

Primary key: play_id.

One row per time a profile pressed play on a title.

Example

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

Title

title_idtitle_name
1Midnight at Marine Drive
2The Spin Doctors
3Monsoon Mehfil
4Desert Rally

Watchlist

profile_idtitle_idadded_on
20112025-01-05
20122025-01-06
20222025-01-07
20232025-01-07
20342025-01-09

Play

play_idprofile_idtitle_idplayed_at
120112025-01-08 21:10:00
220222025-01-03 20:00:00
320132025-01-10 22:30:00
420312025-01-11 19:45:00

Output

profile_idtitle_name
201The Spin Doctors
202Monsoon Mehfil
203Desert Rally
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: 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

ColumnType
title_idint
title_namevarchar

Primary key: title_id.

One row per movie, series or show in the catalogue.

Table: Watchlist

ColumnType
profile_idint
title_idint
added_ondate

Primary key: profile_id, title_id.

One row per title a profile saved to its watchlist.

Table: Play

ColumnType
play_idint
profile_idint
title_idint
played_atdatetime

Primary key: play_id.

One row per time a profile pressed play on a title.

Examples

Example 1

Title

title_idtitle_name
1Midnight at Marine Drive
2The Spin Doctors
3Monsoon Mehfil
4Desert Rally

Watchlist

profile_idtitle_idadded_on
20112025-01-05
20122025-01-06
20222025-01-07
20232025-01-07
20342025-01-09

Play

play_idprofile_idtitle_idplayed_at
120112025-01-08 21:10:00
220222025-01-03 20:00:00
320132025-01-10 22:30:00
420312025-01-11 19:45:00

Output

profile_idtitle_name
201The Spin Doctors
202Monsoon Mehfil
203Desert 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 →