Which Country Watches the Most?
StreamVerse has one licensing budget left this year and the content team has to point it at a single country. Their measure is attention: the country whose subscribers have spent the most minutes actually watching, not the country with the most sign-ups.
Those are different questions, and the data is arranged so you cannot answer the first by looking at one table. Minutes are logged on watch events, one row per viewing session, and a watch event knows only which subscriber it belongs to. The country is a fact about the subscriber. So a viewing session has to be traced back to the person before it can be credited to a place.
subscribers — one row per subscriber. country is a two-letter country code.
| 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 exactly one row with two columns, country and total_minutes — the single subscriber country with the largest total watch minutes, and that total. Every logged session counts, however short. If two countries were tied at the top only one row would still come back, and which of them it is would be unspecified.
Example output — shape only, on an invented country and total.
| country | total_minutes |
|---|---|
| BR | 640 |
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