# Content by Specific Users > Two creators. What did they publish? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The community team is auditing a small group of creators. Pull every piece of content authored by users 'alice', 'aaron42', and 'amelia'. Show the content ID, title, content type, and creator ID, sorted by content ID. ## Worked solution and explanation ### What this is really asking Strip off the community-audit costume and this is a name-to-id lookup hiding behind a join. The content_items table only knows creators by a numeric creator_id; the human-readable names 'alice', 'aaron42', and 'amelia' live one table over, in users.username. The whole problem is the bridge between those two worlds. Anyone can write a SELECT with a filter. What separates a clean answer is realizing you cannot filter content_items by name at all, because the name simply is not in that table. > **Trick to solving** > > Filter on the side of the join where the name actually lives. You join content_items to users on creator_id = user_id, then put the username IN (...) filter on the users table. The join translates the three names into the matching user_ids for you, so you never have to know the numeric IDs yourself. #### 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 here: a content row whose creator_id is null (like 'Exploring Web Development') has no creator and should fall out, and that is precisely what an inner join does for free. #### Step 2: Filter by the three names, not three guessed IDs Apply WHERE u.username IN ('alice', 'aaron42', 'amelia'). Listing the names against the joined users table is what makes this robust: if the IDs ever change, the query still works, because you matched on the stable human identifier the team gave you. #### Step 3: Project and order Return ci.content_id, ci.title, ci.content_type, and ci.creator_id, then ORDER BY ci.content_id so the audit reads in a stable, predictable sequence. Qualify the columns with the content_items alias since content lives on that side. **Canonical solution** ```sql 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', 'amelia') ORDER BY ci.content_id ``` *Join to where the names live, filter there, project from content_items.* > **Common pitfall** > > The tempting shortcut is to skip the join and write WHERE creator_id IN (100, 197, 294) by eyeballing the users table. That hardcodes IDs the prompt never gave you, breaks the moment the data reseeds, and quietly returns the wrong rows if you transpose a digit. Match on the name the team actually handed you. > **Interviewers watch for** > > Whether you reach for the users table at all. A candidate who instantly asks 'where do the usernames live?' and joins to them signals they think in terms of where an attribute is stored, not where it would be convenient to filter. Filtering on the username column rather than reverse-engineering IDs is the tell of someone who has debugged a brittle hardcoded query before. > **Performance insight** > > With an index on users.username and on content_items.creator_id, the planner filters users down to three rows first, then probes content_items by foreign key. The IN list is just three equality checks, so this stays a tiny indexed lookup even when content_items grows into the millions; you never scan content just to throw most of it away. ## Common follow-up questions - How would you also return only content from creators whose account_status is not 'suspended'? _(Tests adding a second predicate on the joined users table without breaking the name filter.)_ - What changes if a creator could appear in users more than once, say one row per region? _(Tests awareness that a one-to-many join would duplicate content rows and would need a DISTINCT or a deduped subquery.)_ - How would you list the three named creators who have zero published items? _(Pushes toward an outer join or anti-join, the inverse of the inner-join shape used here.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/content_by_specific_users) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.