ExpertAnalytics Patterns50 points

Longest listening streak (gaps and islands)

The app is adding a Streak badge, like language-learning apps do. A streak is a run of consecutive calendar days on which the user played at least one track (use the date part of played_at; several plays on one day count once). A missing day breaks the streak.

Task: for each user whose longest streak is 5 days or more, return user_id, name, the length of the longest streak as longest_streak, and the first day of that streak as streak_start (if a user has two streaks of the same longest length, use the earlier one). Sort by longest_streak descending, then user_id.

The data you can query

Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.

artists16 rows
artist_id INTEGERartist_name TEXThome_country TEXTgenre TEXT
1Tobi AdeyemiNigeriaAfrobeats
2Nala OseiGhanaAfrobeats
3Neon HanbokSouth KoreaK-Pop
4Pixel OrchidSouth KoreaK-Pop
5Luz de AlbaMexicoLatin Pop
6Rio CalienteBrazilLatin Pop

Showing the first 6 of 16 rows.

plans4 rows
plan_id INTEGERplan_name TEXTmonthly_price REAL
1Free0
2Student4.99
3Premium9.99
4Family14.99
plays365 rows
play_id INTEGERuser_id INTEGERtrack_id INTEGERplayed_at TEXTseconds_played INTEGERdevice TEXT
12152025-02-10 09:10:00174mobile
21172025-03-12 11:10:00253smart_speaker
313332025-03-25 14:26:23140desktop
413132025-03-25 14:51:34245smart_speaker
512132025-04-01 12:10:00272desktop
610132025-04-11 10:10:00272smart_tv

Showing the first 6 of 365 rows.

subscriptions51 rows
sub_id INTEGERuser_id INTEGERplan_id INTEGERstart_date TEXTend_date TEXTstatus TEXT
1132025-01-072025-09-03cancelled
2132025-11-302026-01-02cancelled
3332025-01-202025-08-23cancelled
4522025-01-28NULLactive
5642025-02-022025-03-27cancelled
6732025-02-062025-10-04cancelled

Showing the first 6 of 51 rows.

tracks48 rows
track_id INTEGERtitle TEXTartist_id INTEGERrelease_year INTEGERduration_sec INTEGER
1Midnight Lagos12019353
2Golden Highway12024156
3Paper Lanterns12022253
4Neon Rain22025249
5Velvet Satellite22026176
6Broken Harbor22020157

Showing the first 6 of 48 rows.

users60 rows
user_id INTEGERname TEXTcountry TEXTsignup_date TEXTplan_id INTEGERbirth_year INTEGER
1Aarav IyerIndia2025-01-0711998
2Diego SalazarMexico2025-01-1211982
3Lucas AlmeidaBrazil2025-01-1711976
4Oliver HartleyUnited Kingdom2025-01-2211978
5Priya BanerjeeUnited Kingdom2025-01-2721999
6Jordan WhitakerUnited States2025-02-0112006

Showing the first 6 of 60 rows.

Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.

Need a hint?

For consecutive days, (day number minus row number) stays constant inside a streak. Group by that value.