Session Length by Plan
The premium-watches-more claim at StreamVerse survived the totals check, so analytics is trying to break it a second way: maybe higher tiers do not watch more often, they just watch for longer at a time. That is a question about the typical length of a sitting, not about how many sittings there were.
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, plan and avg_session, one row per plan with any watch time, avg_session being the mean length in minutes of a single session by subscribers on that plan, rounded to 2 decimal places. Longest typical sitting first; two plans on the same figure come back in alphabetical order of plan.
Example output
Shape only — the plan names are the real ones, the figures are invented. A round mean comes back plain, as 58 rather than 58.00:
| plan | avg_session |
|---|---|
| premium | 72.4 |
| standard | 58 |
| basic | 41.25 |
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