Daily Top Songs
ASKED IN INTERVIEWSpotify
Problem Statement
Spotify charts measure the longevity of hits. A "persistent hit" is defined as a song that remains in the Top 10 (rank 1 through 10) for more than 5 consecutive calendar days.
Write an SQL query to find the song_id of these persistent hits.
Rules:
- Only consider days where the song ranked between 1 and 10.
- "Consecutive" means there are no gaps in the calendar dates for that song's Top 10 streak.
- Output columns: song_id.
- Each song_id should appear only once in the output.
- Results must be sorted by song_id in ascending order.
Tests your understanding of
Window Functions, Gaps and Islands and Date Manipulation
Input Tables
Daily_Rankings
| song_id(INTEGER) | rank(INTEGER) | chart_date(DATE) |
|---|---|---|
| 1 | 1 | 2026-01-01 |
| 1 | 2 | 2026-01-02 |
| 1 | 1 | 2026-01-03 |
| 1 | 3 | 2026-01-04 |
| 1 | 5 | 2026-01-05 |
| 1 | 2 | 2026-01-06 |
| 2 | 1 | 2026-01-01 |
| 2 | 12 | 2026-01-02 |
Expected Output
| song_id(INTEGER) |
|---|
| 1 |
Tags
HardASKED IN INTERVIEWWindow FunctionsGaps and IslandsDate Manipulation
25-30 min
48%