Listeners With a Song on Repeat

Medium
486.

Listeners With a Song on Repeat

MediumDatesAggregation

MySQL

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

ColumnType
play_idint
listener_idint
track_idint
played_atdatetime

Primary key: play_id.

One row per play; times are IST.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Play

play_idlistener_idtrack_idplayed_at
1215012024-08-03 08:10:00
2215012024-08-03 13:45:00
3215012024-08-03 23:59:00
4225022024-08-03 22:00:00
5225022024-08-03 23:30:00
6225022024-08-04 00:01:00
7215032024-08-03 09:00:00
8235012024-08-05 18:00:00
9235012024-08-05 18:04:00
10235012024-08-05 18:08:00
1 more row

Output

listener_idtrack_idplay_dateplays
215012024-08-033
235012024-08-054
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.

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

ColumnType
play_idint
listener_idint
track_idint
played_atdatetime

Primary key: play_id.

One row per play; times are IST.

Examples

Example 1

Play

play_idlistener_idtrack_idplayed_at
1215012024-08-03 08:10:00
2215012024-08-03 13:45:00
3215012024-08-03 23:59:00
4225022024-08-03 22:00:00
5225022024-08-03 23:30:00
6225022024-08-04 00:01:00
7215032024-08-03 09:00:00
8235012024-08-05 18:00:00
9235012024-08-05 18:04:00
10235012024-08-05 18:08:00
11235012024-08-05 18:12:00

Output

listener_idtrack_idplay_dateplays
215012024-08-033
235012024-08-054

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(*) >= 3

Another 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(*) > 2

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