Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Which tracks people actually finish · SQL scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Which tracks people actually finish

The catalogue team wants to know which tracks listeners actually finish. stream_plays.ms_played is how much of the track was played and stream_tracks.duration_ms is how long the track is, so the ratio of the two is the completion of that single play. Return one row per track with at least 3 plays, ordered by completion descending.

Result columns · in this order

`track_id`	The track.
`title`	Its title.
`plays`	How many times it was played.
`avg_completion_pct`	Average share of the track that was listened to.

How to approach it

Compute the ratio per play, then average it — and check the arithmetic is not integer division.

Sample input

stream_playsOne row per play: a listener started a track at played_at and listened for ms_played.

play_id	user_id	track_id	played_at	ms_played	device
1	u1	t1	2026-01-05 20:00:00	210000	mobile
2	u1	t2	2026-01-05 20:04:00	180000	mobile
3	u1	t1	2026-01-05 20:08:00	150000	mobile
4	u1	t9	2026-01-05 21:10:00	200000	mobile
5	u2	t3	2026-01-06 07:00:00	240000	desktop
6	u2	t4	2026-01-06 07:10:00	195000	desktop
7	u2	t3	2026-01-06 09:00:00	30000	desktop
8	u3	t5	2026-01-09 22:00:00	300000	mobile
9	u3	t6	2026-01-09 22:06:00	270000	mobile
10	u3	t5	2026-01-10 22:00:00	300000	mobile
11	u4	t7	2026-01-15 17:00:00	225000	mobile
12	u4	t8	2026-01-15 17:04:00	250000	mobile
13	u5	t1	2026-01-20 06:30:00	210000	desktop
14	u5	t3	2026-01-20 06:34:00	240000	desktop
15	u4	t7	2026-02-02 17:00:00	225000	mobile
16	u1	t1	2026-02-03 19:00:00	210000	desktop
17	u1	t5	2026-02-03 19:05:00	300000	desktop
18	u5	t5	2026-02-05 06:30:00	300000	desktop
19	u5	t7	2026-02-05 07:15:00	225000	desktop
20	u2	t10	2026-02-11 12:00:00	165000	mobile
21	u2	t3	2026-02-11 12:03:00	240000	mobile
22	u3	t6	2026-02-14 21:00:00	270000	desktop
23	u3	t5	2026-02-14 21:05:00	120000	desktop
24	u6	t2	2026-02-18 13:00:00	180000	mobile
25	u6	t1	2026-02-18 13:03:00	210000	mobile
26	u5	t2	2026-03-01 09:00:00	180000	mobile
27	u5	t9	2026-03-01 09:03:00	200000	mobile
28	u1	t9	2026-03-02 08:30:00	60000	mobile
29	u2	t4	2026-03-05 18:00:00	100000	mobile
30	u3	t6	2026-03-08 20:00:00	270000	mobile
31	u4	t8	2026-03-11 16:00:00	20000	mobile
32	u6	t2	2026-03-15 13:00:00	90000	mobile
33	u8	t10	2026-03-20 11:00:00	165000	mobile
34	u8	t4	2026-03-20 11:03:00	195000	mobile
35	u8	t10	2026-03-20 11:04:00	165000	mobile
36	u8	t9	2026-03-20 11:07:00	200000	mobile

36 rows — scroll inside the table to see them all.

stream_tracksOne row per track in the catalogue. track_id is unique.

track_id	title	artist	genre	duration_ms
t1	Northern Lights	Aurora Ridge	indie	210000
t2	Paper Boats	Aurora Ridge	indie	180000
t3	Static Bloom	Kite Machine	electronic	240000
t4	Night Signal	Kite Machine	electronic	195000
t5	Slow Harbour	Marisol	jazz	300000
t6	Blue Hour	Marisol	jazz	270000
t7	Copper Wire	Fenpost	rock	225000
t8	Ash and Ember	Fenpost	rock	250000
t9	Glass Field	Aurora Ridge	indie	200000
t10	Quiet Riot Hour	Kite Machine	electronic	165000

10 rows — all rows shown.

Expected output

The exact result your query should return for this input.

track_id	title	plays	avg_completion_pct
t10	Quiet Riot Hour	3	100
t6	Blue Hour	3	100
t7	Copper Wire	3	100
t1	Northern Lights	5	94.3
t5	Slow Harbour	5	88
t2	Paper Boats	4	87.5
t4	Night Signal	3	83.8
t9	Glass Field	4	82.5
t3	Static Bloom	4	78.1

9 rows — all rows shown.

Constraints

1Completion for one play is 100.0 * ms_played / duration_ms. avg_completion_pct is the average of those per-play values, rounded to 1 decimal place.
2Both columns are integers. Divide without a floating-point literal and SQLite floors the result — a 12.5% play becomes 0.
3Only tracks with 3 or more plays qualify. One skip should not decide a track's reputation.
4That filter tests the group rather than the row, so it belongs in HAVING.
5Order by avg_completion_pct descending, then track_id — three tracks are level on 100.0.

Worked example

Static Bloom is 240,000 ms long and was played four times: three complete plays and one 30,000 ms skip. The per-play completions are 100, 100, 100 and 12.5, so avg_completion_pct is 78.1. Write it as AVG(ms_played / duration_ms) * 100 and integer division floors each ratio to 1 or 0 before the average sees it, giving 75.0 — a plausible number that no listener produced.

What this tests

Composing a join with a per-row ratio and a group-level filter, plus the integer division trap that silently turns a percentage into zero.

SQL

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules