Top Content by Views

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

Problem

Every row of content_views is one view of a piece in content_items, and an editor is filling a most-watched shelf from those views with every piece placed in the top five, most viewed at place 1. For each piece on the shelf, show its id, title, view count and place, along with how many pieces its creator has published in the whole catalog, zero when no creator is on record.

Top five content items by views.

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 top-N-with-ties problem in editorial costume. Anyone can count views per piece. What separates candidates is where the cut falls. On this data fifth place is a tie at 9 views, so the shelf holds more than five pieces, and the cut has to land on a view count, not a row. Write LIMIT 5 over the sorted pieces and the engine quietly decides which tied pieces survive; run it on another engine and the shelf changes. The second trap is the key: titles repeat across many different pieces, so grouping by title merges unrelated pieces into one inflated count.

Why five rows is the wrong unit

"Top five places" is a statement about view counts. A piece at 9 views is in fifth place whether it is the fifth row or the eighth. A row limit cannot see that, because it counts rows. A place that ties share, followed by a cut that keeps everyone at or above the boundary count, can.

Building it

Step 1: Count views per piece, keyed by its id

Inner-join content_items to content_views on content_id and COUNT(*) per piece. The inner join defines the field: a piece nobody viewed has no place. Group on content_id, because about twenty pieces can share a title like 'Breaking Down DevOps'.

Step 2: Place every watched piece

RANK() OVER (ORDER BY view_count DESC) runs over all of piece_views. Tied counts share a place and the next place is skipped, so a tie at fourth pushes the next piece to sixth, and it falls off the shelf.

Step 3: Cut on the fifth-best count

cut_line takes the five highest counts with LIMIT 5 and keeps their MIN. With five or more watched pieces that is the fifth-best count; with fewer, it is the lowest count, so every watched piece still makes the shelf. Keeping view_count >= fifth_best admits every piece tied at the boundary, which is exactly the set with view_place <= 5.

Step 4: Measure the creator against the whole catalog

creator_pieces is a correlated COUNT(*) over content_items itself, matched on creator_id. It reads the whole catalog, not piece_views, because a creator's unwatched pieces are still published. A NULL creator_id never satisfies the =, so that piece reports 0, which is what the editor asked for.

WITH piece_views AS (
  SELECT ci.content_id, ci.title, ci.creator_id, COUNT(*) AS view_count
  FROM content_items ci
  INNER JOIN content_views cv ON cv.content_id = ci.content_id
  GROUP BY ci.content_id, ci.title, ci.creator_id
),
standing AS (
  SELECT content_id, title, creator_id, view_count,
         RANK() OVER (ORDER BY view_count DESC) AS view_place
  FROM piece_views
),
cut_line AS (
  SELECT MIN(view_count) AS fifth_best
  FROM (
    SELECT view_count
    FROM piece_views
    ORDER BY view_count DESC
    LIMIT 5
  ) top_five_counts
)
SELECT s.content_id, s.title, s.view_count, s.view_place,
       (SELECT COUNT(*)
        FROM content_items mine
        WHERE mine.creator_id = s.creator_id) AS creator_pieces
FROM standing s
WHERE s.view_count >= (SELECT fifth_best FROM cut_line)

The trap

SELECT ci.content_id, ci.title, COUNT(*) AS view_count
FROM content_items ci
INNER JOIN content_views cv ON cv.content_id = ci.content_id
GROUP BY ci.content_id, ci.title
ORDER BY view_count DESC
LIMIT 5

Cut on rows

LIMIT 5 on the sorted pieces keeps five rows. The pieces tied at 9 views compete for the last slot, and the losers vanish without a trace.

Cut on a count

fifth_best names the boundary count, and view_count >= fifth_best keeps every piece at or above it. The shelf grows to fit the tie.

Common follow-up questions

• Switch the place to `DENSE_RANK()`. How many pieces reach the shelf now, and why? (Tests the difference between a ranking with gaps and one that compacts.)
• Return the top five places within each `content_type` instead of overall. (Tests partitioning the ranking by type.)
• Count only views where `watch_seconds` exceeds 30. What happens to views where it is `NULL`? (Tests a filter on a column that holds `NULL`s.)
• Compute `creator_pieces` without a correlated subquery. (Tests a pre-aggregated join with `COALESCE` in place of the correlated subquery.)