Average Watch Time by Format

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

L3

Problem

An analyst found that the format comparison sheet averages watch time per title first, so a piece watched once counts as much as one watched fourteen times. Rebuild it from content_views, taking each view's format from content_items, so every view weighs the same in its content type's average watch time, with the content types listed alphabetically.

Which content format keeps viewers watching the longest

Tables

content_items

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

content_views

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

Expected output

Worked solution and explanation

This is a view-weighted mean across a join, dressed up as a format review. content_type lives only on content_items and watch_seconds only on content_views, so no single table holds both the key and the measure: the join on content_id is the price of entry. Anyone writes the join. What separates candidates is the grain of the average. It has to be taken over view rows after the join, not over per-title averages, and its denominator may count only views that logged a watch time. On this seed one piece has 14 views while most have one, so averaging per title first lets a single view weigh as much as fourteen. Dividing SUM(watch_seconds) by COUNT(*) counts the 16 views with no watch time and drags the formats holding them down.

Walk it

Step 1: Bring the format onto each view

content_views holds one row per view with its content_id and watch_seconds, and the format is content_type on content_items. Join the two on content_id so each view row carries its format. content_id is unique in content_items, so the join attaches exactly one format to each view and never duplicates one.

Step 2: Average at the view grain

Group the joined rows by ci.content_type and take AVG(cv.watch_seconds) directly. Each view row contributes once, so a title with 14 views pulls its format 14 times as hard as a title with one. That is exactly what 'every view weighs the same' asks for.

Step 3: Let AVG handle the missing watch times

16 views have watch_seconds NULL: the view happened, but no watch time was logged. AVG drops them from the numerator and the denominator alike, so you need no COALESCE and no filter. Substituting 0 would claim those viewers watched nothing.

Step 4: List the formats alphabetically

Sort by ci.content_type, which the query returns. The order follows the format name, not the average.

SELECT
    ci.content_type,
    AVG(cv.watch_seconds) AS avg_watch_time
FROM content_items ci
INNER JOIN content_views cv ON ci.content_id = cv.content_id
GROUP BY ci.content_type
ORDER BY ci.content_type

Average of per-title averages

Average watch_seconds per content_id in a CTE, then average those per content_type. A title watched once counts as much as one watched 14 times, so the format's number follows its catalogue, not its audience.

Average over views

Join first and take AVG(cv.watch_seconds) per content_type over the view rows. Each view counts once, which is the figure the sheet was supposed to show.

Common follow-up questions

• How would you show each format's sample size next to its average? (`COUNT(cv.watch_seconds)` versus `COUNT(*)`: views with a logged watch time versus all views.)
• Product wants views under 5 seconds treated as accidental and excluded. Where does that filter go? (A `WHERE cv.watch_seconds >= 5` before grouping; with an inner join, `ON` and `WHERE` keep the same rows.)
• What if they want the per-title average instead, so the catalogue mix matters rather than the audience? (Average per `content_id` in a CTE, then average those per format: the comparison's left side, now as the intended answer.)
• How would you report completion rate per format? (`watch_seconds` over `duration_seconds` per view, guarding the 40 missing durations and wrapping the divisor in `NULLIF`.)