The content team wants to find episodes where weekly viewership dropped more than 15% below the 4-week rolling average for that series. Return series_id, episode_id, the week (as a date), the weekly viewer count, the rolling 4-week average, and the percentage change. Count each viewer once per week. Exclude the first 3 weeks of each series, where the rolling window is not yet complete. Round the rolling average to a whole number and the percentage to 2 decimal places, ordered by percentage change ascending.
Schema
series
series_idINTEGER
titleVARCHAR
genreVARCHAR
episodes
episode_idINTEGER
series_idINTEGER
episode_numberINTEGER
release_dateDATE
viewership
view_idINTEGER
series_idINTEGER
episode_idINTEGER
user_idINTEGER
watched_atTIMESTAMP
Target output
Target output
| series_id | episode_id | week | weekly_viewers | rolling_4_week_avg | pct_change |
|---|
| 1 | 105 | 2024-01-29 | 3 | 5 | -42.86 |
| 1 | 108 | 2024-02-19 | 3 | 5 | -40.00 |
What this question tests
Tests a rolling window average with an explicit frame plus a percentage comparison against it. Alerting on a drop below trend is a real production pattern at streaming scale, and the interview version checks that you can control a window frame instead of accepting the default.
More SQL interview questions
