Engagement per Subscriber, by Market
The raw market totals made StreamVerse's biggest market look best, which is roughly what a raw total always does. Finance wants the intensity number instead: for each market, the minutes watched divided by the number of separate people from that market who actually watched something. People who never pressed play must not dilute the figure.
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_per_sub, one row per market with any watch time. minutes_per_sub is that market's total watch_minutes divided by how many different subscribers from that market appear in the session log, rounded to 2 decimal places. Highest first; two markets on the same figure come back in alphabetical order of country.
Example output
Shape only — these markets and figures are invented:
| country | minutes_per_sub |
|---|---|
| BR | 214.75 |
| JP | 180 |
| DE | 96.5 |
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