Filling the empty days: May 2026 daily plays
A dashboard for May 2026 shows gaps because days without any play simply do not exist in the plays table. The BI team needs a complete calendar, generated with a recursive CTE, from 2026-05-01 to 2026-05-31, with 0 for silent days. They also want a smoothed line: the trailing 7-day average, meaning the average of the current day and the 6 days before it. For the first six days of May use only the days of May that are available so far.
Task: return day (YYYY-MM-DD), plays (0 when none) and avg_7d (ROUND(x, 2)) for all 31 days, sorted by day.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| artist_id INTEGER | artist_name TEXT | home_country TEXT | genre TEXT |
|---|---|---|---|
| 1 | Tobi Adeyemi | Nigeria | Afrobeats |
| 2 | Nala Osei | Ghana | Afrobeats |
| 3 | Neon Hanbok | South Korea | K-Pop |
| 4 | Pixel Orchid | South Korea | K-Pop |
| 5 | Luz de Alba | Mexico | Latin Pop |
| 6 | Rio Caliente | Brazil | Latin Pop |
Showing the first 6 of 16 rows.
| plan_id INTEGER | plan_name TEXT | monthly_price REAL |
|---|---|---|
| 1 | Free | 0 |
| 2 | Student | 4.99 |
| 3 | Premium | 9.99 |
| 4 | Family | 14.99 |
| play_id INTEGER | user_id INTEGER | track_id INTEGER | played_at TEXT | seconds_played INTEGER | device TEXT |
|---|---|---|---|---|---|
| 1 | 2 | 15 | 2025-02-10 09:10:00 | 174 | mobile |
| 2 | 11 | 7 | 2025-03-12 11:10:00 | 253 | smart_speaker |
| 3 | 13 | 33 | 2025-03-25 14:26:23 | 140 | desktop |
| 4 | 13 | 13 | 2025-03-25 14:51:34 | 245 | smart_speaker |
| 5 | 12 | 13 | 2025-04-01 12:10:00 | 272 | desktop |
| 6 | 10 | 13 | 2025-04-11 10:10:00 | 272 | smart_tv |
Showing the first 6 of 365 rows.
| sub_id INTEGER | user_id INTEGER | plan_id INTEGER | start_date TEXT | end_date TEXT | status TEXT |
|---|---|---|---|---|---|
| 1 | 1 | 3 | 2025-01-07 | 2025-09-03 | cancelled |
| 2 | 1 | 3 | 2025-11-30 | 2026-01-02 | cancelled |
| 3 | 3 | 3 | 2025-01-20 | 2025-08-23 | cancelled |
| 4 | 5 | 2 | 2025-01-28 | NULL | active |
| 5 | 6 | 4 | 2025-02-02 | 2025-03-27 | cancelled |
| 6 | 7 | 3 | 2025-02-06 | 2025-10-04 | cancelled |
Showing the first 6 of 51 rows.
| track_id INTEGER | title TEXT | artist_id INTEGER | release_year INTEGER | duration_sec INTEGER |
|---|---|---|---|---|
| 1 | Midnight Lagos | 1 | 2019 | 353 |
| 2 | Golden Highway | 1 | 2024 | 156 |
| 3 | Paper Lanterns | 1 | 2022 | 253 |
| 4 | Neon Rain | 2 | 2025 | 249 |
| 5 | Velvet Satellite | 2 | 2026 | 176 |
| 6 | Broken Harbor | 2 | 2020 | 157 |
Showing the first 6 of 48 rows.
| user_id INTEGER | name TEXT | country TEXT | signup_date TEXT | plan_id INTEGER | birth_year INTEGER |
|---|---|---|---|---|---|
| 1 | Aarav Iyer | India | 2025-01-07 | 1 | 1998 |
| 2 | Diego Salazar | Mexico | 2025-01-12 | 1 | 1982 |
| 3 | Lucas Almeida | Brazil | 2025-01-17 | 1 | 1976 |
| 4 | Oliver Hartley | United Kingdom | 2025-01-22 | 1 | 1978 |
| 5 | Priya Banerjee | United Kingdom | 2025-01-27 | 2 | 1999 |
| 6 | Jordan Whitaker | United States | 2025-02-01 | 1 | 2006 |
Showing the first 6 of 60 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
Recursive part: SELECT date(day, '+1 day') FROM days WHERE day < '2026-05-31'. Then LEFT JOIN the daily counts and use a ROWS frame.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.