The Ones Who Finish

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

mid

Asked in

DoorDash

Problem

A fitness streaming app measures engagement by how far members get through each clip before dropping off, comparing time watched against the clip's full length. For views logged this year, find the average completion and the number of views per content format, ignoring items with no recorded length.

Plenty of members press play. Far fewer reach the final rep. Find the formats that hold them.

Tables

content_views

Columns: view_id INTEGER, content_id INTEGER, user_id INTEGER, viewed_at TEXT, watch_seconds INTEGER, device_id INTEGER

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

Expected output

Worked solution and explanation

Why this problem exists in real interviews

This looks like an engagement report, but it is really a grain-and-ratio question wearing a fitness costume. The metric lives in content_views (watch_seconds) while the denominator lives in content_items (duration_seconds), so you cannot compute completion until you join, and once joined the only honest unit is one ratio per view. The trap is what 'average completion' means: do you average the per-view ratios, or do you divide total watch by total length? Those are different numbers, and picking the wrong one silently lets a few long clips dominate the format's score.

Break down the requirements

Step 1: Attach each view to its clip

watch_seconds has no meaning without the clip length next to it, so join content_views to content_items on content_id. After the join every row is one view that knows both how long the member watched and how long the clip runs.

Step 2: Scope to this year and guard the denominator

strftime('%Y', cv.viewed_at) = '2026' keeps the current era. duration_seconds > 0 is the 'ignoring items with no recorded length' clause made literal: an article or a zero-length item would make watch_seconds / duration_seconds divide by zero and surface as a stray null format, and dropping those rows is the correct reading since a clip with no length has no completion to measure.

Step 3: Average the per-view ratio, by format, and count the views

GROUP BY content_type, then AVG(watch_seconds * 1.0 / duration_seconds) for the metric and COUNT(*) for how many views backed it. The * 1.0 forces real division; without it integer math floors most ratios to 0. Order by that average descending so the format that holds attention sits on top.

The solution

SELECT ci.content_type AS content_format,
       ROUND(AVG(cv.watch_seconds * 1.0 / ci.duration_seconds), 3) AS avg_completion_rate,
       COUNT(*) AS view_count
FROM content_views cv
JOIN content_items ci ON cv.content_id = ci.content_id
WHERE strftime('%Y', cv.viewed_at) = '2026'
  AND ci.duration_seconds > 0
GROUP BY ci.content_type

Common follow-up questions

• Rewrite this as total watch time over total length per format. When would a product team prefer that number over the average of ratios? (Tests whether they can express the ratio-of-averages form and reason about weighting by duration vs by view.)
• Only count a view as a 'finish' if completion reaches at least 90 percent, then report the finish rate per format. (Pushes toward a CASE-based 0/1 collapse: `AVG(CASE WHEN watch/duration >= 0.9 THEN 1 ELSE 0 END)`.)
• How would you keep this incremental over a rolling 28 day window on a 2B row fact? (Surfaces partition pruning on `viewed_at` and pushing the date predicate onto the base scan.)