Daily Streams in an Album's First Week, Zero Days Included
Labels judge a new album by its first seven days: the release day is day 1, and day 7 is six days later.
- Difficulty: Hard
- Topics: Dates, Window Functions, Joins
- Dialect: MySQL
- Problem: #497
Problem statement
Labels judge a new album by its first seven days: the release day is day 1, and day 7 is six days later. The chart must show every one of those seven days for every album, with 0 on a day without streams. Streams before release (pre-saves played early) or after day 7 are ignored.
Return the columns album_name, day_no (1 to 7), streams (that day's streams) and cumulative_streams (streams from day 1 up to and including this day). Order by album_name, then day_no.
Tables
Table: Album
| Column | Type |
|---|---|
| album_id | int |
| album_name | varchar |
| release_date | date |
Primary key: album_id.
One row per album; album names are unique.
Table: Stream
| Column | Type |
|---|---|
| stream_id | int |
| album_id | int |
| streamed_on | date |
Primary key: stream_id.
One row per qualifying stream of a track from the album.
Examples
Example 1
Album
| album_id | album_name | release_date |
|---|---|---|
| 1 | Monsoon Tapes | 2024-07-05 |
| 2 | City Lights | 2024-07-30 |
Stream
| stream_id | album_id | streamed_on |
|---|---|---|
| 1 | 1 | 2024-07-05 |
| 2 | 1 | 2024-07-05 |
| 3 | 1 | 2024-07-06 |
| 4 | 1 | 2024-07-09 |
| 5 | 1 | 2024-07-11 |
| 6 | 1 | 2024-07-12 |
| 7 | 1 | 2024-07-04 |
| 8 | 2 | 2024-08-01 |
| 9 | 2 | 2024-08-05 |
| 10 | 2 | 2024-07-31 |
Output
| album_name | day_no | streams | cumulative_streams |
|---|---|---|---|
| City Lights | 1 | 0 | 0 |
| City Lights | 2 | 1 | 1 |
| City Lights | 3 | 1 | 2 |
| City Lights | 4 | 0 | 2 |
| City Lights | 5 | 0 | 2 |
| City Lights | 6 | 0 | 2 |
| City Lights | 7 | 1 | 3 |
| Monsoon Tapes | 1 | 2 | 2 |
| Monsoon Tapes | 2 | 1 | 3 |
| Monsoon Tapes | 3 | 0 | 3 |
| Monsoon Tapes | 4 | 0 | 3 |
| Monsoon Tapes | 5 | 1 | 4 |
| Monsoon Tapes | 6 | 0 | 4 |
| Monsoon Tapes | 7 | 1 | 5 |
How to solve Daily Streams in an Album's First Week, Zero Days Included
The difficulty is the zero days: an aggregate over Stream can only produce days that have streams. So generate the scaffold first — a recursive CTE yields the numbers 1 to 7 (it stops at day_no < 7, so it terminates after seven rows), and a CROSS JOIN with Album gives one slot per album per day.
Each slot's date is DATE_ADD(release_date, INTERVAL day_no - 1 DAY). LEFT JOIN the streams on album and that date, group by the slot, and COUNT(s.stream_id): it counts matched streams only, so a slot with none shows 0 rather than 1. Pre-release streams and those after day 7 never match a slot, so they are ignored without an extra filter.
The running total is a window over the finished daily rows: SUM(streams) OVER (PARTITION BY album ORDER BY day_no) with an explicit ROWS frame. The alternative builds the seven days with UNION ALL and computes both numbers with correlated COUNTs by day offset (DATEDIFF) — simpler to read, but it scans the streams twice per slot.
Reference solution (MySQL)
WITH RECURSIVE days AS (
SELECT 1 AS day_no
UNION ALL
SELECT day_no + 1 FROM days WHERE day_no < 7
), daily AS (
SELECT a.album_name, d.day_no, COUNT(s.stream_id) AS streams
FROM Album a
CROSS JOIN days d
LEFT JOIN Stream s
ON s.album_id = a.album_id
AND s.streamed_on = DATE_ADD(a.release_date, INTERVAL d.day_no - 1 DAY)
GROUP BY a.album_id, a.album_name, d.day_no
)
SELECT album_name, day_no, streams,
SUM(streams) OVER (PARTITION BY album_name ORDER BY day_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_streams
FROM daily
ORDER BY album_name, day_noAnother way
SELECT a.album_name, d.day_no,
(SELECT COUNT(*) FROM Stream s WHERE s.album_id = a.album_id AND DATEDIFF(s.streamed_on, a.release_date) = d.day_no - 1) AS streams,
(SELECT COUNT(*) FROM Stream s WHERE s.album_id = a.album_id AND DATEDIFF(s.streamed_on, a.release_date) BETWEEN 0 AND d.day_no - 1) AS cumulative_streams
FROM Album a
CROSS JOIN (SELECT 1 AS day_no UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7) d
ORDER BY a.album_name, d.day_no← Free-Trial Conversion and Second-Month Funnel · Prepaid Plans Expiring in the First Week of June →