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
| Column | Type |
|---|---|
| listener_id | int |
| display_name | varchar |
| tier | enum(free, premium) |
Primary key: listener_id.
One row per listener and their current tier.
Table: SkipEvent
| Column | Type |
|---|---|
| skip_id | int |
| listener_id | int |
| skipped_at | datetime |
Primary key: skip_id.
One row per press of the skip button.
Examples
Example 1
Listener
| listener_id | display_name | tier |
|---|---|---|
| 1 | Aarav | free |
| 2 | Diya | free |
| 3 | Kabir | premium |
| 4 | Meera | free |
SkipEvent
| skip_id | listener_id | skipped_at |
|---|---|---|
| 1 | 1 | 2024-10-05 21:02:10 |
| 2 | 1 | 2024-10-05 21:05:44 |
| 3 | 1 | 2024-10-05 21:10:03 |
| 4 | 1 | 2024-10-05 21:31:59 |
| 5 | 1 | 2024-10-05 21:47:20 |
| 6 | 1 | 2024-10-05 21:59:59 |
| 7 | 2 | 2024-10-05 09:50:00 |
| 8 | 2 | 2024-10-05 09:52:00 |
| 9 | 2 | 2024-10-05 09:55:00 |
| 10 | 2 | 2024-10-05 10:01:00 |
| 11 | 2 | 2024-10-05 10:03:00 |
| 12 | 2 | 2024-10-05 10:04:00 |
| 13 | 3 | 2024-10-06 18:00:01 |
| 14 | 3 | 2024-10-06 18:01:01 |
| 15 | 3 | 2024-10-06 18:02:01 |
| 16 | 3 | 2024-10-06 18:03:01 |
| 17 | 3 | 2024-10-06 18:04:01 |
| 18 | 3 | 2024-10-06 18:05:01 |
| 19 | 4 | 2024-10-06 08:15:00 |
Output
| listener_id | display_name | capped_hours | peak_skips |
|---|---|---|---|
| 1 | Aarav | 1 | 6 |
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_nameAnother 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_nameAnother 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 →