Team Scores in Football Tournament

Mechanism

Each match contributes points to two rows, so you fan one Matches row out to both participants with a LEFT JOIN on team_id = host_team OR team_id = guest_team, then collapse the fan-out with SUM(CASE …) that reads the match from whichever side (host or guest) the team played, awarding 3 / 1 / 0. The LEFT side keeps teams that never played (their only joined row is all-NULL, so the CASE falls through to 0).

Schema and the canonical query

Teams(team_id PK, team_name) and Matches(match_id PK, host_team, guest_team, host_goals, guest_goals). Every finished match is one row holding both teams and both scores; nobody's total lives in one place, which is exactly why a plain join won't do.

SELECT t.team_id,
       t.team_name,
       SUM(CASE
             WHEN t.team_id = m.host_team  AND m.host_goals  > m.guest_goals THEN 3
             WHEN t.team_id = m.guest_team AND m.guest_goals > m.host_goals  THEN 3
             WHEN m.host_goals = m.guest_goals                              THEN 1
             ELSE 0
           END) AS num_points
FROM   Teams t
LEFT JOIN Matches m
       ON t.team_id = m.host_team
       OR t.team_id = m.guest_team
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id ASC;

Note the third WHEN: by the time control reaches it, the team is provably a participant (it survived the join) and neither win-branch fired, so host_goals = guest_goals alone correctly catches the draw for either side. For a team with no matches, m.host_goals is NULL; every comparison is NULL (not true), so the CASE returns 0 and SUM of a single 0 is 0 — the team still appears.

Worked example with real values

Five teams; Chicago (40) plays no match. Five matches:

After the OR-join, every match appears once per participant — match 1 produces one row for team 10 and one for team 20, never two identical rows for the same team. Here is the exact join output (one line per team-match), with the CASE evaluated:

GROUP BY then sums each team's CASE pts:

After ORDER BY num_points DESC, team_id ASC the final result is 10 (7), 20 (3), 50 (3), 30 (1), 40 (0) — the 20-before-50 tie broken by the smaller team_id, and Chicago surviving at 0 because of the LEFT join.

Why the naive version is wrong

A tempting shortcut writes the team into two rows itself — one fixed to the host slot, one to the guest slot — by joining twice or by a Cartesian-style condition that doesn't guard which side it scores. If the CASE then doesn't pin the comparison to the team's actual role, a single match can be counted twice for the same team, inflating points (the symptom: a phantom duplicate join row like 20 | 20 30 | 1 0 appearing twice for team 20, which the OR-join never produces — each match matches a given team on exactly one of the two OR arms). Always tie the win-test to the role: team_id = host_team AND host_goals > guest_goals, never just host_goals > guest_goals.

The UNION ALL alternative (and why it's often faster)

The OR in a join predicate is the expensive part: most planners cannot turn a = x OR a = y into a single index seek, so it degrades toward a nested-loop or hash join that touches the whole table. Splitting the two roles into a UNION ALL gives the optimizer two clean equi-joins, each index-friendly:

SELECT t.team_id, t.team_name,
       COALESCE(SUM(s.pts), 0) AS num_points
FROM Teams t
LEFT JOIN (
    SELECT host_team AS team_id,
           CASE WHEN host_goals > guest_goals THEN 3
                WHEN host_goals = guest_goals THEN 1 ELSE 0 END AS pts
    FROM Matches
    UNION ALL
    SELECT guest_team AS team_id,
           CASE WHEN guest_goals > host_goals THEN 3
                WHEN guest_goals = host_goals THEN 1 ELSE 0 END AS pts
    FROM Matches
) s ON s.team_id = t.team_id
GROUP BY t.team_id, t.team_name
ORDER BY num_points DESC, t.team_id ASC;

It must be UNION ALL, not UNION: a 3-0 win and another 3-0 win are identical (team_id, pts) rows and UNION would dedup them, silently dropping points. The COALESCE replaces the all-NULL sum for teams with no matches.

Pitfalls

• Dropping no-match teams. An INNER JOIN (or filtering on match_id IS NOT NULL) makes Chicago (40) vanish; the spec wants every team, including 0-point ones. Use LEFT JOIN from Teams.
• Scoring the wrong side. Comparing goals without binding to the team's role (host vs guest) miscredits points and can double-count. Each WHEN for a win must include the team_id = …_team guard.
• Draw branch ordering. The bare WHEN host_goals = guest_goals THEN 1 only works because it sits after the two win branches and a non-participant row is already NULL there. Put it first and you'd award draws before checking wins — for a draw that's fine, but the dependency on order is a real footgun if you later edit the branches.
• UNION vs UNION ALL. Using UNION in the split version deduplicates identical score rows and undercounts. Always UNION ALL when summing.
• OR-join cost at scale. On large Matches, the OR predicate blocks index seeks; check the plan and prefer the UNION ALL form if you see a full scan or expensive join.

Takeaways

• When one fact row credits two parties, fan it out (OR-join or UNION ALL) then collapse with SUM(CASE) — one row in, two scored rows, summed per party.
• Bind every conditional credit to the row's actual role; a comparison without the role guard double-counts or miscredits.
• LEFT JOIN from the dimension you must fully enumerate (Teams) keeps zero-activity members; NULL comparisons fall through CASE to 0.
• OR in a join is a planner trap — the UNION ALL rewrite is equivalent, index-friendly, and usually faster, but must be ALL to avoid dropping duplicate scores.

Problem: LeetCode 1212 “Team Scores in Football Tournament”. Mechanics of conditional aggregation, the OR-join planner cost, and the UNION ALL rewrite cross-checked against the PostgreSQL documentation (join methods, UNION/UNION ALL set semantics) and standard SQL CASE/NULL three-valued-logic behavior. Re-authored and deepened for this guide: the original Step-1 intermediate table contained a fabricated phantom duplicate row for team 20 that the OR-join cannot produce; replaced with a fully self-consistent five-team / five-match dataset, an end-to-end traced join-and-aggregate, a fan-out diagram, the ‘why the naive double-count is wrong’ note, and the UNION ALL alternative with its cost rationale.