Daily Streams in an Album's First Week, Zero Days Included

Hard
497.

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. 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

ColumnType
album_idint
album_namevarchar
release_datedate

Primary key: album_id.

One row per album; album names are unique.

Table: Stream

ColumnType
stream_idint
album_idint
streamed_ondate

Primary key: stream_id.

One row per qualifying stream of a track from the album.

Example

The rows must come back in the order shown.

Album

album_idalbum_namerelease_date
1Monsoon Tapes2024-07-05
2City Lights2024-07-30

Stream

stream_idalbum_idstreamed_on
112024-07-05
212024-07-05
312024-07-06
412024-07-09
512024-07-11
612024-07-12
712024-07-04
822024-08-01
922024-08-05
1022024-07-31

Output

album_nameday_nostreamscumulative_streams
City Lights100
City Lights211
City Lights312
City Lights402
City Lights502
City Lights602
City Lights713
Monsoon Tapes122
Monsoon Tapes213
Monsoon Tapes303
4 more rows
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
album_idint
album_namevarchar
release_datedate

Primary key: album_id.

One row per album; album names are unique.

Table: Stream

ColumnType
stream_idint
album_idint
streamed_ondate

Primary key: stream_id.

One row per qualifying stream of a track from the album.

Examples

Example 1

Album

album_idalbum_namerelease_date
1Monsoon Tapes2024-07-05
2City Lights2024-07-30

Stream

stream_idalbum_idstreamed_on
112024-07-05
212024-07-05
312024-07-06
412024-07-09
512024-07-11
612024-07-12
712024-07-04
822024-08-01
922024-08-05
1022024-07-31

Output

album_nameday_nostreamscumulative_streams
City Lights100
City Lights211
City Lights312
City Lights402
City Lights502
City Lights602
City Lights713
Monsoon Tapes122
Monsoon Tapes213
Monsoon Tapes303
Monsoon Tapes403
Monsoon Tapes514
Monsoon Tapes604
Monsoon Tapes715

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_no

Another 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 →