Longer Than Average
Recommendations at StreamVerse are trained on the sittings that went well, and the working definition of "went well" is a session longer than a typical one on the platform. The threshold is not a number anybody wrote down — it is whatever the mean session length happens to be right now, and it moves as the log grows.
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 three columns — id, show_id and watch_minutes — for every session strictly longer than the mean length of all sessions in the log. Longest first; two sessions of the same length come back with the smaller id first.
Example output
Shape only — these sessions are invented:
| id | show_id | watch_minutes |
|---|---|---|
| 88 | 3 | 205 |
| 41 | 7 | 180 |
| 63 | 3 | 155 |
| 17 | 9 | 140 |
| 52 | 7 | 120 |
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