The Repeat Viewers
The StreamVerse retention model calls a subscriber "engaged" once they have started three or more watch sessions, and the weekly review wants that shortlist with the session numbers beside it, so the borderline cases are visible.
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 two columns, subscriber_id and sessions, listing only subscribers with three or more sessions, sessions being how many sessions they started. Busiest first; two subscribers on the same number of sessions come back with the smaller subscriber_id first.
Example output
If one subscriber cleared the bar with nine sittings, the answer would be a one-row table like this — invented id and figure:
| subscriber_id | sessions |
|---|---|
| 47 | 9 |
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