Who's Barely Watching Anything?
Retention at StreamVerse has learned to watch for a particular pattern: somebody signs up, watches one thing, and is never seen again. They almost always cancel at the end of the billing period, and a nudge before that — a recommendation email, a reminder of what is new — recovers a useful fraction of them.
Finding those people means counting, and the viewing log does not count anything. It records one row per viewing session, so a subscriber with a single session looks exactly like any other row until the rows belonging to each person are gathered up. The email tool wants names, and names are on the subscriber record rather than in the log.
subscribers — one row per subscriber. plan is what they pay for.
| id | name | plan | signup_date | country |
|---|---|---|---|---|
| 1 | Ana Torres | premium | 2022-11-01 | US |
| 2 | Ben Osei | basic | 2023-01-15 | UK |
| 3 | Chloe Martin | standard | 2023-02-20 | US |
| 4 | Dev Malhotra | premium | 2023-03-10 | IN |
| 5 | Ella Novak | basic | 2023-04-05 | UK |
watch_events — one row per viewing session. subscriber_id points at a row in subscribers; watch_minutes is how long that session lasted.
| id | subscriber_id | show_id | watch_minutes | watch_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 45 | 2023-05-01 |
| 2 | 1 | 5 | 60 | 2023-05-03 |
| 3 | 2 | 3 | 20 | 2023-05-02 |
| 4 | 3 | 2 | 55 | 2023-05-05 |
| 5 | 4 | 1 | 40 | 2023-05-06 |
| 6 | 4 | 4 | 70 | 2023-05-08 |
| 7 | 1 | 3 | 25 | 2023-05-10 |
| 8 | 3 | 5 | 50 | 2023-05-11 |
| 9 | 2 | 2 | 30 | 2023-05-12 |
| 10 | 5 | 4 | 65 | 2023-05-14 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query returning a single column, name, holding every subscriber with exactly one viewing session on record. Two or more sessions does not qualify however short they were, and somebody with no sessions at all does not qualify either — this list is for people who started and stopped, not for people who never started.
Example output — shape only, on an invented subscriber.
| name |
|---|
| Mateo Ferrand |
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in