ExpertWindow Functions50 points

VIP outreach: the top 10 percent of listeners

Customer care will phone the heaviest listeners to say thank you. Take every user who has at least one play and add up their seconds_played over the whole dataset. Rank those users with the percent rank of their total (a user with the smallest total gets 0, the largest gets 1; equal totals share a value). A VIP sits at or above the 90th percentile (percent rank of 0.9 or more).

Task: return user_id, name, total listening minutes as minutes (seconds divided by 60, ROUND(x, 2)) and percentile (percent rank times 100, ROUND(x, 2)) for every VIP. Sort by percentile 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?

PERCENT_RANK() OVER (ORDER BY total) works on the aggregated per-user table, so aggregate in a CTE first.