Top Content by Watch Time

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

L4

Asked in

BeyondTrust

Problem

A media platform wants to know which content holds the most attention among its signed-in audience, so it totals watch time only from views tied to a logged-in viewer. To keep untracked traffic off the board, it also drops any content that never drew a view from a recognized device. Return the content in the top three tiers by total watch time, where items sharing a total belong to the same tier, showing the content ID, title, and total watch time, highest first.

Some content holds attention. Others get skipped.

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

What this problem is really testing

Strip the 'watch time' costume and this is a scoped aggregate ranked into tie-tiers, guarded by two quality gates. The skill: sum watch time per content, but only over views that survive both gates, then return whole tiers of totals rather than a fixed row count. Anyone can write the join and the SUM. Three things separate candidates. First, the scope: count anonymous views (user_id is null) and every total inflates, so the wrong content floats up. Second, the untracked gate: content whose views never carry a device_id is traffic you cannot trust, and it has to be dropped after grouping, which is exactly what a COUNT-based HAVING is for. Third, the tie semantics: 'top three tiers including ties' rules out LIMIT 3 and ROW_NUMBER, which both cut a tied third tier. Miss any one and your answer is confidently wrong.

Building it up

Step 1: Scope to signed-in views

The metric is watch time from the signed-in audience, so add WHERE cv.user_id IS NOT NULL. This is not decorative: about a tenth of views are anonymous and they carry real watch seconds, so dropping this filter changes totals and can reorder the top three. Note it filters the VIEWER, not the duration; a signed-in view can still have a null watch_seconds, and SUM simply ignores that value.

Step 2: Aggregate per content

First CTE: INNER JOIN content_items to content_views on content_id, then GROUP BY ci.content_id, ci.title and SUM(cv.watch_seconds) AS lifetime_value. Carry title in the group key so you can return it without a second pass over the dimension table.

Step 3: Drop untracked content

A content whose every view has a null device_id is untracked traffic you should not rank. That is a condition on the GROUP, not on one row, so it belongs in HAVING COUNT(cv.device_id) >= 1: COUNT ignores nulls, so a content with no recognized device counts zero and is dropped. Do this in HAVING, not WHERE: a per-view device_id IS NOT NULL would keep only some of a content's views and mis-total the rest.

Step 4: Rank the totals into tiers

Second CTE: DENSE_RANK() OVER (ORDER BY lifetime_value DESC). Dense, not regular RANK: regular RANK skips numbers after a tie, so two items tied at tier 1 push RANK to 3 and tier <= 3 would grab a fourth-place item while pretending it saw three tiers. DENSE_RANK gives 1, 1, 2, so three tiers means three genuine total levels.

Step 5: Keep the top three tiers, ordered

Outer SELECT ... WHERE tier <= 3, then ORDER BY lifetime_value DESC, content_id so the output is deterministic even when two items tie. This is the only place <= 3 appears; do not also add LIMIT 3 or you reintroduce the truncation you just avoided.

The solution

WITH content_stats AS (
  SELECT
    ci.content_id,
    ci.title,
    SUM(cv.watch_seconds) AS lifetime_value
  FROM content_items ci
  INNER JOIN content_views cv ON ci.content_id = cv.content_id
  WHERE cv.user_id IS NOT NULL
  GROUP BY ci.content_id, ci.title
  HAVING COUNT(cv.device_id) >= 1
),
ranked AS (
  SELECT
    content_id,
    title,
    lifetime_value,
    DENSE_RANK() OVER (ORDER BY lifetime_value DESC) AS tier
  FROM content_stats
)
SELECT content_id, title, lifetime_value
FROM ranked
WHERE tier <= 3
ORDER BY lifetime_value DESC, content_id

Common follow-up questions

Common follow-up questions

• How would you return the top three tiers per `content_type` instead? (Probes whether you add `PARTITION BY ci.content_type` to the `DENSE_RANK` window and carry `content_type` through the first CTE.)
• Replays can push `watch_seconds` above the content's duration. How would you cap it? (Tests `LEAST(cv.watch_seconds, ci.duration_seconds)` inside the SUM, showing comfort with row-level expressions inside an aggregate.)
• How would you do this without a window function? (Looks for a scalar subquery against the third-highest distinct `lifetime_value`, and forces you to articulate why `DENSE_RANK` is cleaner.)
• This is too slow at a billion views. What do you change first? (Expects pushing the null filter and aggregation down before the join, or pre-aggregating `content_views` by `content_id` in a rollup so the billion-row scan happens once.)