The One-Show Wonders
A subscriber who has only ever watched one title at StreamVerse has nothing to come back for once it ends, and retention loses them the week the finale airs. The team wants those accounts flagged so the recommendation engine can push a second title at them. Watching the same show three times still counts as one title.
watch_events — one row per watch session: one sitting in which one subscriber watched one show. subscriber_id points at a row in subscribers, show_id at a row in shows, and watch_minutes is how long that sitting lasted. The same person can appear many times.
| 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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns a single column, subscriber_id, listing every subscriber whose sessions all landed on the same one show, sorted by subscriber_id from smallest to largest. Subscribers who have watched two or more different shows must not appear, and neither must anyone with no sessions at all.
Example output
If exactly one subscriber had stayed on a single show, the answer would be a one-row table like this — the id is invented:
| subscriber_id |
|---|
| 47 |
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