Content by Specific Users

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

Problem

Which pieces of content in content_items were authored by the accounts 'alice', 'aaron42' and 'ava99'? A reviewer auditing those three creators needs each item's content ID, title, content type and creator ID, sorted by content ID.

Two creators. What did they publish?

Tables

content_items

Columns: content_id INTEGER, title TEXT, content_type TEXT, duration_seconds INTEGER, creator_id INTEGER, publish_date TEXT

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this is really asking

Strip off the audit costume and this is a name-to-id lookup: a filter whose predicate lives in a different table from the rows you return. content_items only knows a creator as a numeric creator_id; the names 'alice', 'aaron42' and 'ava99' live one table over, in users.username. Anyone can write a SELECT with a filter. What separates answers is realizing you cannot filter content_items by name at all, because the name is not in that table. Skip the bridge and you either hit a missing column or hardcode ids you read off the screen, and those quietly pick up the wrong creators the day the ids move.

Step 1: Bridge content to its creator

Join content_items to users on ci.creator_id = u.user_id. An inner join is exactly right: content rows with a NULL creator_id (like 'Exploring Web Development') belong to nobody, and they drop out because NULL never equals anything.

Step 2: Filter by the three names, not three guessed ids

Apply WHERE u.username IN ('alice', 'aaron42', 'ava99'). Matching on the identifier the reviewer handed you keeps the query right even if ids are reassigned. Exact match is the point: 'Alice' with a capital A is a different account, and it stays out.

Step 3: Project from the content side and sort

Return ci.content_id, ci.title, ci.content_type and ci.creator_id, then sort by ci.content_id ascending. content_id is unique, so the order is fully determined and two correct answers cannot disagree.

SELECT ci.content_id, ci.title, ci.content_type, ci.creator_id
FROM content_items ci
JOIN users u ON ci.creator_id = u.user_id
WHERE u.username IN ('alice', 'aaron42', 'ava99')
ORDER BY ci.content_id

Common follow-up questions

• How would you also exclude content from creators whose `account_status` is 'suspended'? (A second predicate on the joined `users` side; 'aaron42' is suspended, so the result shrinks.)
• What changes if a username was stored with stray spaces, like ' amelia'? (Tests whether you normalize the stored key before matching instead of trusting the text as written.)
• How would you list the named creators who have zero items? (Pushes toward an outer join or anti-join, the inverse of the inner-join shape used here.)