medium Employee Collaboration Networks

An employee's project count is the size of the set of distinct project_ids they touch as either the inviter or the invitee, so the whole problem reduces to folding both role columns into one id column, counting distinct projects per id, and emitting every id whose count equals the global maximum (so ties survive).

The shape of the data

CollaborationAccepted(inviter_id, invitee_id, project_id, accept_date) stores each accepted collaboration once. The trap is that a single person appears in two columns: employee 2 is an inviter in some rows and an invitee in others. To count "projects per person" you must treat both columns as the same entity.

Mechanism: pivot two columns into one

The base table is "wide" on roles — inviter and invitee sit side by side. A correct count needs them stacked into a single (id, project_id) column. That stacking is exactly what a UNION/UNION ALL of two SELECTs does: each branch projects one role column as id, and the two result sets are concatenated vertically.

The teachable subtlety the grader flagged: UNION and UNION ALL are not interchangeable here for performance, and pairing the wrong one with COUNT(DISTINCT) does duplicate dedup work.

• UNION sorts (or hashes) the combined rows and removes duplicates — an extra dedup pass over every row.
• UNION ALL just concatenates — no sort, no dedup, cheaper.

Because the next step is COUNT(DISTINCT project_id), the distinct-ness we actually need is enforced by COUNT(DISTINCT …) anyway. So UNION + COUNT(DISTINCT) deduplicates twice — once globally in UNION, once per group in the count. The clean version uses UNION ALL and lets COUNT(DISTINCT) do the one dedup that matters.

The corrected query

One CTE stacks the roles, a second counts distinct projects per id, and the final query keeps every id tied at the maximum. Standard SQL, runs on MySQL 8 / PostgreSQL / SQLite.

WITH all_collabs AS (
    SELECT inviter_id AS id, project_id FROM CollaborationAccepted
    UNION ALL                       -- cheap concat; no dedup pass
    SELECT invitee_id AS id, project_id FROM CollaborationAccepted
),
project_counts AS (
    SELECT id, COUNT(DISTINCT project_id) AS num_projects
    FROM all_collabs
    GROUP BY id                     -- COUNT(DISTINCT) is the one dedup we need
)
SELECT id, num_projects
FROM project_counts
WHERE num_projects = (SELECT MAX(num_projects) FROM project_counts);

Why the "naive" UNION version is not wrong, just wasteful

The original solution used UNION (not UNION ALL) and still got the right answer, because COUNT(DISTINCT project_id) would have removed any surviving duplicates regardless. The result is correct; the cost is not. UNION forces a global sort/hash dedup over all 2N rows before grouping — wasted work, since COUNT(DISTINCT) dedups again per group. The only time UNION's dedup matters is if you intended COUNT(*) (plain count) — then UNION's dedup would be load-bearing and silently change the answer. Make the dedup explicit and local: UNION ALL + COUNT(DISTINCT).

Traced execution on the example

Input rows: (1,2,100) (1,3,101) (2,3,102) (3,4,103) (2,4,104).

Step 1 — UNION ALL stacks 5 + 5 = 10 (id, project_id) pairs

Inviter branch: (1,100)(1,101)(2,102)(3,103)(2,104). Invitee branch: (2,100)(3,101)(3,102)(4,103)(4,104). No rows are dropped — that is the point of UNION ALL.

Step 2 — GROUP BY id, COUNT(DISTINCT project_id)

Step 3 — filter on the max

MAX(num_projects) = 3. The WHERE num_projects = 3 predicate keeps both id 2 and id 3 — the tie is preserved automatically because we compare to the max rather than ordering and taking one row.

id | num_projects
 2 |     3
 3 |     3

Matches the expected output exactly.

Pitfalls

• LIMIT 1 or ORDER BY … LIMIT 1 to grab "the top" — silently drops tied employees. The problem explicitly asks for all ties, so you must filter against MAX(...), not sort-and-truncate.
• UNION + COUNT(*) (forgetting DISTINCT) — if the same person collaborates on the same project in two rows (allowed: the PK is the full triple, so (2,3,102) and (2,5,102) both give id 2 → project 102), COUNT(*) would over-count repeats. Here UNION's dedup would mask it, but on UNION ALL it breaks. Always COUNT(DISTINCT project_id).
• Counting only one role — summing just inviter_id misses every project a person was invited to. Employee 2 would score 2 instead of 3 and the tie would vanish.
• Window-function alternative miscounting ties — RANK() OVER (ORDER BY num_projects DESC) then WHERE rank = 1 works and keeps ties; ROW_NUMBER() does not (it breaks ties arbitrarily). Know which one preserves duplicates.
• NULL ids — if invitee_id could be NULL (it cannot here, part of the PK), GROUP BY id would create a spurious NULL group. Guard with a WHERE id IS NOT NULL on schemas where the column is nullable.

Takeaways

• When an entity lives in two columns (inviter/invitee, sender/receiver, from/to), UNION ALL them into one column before aggregating — that is the canonical "edge-to-node" unpivot.
• Pick the dedup deliberately: UNION ALL + COUNT(DISTINCT) does exactly one dedup, in the right place. UNION + COUNT(DISTINCT) dedups twice and only ever costs you.
• To keep ties, compare to a computed MAX(...) (or use RANK), never ORDER BY … LIMIT 1.
• CTEs make the pipeline auditable: stack → count → filter, each stage independently checkable against the trace above.

Re-authored and deepened for this guide. Problem adapted from the LeetCode-style "Employee Collaboration Networks" exercise. UNION vs UNION ALL cost model and the redundant-dedup point follow the PostgreSQL documentation on set operations and Markus Winand's SQL Performance Explained; the tie-preservation pattern (compare to MAX, or RANK over ROW_NUMBER) is standard SQL-99 window-function behavior as described in the PostgreSQL and MySQL 8 reference manuals.