Netflix-style Company ChallengeMediumVerified answerSQLite live

Subscribed but Never Watched

Return user_id, country, and plan for subscribers who have no entries in watch_history, ordered by user_id.

  • Joins
  • Subqueries
  • Filtering
  • Sorting

Challenge brief

Understand the request

Retention — Churn Prevention Retention is running a re-engagement campaign targeting subscribers who signed up but never watched anything — a strong churn signal.

Return user_id, country, plan for subscribers without watch history.

Return

  • user_id
  • country
  • plan

Constraints

  • Return subscribers with no matching identified watch records
  • Remain correct when watch history contains NULL user IDs
  • Order by user ID

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

users

  • user_idINTEGER
  • countryVARCHAR(50)

subscriptions

  • user_idINTEGER
  • planVARCHAR(20)

watch_history

  • user_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Start with subscribers and test whether any watch row matches each user.

Hint 2

Use an anti-existence condition that remains correct in the presence of NULL activity keys.

Hint 3

Return subscriber details in user order.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT u.user_id, u.country, s.plan FROM users u INNER JOIN subscriptions s ON u.user_id = s.user_id WHERE NOT EXISTS (SELECT 1 FROM watch_history w WHERE w.user_id = u.user_id) ORDER BY u.user_id

Why this works

The anti-existence check tests each subscriber directly and is unaffected by NULL user identifiers in unrelated watch rows.

Success check

Returns the complete deterministic result for subscribed but never watched

Expected result

Use this output to verify values, aliases, ordering, and row count.

user_idcountryplan
9BRBasic
10FRStandard

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.