The Ones Who Hold Attention

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

How long do viewers stay with each creator's work, measured as the average watch_seconds across every view in content_views of the pieces that creator published in content_items? We want one figure per creator_id, with views of pieces that have no credited creator pooled into a single blank row.

Time on screen is the real vote. Find the creators earning it.

Tables

content_views

Columns: view_id INTEGER, content_id INTEGER, user_id INTEGER, device_id INTEGER, viewed_at TEXT, watch_seconds 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

Under the attention-analytics costume, this is two-hop attribution. A row in content_views names a piece of content. It never names whose piece it is. That fact lives only in content_items. Anyone can write the GROUP BY and the AVG. What separates candidates is the bridge they pick. content_id is the real link. user_id against creator_id only looks like one. Pick the look-alike and every creator gets credited with their own browsing instead of with the attention their work earned.

The trap

Both tables carry an id that smells like a person: cv.user_id and ci.creator_id. Writing ON cv.user_id = ci.creator_id compiles, runs, and returns believable numbers. But the user_id on a view is the viewer. Equate it with a creator and you are measuring how long creators watch other people's work, which is the exact opposite of the question. The ids even overlap in value, so nothing errors and nothing looks empty.

Step 1: Bridge each view to its piece on content_id

Inner-join content_items to content_views on cv.content_id = ci.content_id. content_id is unique in content_items, so each view picks up exactly one piece and one creator_id. Nothing fans out, which means no dedup worries anywhere downstream.

Step 2: Average the views per creator

Group by ci.creator_id and take AVG(cv.watch_seconds) over the raw views. AVG skips views with no recorded watch time, so an untimed view neither counts as zero seconds nor pads the denominator. Pieces with no credited creator all share a NULL creator_id, so their views collapse into the one blank row the statement asks to keep.

SELECT ci.creator_id,
       AVG(cv.watch_seconds) AS avg_watch_seconds
FROM content_views cv
INNER JOIN content_items ci
       ON cv.content_id = ci.content_id
GROUP BY ci.creator_id
;

Linking on the viewer

ON cv.user_id = ci.creator_id pairs a creator with the views they themselves made. It also repeats each of those views once per piece the creator published. The numbers describe creators as an audience.

Linking on the piece

ON cv.content_id = ci.content_id pairs each view with the piece it was a view of, exactly once. The numbers describe the audience a creator's work held.

Common follow-up questions

• Also report how many timed views each creator's average is built on. (Separates `COUNT(cv.watch_seconds)` from `COUNT(*)` and checks whether they know which views the average used.)
• Restrict to views from this year only. (Tests putting a `viewed_at` predicate before aggregation and how it changes which views feed each figure.)
• Include creators whose content was never viewed. (Pushes to a `LEFT JOIN` from `content_items` so that creators whose pieces drew no views still appear.)