Find Interview Candidates

Two unrelated qualifications get computed in one pass and then OR'd together: a gaps-and-islands trick collapses each user's contest IDs into consecutive runs (subtracting a per-user row number from contest_id yields a constant only while IDs are contiguous), while a plain COUNT over the gold_medal column counts gold wins; a row surviving either branch is a candidate.

The problem (LeetCode 1378): a user is an interview candidate if they won any medal in 3+ consecutive contests, or won the gold medal in 3+ contests (consecutive or not). Contests have gap-free, consecutive IDs.

The two conditions are genuinely different

The single most common mistake here is treating these as one rule. They aren't. Condition 1 cares about position (a streak) and accepts any medal color. Condition 2 cares about count (total golds) and ignores position entirely. A user can satisfy one and fail the other — and in this dataset, the most interesting candidate qualifies only via the gold branch.

The mechanism: why contest_id − rn buckets a streak

Number a user's contests 1,2,3,… in ascending contest_id order with ROW_NUMBER(). Whenever the contests are consecutive, both the ID and the row number increase by exactly 1 per step, so their difference stays constant. The instant a gap appears, contest_id jumps but rn still only ticks by 1, so the difference jumps too — starting a new bucket. Grouping by that difference and keeping groups of size ≥ 3 finds every run of three or more consecutive contests.

The query

The one correctness fix versus the naive version: the medalist union must keep the medal color (or at least keep gold separable) — otherwise the gold-count branch can't be evaluated. Here the gold branch reads the Contests table directly, so the union only needs (user, contest_id) for the streak branch. Both branches are UNION'd (set union dedupes user IDs) and joined to Users.

WITH medalists AS (          -- one row per (user, contest), any medal
    SELECT gold_medal   AS user_id, contest_id FROM Contests
    UNION ALL
    SELECT silver_medal AS user_id, contest_id FROM Contests
    UNION ALL
    SELECT bronze_medal AS user_id, contest_id FROM Contests
),
numbered AS (
    SELECT user_id, contest_id,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY contest_id) AS rn
    FROM medalists
),
candidates AS (
    -- Condition 1: any medal in 3+ consecutive contests
    SELECT user_id
    FROM numbered
    GROUP BY user_id, contest_id - rn
    HAVING COUNT(*) >= 3

    UNION                    -- set union: dedupes user_ids across both rules

    -- Condition 2: gold medal in 3+ contests
    SELECT gold_medal AS user_id
    FROM Contests
    GROUP BY gold_medal
    HAVING COUNT(*) >= 3
)
SELECT u.name, u.mail
FROM candidates c
JOIN Users u ON u.user_id = c.user_id;

Why the naive version is wrong: if medalists aliases all three medal columns to a generic user and you then try to count golds from that union, you cannot — a row in the union no longer remembers whether it came from gold, silver, or bronze. The gold rule must read Contests.gold_medal directly. (Using UNION ALL for the final candidate combine instead of UNION is harmless here only because the outer query is JOIN + an implicit dedupe is not applied — to be safe, use UNION, or SELECT DISTINCT in the final projection.)

Worked example (the exact LeetCode 1378 data)

Contests (contest_id, gold, silver, bronze):

After the union + ROW_NUMBER, each user's medal contests (sorted) and the resulting id − rn buckets:

Condition 1 (streak) candidates: {2, 3, 5}.

Now the gold branch — count of golds per user from the gold_medal column: contests 190,193,196 → user 1 (×3); 191 → user 2; 192 → user 5; 194,195 → user 4 (×2). So gold counts are {1:3, 2:1, 4:2, 5:1}.

Condition 2 (gold ≥ 3) candidates: {1}. User 1 is the payoff case: contests 190,193,195,196 contain no 3-in-a-row, so condition 1 rejects them — but they took gold three separate times (190, 193, 196), so condition 2 admits them. This is exactly why the two branches cannot be collapsed.

Union {2,3,5} ∪ {1} = {1, 2, 3, 5}. Joining to Users (1=Alice, 2=Bob, 3=Alex, 4=Donald, 5=Rocky) drops user 4 and yields:

(Order is unspecified.)

Pitfalls

• Collapsing the two conditions. User 1 proves they're independent: no streak, but qualifies on gold count. A query that only does gaps-and-islands silently drops Alice and still returns a plausible-looking 3 rows — a bug that passes a careless eyeball check.
• Losing the medal color in the union. If you alias gold/silver/bronze into one anonymous user column and then try to count golds from that, you've thrown away the information you need. Count golds from Contests.gold_medal directly.
• The gaps-and-islands trick assumes gap-free contest IDs. It works because the contest schedule has consecutive IDs, so a user's own gap (a contest they didn't medal in) is what breaks the run. If contest IDs themselves had holes, id − rn would mis-bucket. With non-contiguous keys you'd instead use LAG() + a running sum of "is this a new run" flags, or ROW_NUMBER() over a densely-ranked key.
• Forgetting to dedupe the final candidate set. A user can hit both conditions; UNION ALL would then emit them twice and (without a DISTINCT on the projection) duplicate their name/mail. Use UNION, or SELECT DISTINCT at the end.
• NULL medalists. If any medal slot can be NULL (no entrant), those rows enter the union as a NULL user and quietly drop on the join — usually fine, but be deliberate about it rather than surprised.

Takeaways

• key − ROW_NUMBER() is the canonical gaps-and-islands tool: the difference is constant within a consecutive run and changes at every gap, so GROUP BY on it isolates runs.
• When a problem says "OR", compute each branch independently and combine with UNION; don't try to express two different shapes of condition in one aggregation.
• Decide what each branch needs before you build the shared CTE — the gold branch needs medal color, so the streak union can't be the only source of truth.
• Verify by tracing the adversarial row (here, user 1) that satisfies the less-obvious branch, not the easy one.

Based on LeetCode 1378 "Find Interview Candidates" (Database, premium), using its canonical sample Contests and Users data. Gaps-and-islands technique as described in Itzik Ben-Gan, T-SQL Querying / Microsoft SQL Server High-Performance T-SQL Using Window Functions, and the long-standing SQL community treatment of islands via row-number differencing. Re-authored and deepened for this guide: the medal-color loss in the union was fixed so the gold branch is computable, every user's buckets were derived rather than asserted, and user 1's gold-only qualification (the case the original example silently mis-traced) is now the worked centerpiece.