You are given daily listen counts per artist. Calculate the 7-day rolling average of daily listens for each artist. The window covers each day plus the 6 days before it. Return artist_id, name, listen_date, daily_listens, and the rolling average rounded to 2 decimal places, ordered by artist_id then listen_date.
Schema
listen_logs
log_idINTEGER
artist_idINTEGER
listen_dateDATE
daily_listensINTEGER
artists
artist_idINTEGER
nameVARCHAR
genreVARCHAR
Target output
Target output
| artist_id | name | listen_date | daily_listens | rolling_7_day_avg |
|---|
| 1 | Olivia Dean | 2024-01-01 | 1,200 | 1200.00 |
| 1 | Olivia Dean | 2024-01-02 | 1,350 | 1275.00 |
| 1 | Olivia Dean | 2024-01-03 | 980 | 1176.67 |
| 1 | Olivia Dean | 2024-01-04 | 1,100 | 1157.50 |
| 1 | Olivia Dean | 2024-01-05 | 1,420 | 1210.00 |
| 1 | Olivia Dean | 2024-01-06 | 1,600 | 1275.00 |
| 1 | Olivia Dean | 2024-01-07 | 1,250 | 1271.43 |
| 1 | Olivia Dean | 2024-01-08 | 1,380 | 1297.14 |
| 2 | The Midnight | 2024-01-01 | 400 | 400.00 |
| 2 | The Midnight | 2024-01-02 | 600 | 500.00 |
| 2 | The Midnight | 2024-01-03 | 500 | 500.00 |
What this question tests
Tests the 7-day rolling average with an explicit ROWS BETWEEN frame. It is the cleanest probe of whether you actually control window frames: the default frame quietly produces a running average from the start of time instead of a 7-day roll.
More SQL interview questions
