Skip rate by device
Product wants to know on which device people lose patience fastest. Following the industry convention, a play is a skip when seconds_played is less than 30 (exactly 30 seconds is a real stream, not a skip).
Task: for every device, return the number of plays as plays, the number of skips as skips and the skip rate as skip_rate_pct (skips divided by plays times 100, ROUND(x, 2)). Sort by skip_rate_pct from highest to lowest, then by device A to Z.
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?
Conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END).
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.