Creator Favorite Content Type

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

Our profile badge that names a creator's favorite format picks one at random whenever a creator publishes two formats equally often, and it should show every format tied for the lead. Using content_items, find the content type each creator publishes most often.

Every creator has a go-to format.

Tables

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

This is a per-group mode in a profile-badge costume: for every creator, the content type with the most items, every tied type kept. Anyone can count by creator_id and content_type. What separates answers is how the top type per creator gets picked. Pick it by value with MAX(content_type) and the items with no creator report 'video' when 'livestream' leads 6 to 4. Pick exactly one row per creator and, the day two formats tie, a creator's badge quietly loses one.

Where the data bites

Read the seed before writing anything. Every named creator in content_items publishes a single format, so almost any query looks right on them. The one group with real competition is the creatorless one: rows with a NULL creator_id cover all five types, 'livestream' on 6 items and the rest on 4 each. That group is where wrong answers show up, and it is the first row of the expected output.

Building it

Step 1: Count items per creator and type

Use GROUP BY creator_id, content_type with COUNT(*). Count rows, not a column: COUNT(creator_id) is 0 for every type in the NULL group, which ties all five types at the top and returns five rows where one belongs.

Step 2: Rank types inside each creator

RANK() OVER (PARTITION BY creator_id ORDER BY COUNT(*) DESC) is evaluated after the grouping, so it orders the per-type counts, not raw items. PARTITION BY puts every NULL key in one partition, so the creatorless items compete among themselves instead of vanishing.

Step 3: Keep rank 1

WHERE rnk = 1 in the outer query keeps the leader. Because RANK() gives equal counts the same number, a two-way tie keeps two rows, which is exactly what the badge needs.

WITH type_counts AS (
    SELECT
        creator_id,
        content_type,
        COUNT(*) AS cnt,
        RANK() OVER (PARTITION BY creator_id ORDER BY COUNT(*) DESC) AS rnk
    FROM content_items
    GROUP BY creator_id, content_type
)
SELECT creator_id, content_type
FROM type_counts
WHERE rnk = 1

One pick per creator

ROW_NUMBER(), or ORDER BY with LIMIT 1 per creator, always emits exactly one row. It matches today's data, where no named creator ties, and drops a format the first time one does.

Every type at the top

RANK() hands tied counts the same rank, so rnk = 1 keeps all of them. Same cost, and the answer stays right whether or not the data ties.

Common follow-up questions

• What if the badge should show each creator's top two formats? (`rnk <= 2` works, but now `RANK()` and `DENSE_RANK()` differ: after a two-way tie for first, `RANK()` has no 2.)
• Product decides a tie should show one format, alphabetically first. What changes? (Switch to `ROW_NUMBER()` and add `content_type` as a second sort key so the single pick is deterministic.)
• How would you add each favorite format's share of the creator's items? (A second window, `SUM(COUNT(*)) OVER (PARTITION BY creator_id)`, over the same grouped rows.)