The Daily Engagement Curve
StreamVerse shipped a new app build at the start of May and wants the daily engagement curve for the opening stretch of the month: how many watch sessions started on each day. Days on which nobody watched anything leave no trace in the log, so the curve is over the days that appear, not over every calendar date. Product only wants the first seven such days.
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 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns two columns, watch_date and sessions, one row per day on which at least one session started, sessions being how many sessions started that day. Return the seven earliest days only, in date order from earliest to latest.
Example output
Shape only — these days and figures are invented:
| watch_date | sessions |
|---|---|
| 2022-02-01 | 3 |
| 2022-02-03 | 1 |
| 2022-02-04 | 5 |
| 2022-02-06 | 2 |
| 2022-02-07 | 4 |
| 2022-02-09 | 1 |
| 2022-02-11 | 2 |
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