Listeners With a Song on Repeat — SQL Dates Problem
The "On Repeat" playlist is seeded from songs a listener played at least three times on the same calendar day.
- Difficulty: Medium
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #486
Problem statement
The "On Repeat" playlist is seeded from songs a listener played at least three times on the same calendar day. Plays at 23:59 and 00:01 fall on different days.
Return the columns listener_id, track_id, play_date (the day, as a date) and plays (that listener's plays of that track on that day) for every such listener, track and day. Return the rows in any order.
Tables
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| listener_id | int |
| track_id | int |
| played_at | datetime |
Primary key: play_id.
One row per play; times are IST.
Examples
Example 1
Play
| play_id | listener_id | track_id | played_at |
|---|---|---|---|
| 1 | 21 | 501 | 2024-08-03 08:10:00 |
| 2 | 21 | 501 | 2024-08-03 13:45:00 |
| 3 | 21 | 501 | 2024-08-03 23:59:00 |
| 4 | 22 | 502 | 2024-08-03 22:00:00 |
| 5 | 22 | 502 | 2024-08-03 23:30:00 |
| 6 | 22 | 502 | 2024-08-04 00:01:00 |
| 7 | 21 | 503 | 2024-08-03 09:00:00 |
| 8 | 23 | 501 | 2024-08-05 18:00:00 |
| 9 | 23 | 501 | 2024-08-05 18:04:00 |
| 10 | 23 | 501 | 2024-08-05 18:08:00 |
| 11 | 23 | 501 | 2024-08-05 18:12:00 |
Output
| listener_id | track_id | play_date | plays |
|---|---|---|---|
| 21 | 501 | 2024-08-03 | 3 |
| 23 | 501 | 2024-08-05 | 4 |
How to solve Listeners With a Song on Repeat
"Same calendar day" means the timestamp's date part: CAST(played_at AS DATE) (or DATE_FORMAT(played_at, '%Y-%m-%d'), or the first ten characters of the fixed-format text) turns 2024-08-03 23:59:00 into 2024-08-03. Grouping by (listener, track, day) puts each listener's plays of one track on one day into one group, and HAVING COUNT(*) >= 3 keeps the busy ones.
The midnight edge is where a rolling 24-hour window would disagree: listener 22's plays at 22:00, 23:30 and 00:01 are three plays within three hours, but they span two calendar days, so neither day reaches three. Grouping on the date handles it with no special case.
The window alternative counts the group size on every row with COUNT(*) OVER (PARTITION BY …) and deduplicates with DISTINCT; it is the same partition, written differently. Both are one pass with a sort or hash on the three keys.
Reference solution (MySQL)
SELECT listener_id, track_id, CAST(played_at AS DATE) AS play_date, COUNT(*) AS plays
FROM Play
GROUP BY listener_id, track_id, CAST(played_at AS DATE)
HAVING COUNT(*) >= 3Another way
SELECT listener_id, track_id, play_date, COUNT(*) AS plays FROM (SELECT listener_id, track_id, DATE_FORMAT(played_at, '%Y-%m-%d') AS play_date FROM Play) p GROUP BY listener_id, track_id, play_date HAVING COUNT(*) > 2Another way
SELECT DISTINCT listener_id, track_id, d AS play_date, cnt AS plays FROM (SELECT listener_id, track_id, LEFT(played_at, 10) AS d, COUNT(*) OVER (PARTITION BY listener_id, track_id, LEFT(played_at, 10)) AS cnt FROM Play) x WHERE cnt >= 3← Classify Every Plan Change as Upgrade or Downgrade · Titles Licensed in Every Region on Launch Day →