# Second Fiddle

> The spotlight finds number one. The story is in what comes right after.

Canonical URL: <https://datadriven.io/problems/second_fiddle>

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

We measure each content format by how many items registered account holders have published, so an item whose creator has no matching user record does not add to a format's total. Take the format sitting second by that total (include every format tied for second) and return the complete records of all its items, whoever the creator is.

## Worked solution and explanation

### What this really is

Strip the business framing and this is a two-step ranking problem with two separate traps stacked on top of each other. First trap: do you know what DENSE_RANK does that ROW_NUMBER does not when two content types share the exact same count? Second, subtler trap: the registered-creator filter decides WHICH type places second, but it does not decide which rows come back. The question asks for every item of the winning type, whoever published it. Grab 'the second one' with ROW_NUMBER, or re-apply the creator filter on the way out, and the query still runs clean while the answer is quietly wrong.

> **Trick to solving**
>
> The phrase 'include every format tied for second' rules out ROW_NUMBER and LIMIT/OFFSET, which each collapse ties to a single row. DENSE_RANK assigns the same rank to equal counts and never skips a number, so filtering rank = 2 pulls every tied type at once.

---

### Break down the requirements

#### Step 1: Scope to registered creators (for counting only)

Scope content_items to rows whose creator_id appears in users, via WHERE creator_id IN (SELECT user_id FROM users). Content from unregistered creators, and the null creator_id, drops out before any counting happens. This filter exists ONLY to decide the ordering, so keep it confined to the counting step.

#### Step 2: Count per type and rank

GROUP BY content_type with COUNT(*) to get per-type volume, then assign DENSE_RANK() OVER (ORDER BY COUNT(*) DESC). Using DENSE_RANK rather than ROW_NUMBER is what lets tied types share a rank.

#### Step 3: Keep the second rank

Filter the ranked types to rnk = 2. Because DENSE_RANK gives every tied type the same value, this naturally keeps all types sitting at second place, not just one.

#### Step 4: Return the full records

Join the qualifying type(s) back to the FULL content_items table, not the filtered set, and order by content_id for a stable result. Because the creator filter lived only inside the counting step, items from unregistered or null creators reappear here as long as their type placed second. The livestream with a null creator_id (content_id 587) is exactly such a row, and it belongs in the output.

---

### The solution

**Count under the creator filter, rank with DENSE_RANK, then rejoin the full table**

```sql
WITH type_counts AS (
    SELECT
        content_type,
        COUNT(*) AS cnt,
        DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
    FROM content_items
    WHERE creator_id IN (SELECT user_id FROM users)
    GROUP BY content_type
)
SELECT ci.*
FROM content_items ci
INNER JOIN type_counts tc ON ci.content_type = tc.content_type
WHERE tc.rnk = 2
ORDER BY ci.content_id
```

> **Do not re-filter on the way out**
>
> The creator_id IN (SELECT user_id FROM users) filter belongs ONLY inside the counting CTE. Re-applying it in the final SELECT quietly drops items whose creator is unregistered or null, even though those items still belong to the second-place type. The livestream with a null creator_id is the row that vanishes, and the query still runs clean, so nothing warns you.

> **Cost analysis**
>
> content_items is 8M rows (4 GB), but the window function runs after the GROUP BY, so it ranks only the dozen or so distinct content types, not the full table. The heavy cost is the scan plus the creator_id membership check for counting, and a second scan to pull the winning type's full records, not the ranking itself.

> **Interviewers watch for**
>
> The candidate who reaches for DENSE_RANK and can say out loud why ROW_NUMBER would be wrong, and who notices that the creator filter scopes the count but not the returned rows, is the one who has actually parsed the requirement. Naming both distinctions unprompted is the seniority signal.

> **Common pitfall**
>
> ROW_NUMBER or LIMIT 1 OFFSET 1 silently returns a single type even when two types tie for second, dropping half the correct rows with no error. It also breaks at first place: a tie for the top count shifts your 'second' pick to a still-first-place type.

---

## Common follow-up questions

- How would you change the query if the returned records should ALSO be limited to registered creators? _(Tests whether the candidate can articulate the count-versus-return asymmetry as a general modeling idea.)_
- How would the answer change if you used RANK() instead of DENSE_RANK()? _(Tests the candidate's grasp of RANK vs DENSE_RANK gap behavior.)_
- How would you compute this if content_items were sharded across multiple databases? _(Tests distributed query reasoning: where to push down the creator filter and how to merge per-shard type counts.)_
- What happens to items whose creator_id points at a deleted user, and how does your query treat them? _(Tests referential integrity awareness and how the IN filter already guards against orphaned creator references during counting.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/second_fiddle)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.