Every pair once: head-to-head records

Pairs come up everywhere: products bought together, people who share a meeting, teams that have played each other. The puzzle is listing each pair once: Chennai v Leeds and Leeds v Chennai are the same pair.

The test case

Every pair of teams, each pair once (alphabetical), with the matches they played against each other over both seasons and how many each side won.

Expected result

Fifteen pairs, because six teams make 6 × 5 / 2 of them. Chennai and Leeds met three times: Leeds won two (one of them the 2026 final) and the third was the tie. Count the columns: 0 + 2 is 2, not 3.

Way 1: LEAST and GREATEST

select a.team_code as team_a, b.team_code as team_b,
       count(*) as played,
       sum(case when m.winner_id = a.team_id then 1 else 0 end) as a_won,
       sum(case when m.winner_id = b.team_id then 1 else 0 end) as b_won
from   cr_matches m
join   cr_teams h on h.team_id = m.home_team_id
join   cr_teams w on w.team_id = m.away_team_id
join   cr_teams a on a.team_code = least(h.team_code, w.team_code)
join   cr_teams b on b.team_code = greatest(h.team_code, w.team_code)
group  by a.team_code, b.team_code
order  by a.team_code, b.team_code;

LEAST and GREATEST put each match's two teams in a fixed order, so home-and-away and away-and-home fall into the same group.

Way 2: pair the teams first, then find their matches

select a.team_code as team_a, b.team_code as team_b,
       count(m.match_id) as played,
       count(case when m.winner_id = a.team_id then 1 end) as a_won,
       count(case when m.winner_id = b.team_id then 1 end) as b_won
from   cr_teams a
join   cr_teams b on a.team_code < b.team_code
left   join cr_matches m on (m.home_team_id = a.team_id and m.away_team_id = b.team_id)
                         or (m.home_team_id = b.team_id and m.away_team_id = a.team_id)
group  by a.team_code, b.team_code
order  by a.team_code, b.team_code;

a.team_code < b.team_code is the classic "each pair once" join: <= would pair a team with itself, <> would list every pair twice. Starting from the pairs also shows pairs that never met (with 0), which Way 1 can't.

19c and 26ai

The same on both.

Gotchas

• < vs <> in a self-join is the difference between 15 rows and 30.
• N teams make N × (N − 1) / 2 pairs. Fine for 6; for 100,000 customers it's five billion rows. Pair only what you need (customers in the same basket, not all customers).