Spotify: Free Listeners Who Hit the Hourly Skip Cap

Medium
932.

Spotify: Free Listeners Who Hit the Hourly Skip Cap

On the free tier a listener gets a limited number of skips per hour. Product analytics wants to know who keeps running into the limit, as a signal for an upgrade offer. Count skips per clock hour — the same date and the same hour, so 10:00:00 to 10:59:59 — and call an hour capped when a listener made 6 or more skips in it.

Return every free listener with at least one capped hour, with the columns listener_id, display_name, capped_hours (how many clock hours were capped) and peak_skips (the most skips they made in any single clock hour). Premium listeners are never in the answer. Return the rows in any order.

A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.

Tables

Table: Listener

ColumnTypeValues
listener_idint
display_namevarchar
tierenumfree, premium

Primary key: listener_id.

One row per listener and their current tier.

Table: SkipEvent

ColumnType
skip_idint
listener_idint
skipped_atdatetime

Primary key: skip_id.

One row per press of the skip button.

Example

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

Listener

listener_iddisplay_nametier
1Aaravfree
2Diyafree
3Kabirpremium
4Meerafree

SkipEvent

skip_idlistener_idskipped_at
112024-10-05 21:02:10
212024-10-05 21:05:44
312024-10-05 21:10:03
412024-10-05 21:31:59
512024-10-05 21:47:20
612024-10-05 21:59:59
722024-10-05 09:50:00
822024-10-05 09:52:00
922024-10-05 09:55:00
1022024-10-05 10:01:00
9 more rows

Output

listener_iddisplay_namecapped_hourspeak_skips
1Aarav16
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.

Spotify: Free Listeners Who Hit the Hourly Skip Cap

On the free tier a listener gets a limited number of skips per hour.

  • Difficulty: Medium
  • Topics: Aggregation, Dates, Subqueries
  • Dialect: MySQL
  • Problem: #932

Problem statement

On the free tier a listener gets a limited number of skips per hour. Product analytics wants to know who keeps running into the limit, as a signal for an upgrade offer. Count skips per clock hour — the same date and the same hour, so 10:00:00 to 10:59:59 — and call an hour capped when a listener made 6 or more skips in it.

Return every free listener with at least one capped hour, with the columns listener_id, display_name, capped_hours (how many clock hours were capped) and peak_skips (the most skips they made in any single clock hour). Premium listeners are never in the answer. Return the rows in any order.

A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.

Tables

Table: Listener

ColumnType
listener_idint
display_namevarchar
tierenum(free, premium)

Primary key: listener_id.

One row per listener and their current tier.

Table: SkipEvent

ColumnType
skip_idint
listener_idint
skipped_atdatetime

Primary key: skip_id.

One row per press of the skip button.

Examples

Example 1

Listener

listener_iddisplay_nametier
1Aaravfree
2Diyafree
3Kabirpremium
4Meerafree

SkipEvent

skip_idlistener_idskipped_at
112024-10-05 21:02:10
212024-10-05 21:05:44
312024-10-05 21:10:03
412024-10-05 21:31:59
512024-10-05 21:47:20
612024-10-05 21:59:59
722024-10-05 09:50:00
822024-10-05 09:52:00
922024-10-05 09:55:00
1022024-10-05 10:01:00
1122024-10-05 10:03:00
1222024-10-05 10:04:00
1332024-10-06 18:00:01
1432024-10-06 18:01:01
1532024-10-06 18:02:01
1632024-10-06 18:03:01
1732024-10-06 18:04:01
1832024-10-06 18:05:01
1942024-10-06 08:15:00

Output

listener_iddisplay_namecapped_hourspeak_skips
1Aarav16

How to solve Spotify: Free Listeners Who Hit the Hourly Skip Cap

The cap is measured per clock hour, so the first step is a bucket per listener per hour. Since timestamps are stored as YYYY-MM-DD HH:MM:SS, the first 13 characters (LEFT(skipped_at, 13)) name the date and the hour, and grouping by that with COUNT(*) gives skips per bucket. DATE() plus HOUR() or DATE_FORMAT(…, '%Y-%m-%d %H') build the same key.

The second step keeps the capped buckets (skips >= 6, so exactly six counts) and groups again per listener: COUNT(*) is the number of capped hours and MAX(skips) the peak. Joining Listener and filtering tier = 'free' removes premium users — in the example Kabir skips six times in an hour, but he is premium. Diya's burst of six skips straddles 10:00, three on each side, so she never hits the cap.

The peak over capped hours equals the peak over all hours, because a listener's busiest hour is at least as busy as any capped one. The last alternative shows that by computing both figures over every hour and filtering afterwards. All versions are two hash aggregations over the skip log.

Reference solution (MySQL)

WITH hourly AS (
  SELECT listener_id, LEFT(skipped_at, 13) AS clock_hour, COUNT(*) AS skips
  FROM SkipEvent
  GROUP BY listener_id, LEFT(skipped_at, 13)
)
SELECT l.listener_id, l.display_name, COUNT(*) AS capped_hours, MAX(h.skips) AS peak_skips
FROM Listener l
JOIN hourly h ON h.listener_id = l.listener_id
WHERE l.tier = 'free' AND h.skips >= 6
GROUP BY l.listener_id, l.display_name

Another way

SELECT l.listener_id, l.display_name, COUNT(*) AS capped_hours, MAX(h.skips) AS peak_skips
FROM Listener l
JOIN (SELECT listener_id, DATE(skipped_at) AS d, HOUR(skipped_at) AS hr, COUNT(*) AS skips
      FROM SkipEvent GROUP BY listener_id, DATE(skipped_at), HOUR(skipped_at)
      HAVING COUNT(*) >= 6) h ON h.listener_id = l.listener_id
WHERE l.tier IN ('free')
GROUP BY l.listener_id, l.display_name

Another way

SELECT l.listener_id, l.display_name, x.capped_hours, x.peak_skips
FROM Listener l
JOIN (SELECT listener_id, SUM(CASE WHEN skips >= 6 THEN 1 ELSE 0 END) AS capped_hours, MAX(skips) AS peak_skips
      FROM (SELECT listener_id, DATE_FORMAT(skipped_at, '%Y-%m-%d %H') AS hh, COUNT(*) AS skips FROM SkipEvent GROUP BY listener_id, DATE_FORMAT(skipped_at, '%Y-%m-%d %H')) t
      GROUP BY listener_id) x ON x.listener_id = l.listener_id
WHERE l.tier = 'free' AND x.capped_hours > 0

← Spotify: Discover Weekly Save Rate per Listener · Spotify: Wrapped Top Artist of Every Listener →