Content Types by Creator

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

Each row of content_items is one published piece tagged with its content_type and its creator_id, and a creator who publishes often appears on many rows. Across the creators whose creator_id is below 300, list the content types they have published, each type once.

One creator. What did they make?

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

What this really is

This is a dedupe question in an editorial costume. Everyone writes the filter correctly. The trap is believing that narrowing to a handful of creators also narrows the output to a handful of rows. It does not: content_items holds one row per piece, so each of those creators shows up several times. A bare projection of content_type returns twelve rows when the answer is three, with 'video' repeated four times, and the reviewer reading it believes the creators published far more kinds of content than they did.

Getting there

Step 1: Keep only the creators in scope

WHERE creator_id < 300 keeps the low-numbered creators. Rows with a missing creator_id drop out on their own, since a comparison against NULL is never true, so no extra guard is needed.

Step 2: Collapse the repeats

The question is about which types exist, not how many pieces carry them. SELECT DISTINCT content_type turns every pile of identical rows into one, which is exactly the list the reviewer asked for.

SELECT DISTINCT content_type
FROM content_items
WHERE creator_id < 300

Without dedupe

SELECT content_type ... WHERE creator_id < 300 returns one row per item: twelve rows, each type repeated once per piece the creator published.

With dedupe

SELECT DISTINCT content_type returns 'video', 'article' and 'podcast', one row each: the actual answer.

Common follow-up questions

• How would you show how many pieces each type has for these creators? (Switch to `GROUP BY content_type` with `COUNT(*)`; the dedupe now carries an aggregate.)
• Which creators in scope have published more than one type? (Group by `creator_id` and keep groups where `COUNT(DISTINCT content_type) > 1`.)
• What if `content_type` were entered by hand, with 'Video' and 'video ' both present? (Tests normalizing with `LOWER(TRIM(content_type))` before deduping, or one type counts twice.)