Minutes by Market
Regional teams at StreamVerse are graded on engagement in their own market, so finance needs watch time pooled by the subscriber's country. The session log has no country column — the market lives on the person, not the sitting.
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 |
subscribers — one row per person who pays for StreamVerse. plan is one of basic, standard or premium; country is a two-letter market code; signup_date is the day they signed up.
| 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 |
Both tables already exist in the database — there is nothing to create or load.
Task: Write a query that returns two columns, country and minutes, one row per market that has any watch time, minutes being the total watch_minutes from that market's subscribers. Most minutes first; two markets on the same total come back in alphabetical order of country.
Example output
Shape only — these markets and figures are invented:
| country | minutes |
|---|---|
| BR | 3200 |
| DE | 1450 |
| JP | 980 |
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