Who's Watching the Most?
StreamVerse's engagement team is picking people to invite into an early-access beta, and wants to start with the heaviest viewers. Before anyone can be ranked, there has to be one viewing figure per subscriber to rank them by — and that figure does not exist yet.
What the app records is sessions. Every time somebody watches something, a row is written with the minutes they spent, so a subscriber who watched three things owns three rows. The table has ten rows and five subscribers behind them.
watch_events — one row per viewing session. subscriber_id is who watched, show_id is what they watched, and watch_minutes is how long that single 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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query returning two columns, subscriber_id and total_minutes, with one row for every subscriber who has at least one session in the table. A subscriber who has never watched anything produces no row. Rows come back sorted by subscriber_id, smallest first.
Example output — shape only, on invented subscribers and totals.
| subscriber_id | total_minutes |
|---|---|
| 6 | 240 |
| 7 | 95 |
| 9 | 310 |
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