Second Fiddle

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Vesta Innovations

Problem

An analyst sizes up each content format in content_items by how many items registered account holders published, so an item whose creator_id has no match in users adds nothing to its format's total. For the format holding the second-highest total (every format, if several tie there), return the full record of each of its items, whoever created it, lowest content_id first.

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

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 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.

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 standings, 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 sort by content_id, which is unique, so the order is fully determined. 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

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

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.)