What's Our Runner-Up Show?
StreamVerse's licensing team is negotiating renewals and wants a fallback ready. They already know which title is watched most; what they need now is the one directly behind it, so that if the top show's rights lapse there is a prepared second choice rather than a scramble.
A show's total is not stored anywhere. Viewing is logged one session at a time, and a show accumulates its minutes across sessions belonging to different subscribers, so the totals have to be built before any of them can be compared. "Second" is likewise not a property of any show — it appears only once every total sits beside the others.
Worth knowing before you trust the result: two of the five shows finish on exactly the same total. That tie is not at the position being asked for, but it is in the data, and the same query pointed one place further down would be picking between them on nothing at all.
watch_events — one row per viewing session. show_id is the show watched. watch_minutes is how long the session lasted.
| 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 returning exactly one row with two columns, show_id and total_minutes — the show standing second when every show is arranged by total watch minutes, largest first, and that show's total. "Second" is positional: had two shows tied at the top, this would return the second of that pair rather than the next lower total.
Example output — shape only, on an invented show and total.
| show_id | total_minutes |
|---|---|
| 8 | 265 |
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