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.
| artist_id INTEGER | artist_name TEXT | home_country TEXT | genre TEXT |
|---|---|---|---|
| 1 | Tobi Adeyemi | Nigeria | Afrobeats |
| 2 | Nala Osei | Ghana | Afrobeats |
| 3 | Neon Hanbok | South Korea | K-Pop |
| 4 | Pixel Orchid | South Korea | K-Pop |
| 5 | Luz de Alba | Mexico | Latin Pop |
| 6 | Rio Caliente | Brazil | Latin Pop |
Showing the first 6 of 16 rows.
| plan_id INTEGER | plan_name TEXT | monthly_price REAL |
|---|---|---|
| 1 | Free | 0 |
| 2 | Student | 4.99 |
| 3 | Premium | 9.99 |
| 4 | Family | 14.99 |
| play_id INTEGER | user_id INTEGER | track_id INTEGER | played_at TEXT | seconds_played INTEGER | device TEXT |
|---|---|---|---|---|---|
| 1 | 2 | 15 | 2025-02-10 09:10:00 | 174 | mobile |
| 2 | 11 | 7 | 2025-03-12 11:10:00 | 253 | smart_speaker |
| 3 | 13 | 33 | 2025-03-25 14:26:23 | 140 | desktop |
| 4 | 13 | 13 | 2025-03-25 14:51:34 | 245 | smart_speaker |
| 5 | 12 | 13 | 2025-04-01 12:10:00 | 272 | desktop |
| 6 | 10 | 13 | 2025-04-11 10:10:00 | 272 | smart_tv |
Showing the first 6 of 365 rows.
| sub_id INTEGER | user_id INTEGER | plan_id INTEGER | start_date TEXT | end_date TEXT | status TEXT |
|---|---|---|---|---|---|
| 1 | 1 | 3 | 2025-01-07 | 2025-09-03 | cancelled |
| 2 | 1 | 3 | 2025-11-30 | 2026-01-02 | cancelled |
| 3 | 3 | 3 | 2025-01-20 | 2025-08-23 | cancelled |
| 4 | 5 | 2 | 2025-01-28 | NULL | active |
| 5 | 6 | 4 | 2025-02-02 | 2025-03-27 | cancelled |
| 6 | 7 | 3 | 2025-02-06 | 2025-10-04 | cancelled |
Showing the first 6 of 51 rows.
| track_id INTEGER | title TEXT | artist_id INTEGER | release_year INTEGER | duration_sec INTEGER |
|---|---|---|---|---|
| 1 | Midnight Lagos | 1 | 2019 | 353 |
| 2 | Golden Highway | 1 | 2024 | 156 |
| 3 | Paper Lanterns | 1 | 2022 | 253 |
| 4 | Neon Rain | 2 | 2025 | 249 |
| 5 | Velvet Satellite | 2 | 2026 | 176 |
| 6 | Broken Harbor | 2 | 2020 | 157 |
Showing the first 6 of 48 rows.
| user_id INTEGER | name TEXT | country TEXT | signup_date TEXT | plan_id INTEGER | birth_year INTEGER |
|---|---|---|---|---|---|
| 1 | Aarav Iyer | India | 2025-01-07 | 1 | 1998 |
| 2 | Diego Salazar | Mexico | 2025-01-12 | 1 | 1982 |
| 3 | Lucas Almeida | Brazil | 2025-01-17 | 1 | 1976 |
| 4 | Oliver Hartley | United Kingdom | 2025-01-22 | 1 | 1978 |
| 5 | Priya Banerjee | United Kingdom | 2025-01-27 | 2 | 1999 |
| 6 | Jordan Whitaker | United States | 2025-02-01 | 1 | 2006 |
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.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.
