Head-to-Head Analysis and Derby Rivalries

Head-to-head (H2H) records answer a specific question that overall league standings cannot: "When these two specific teams meet, who tends to win?" Some teams have psychological advantages over specific opponents that defy their overall league position. Derby matches between local rivals often follow their own rules entirely, with form and table position mattering less than usual.

This lesson builds a complete H2H analysis system — from basic win/loss/draw records to goal averages, recent form in derbies, and margin of victory patterns.

Understanding the Challenge

The main SQL challenge with H2H analysis is that the same match can appear from two perspectives. A match between Team A (home) and Team B (away) is:

• A home game for Team A
• An away game for Team B

When computing H2H between two specific teams, we want to capture the match from a consistent perspective — typically always from the "first" team's viewpoint. We also need to handle the fact that the same fixture can have Team A at home in some seasons and Team B at home in others.

Full H2H Record Between All Pairs

This query computes a complete matrix of head-to-head records across all team pairs that have played each other. The WHERE home_team_id < away_team_id filter avoids double-counting (it ensures each pair appears only once, in a consistent order).

WITH h2h AS (
    SELECT
        LEAST(home_team_id, away_team_id) AS team_a_id,
        GREATEST(home_team_id, away_team_id) AS team_b_id,
        home_team_id, away_team_id,
        home_goals, away_goals,
        match_date, season
    FROM matches WHERE competition = 'league'
),
h2h_summary AS (
    SELECT
        team_a_id, team_b_id,
        COUNT(*) AS total_meetings,
        SUM(CASE WHEN (home_team_id = team_a_id AND home_goals > away_goals)
                   OR (away_team_id = team_a_id AND away_goals > home_goals)
                 THEN 1 ELSE 0 END) AS team_a_wins,
        SUM(CASE WHEN home_goals = away_goals THEN 1 ELSE 0 END) AS draws,
        SUM(CASE WHEN (home_team_id = team_b_id AND home_goals > away_goals)
                   OR (away_team_id = team_b_id AND away_goals > home_goals)
                 THEN 1 ELSE 0 END) AS team_b_wins,
        ROUND(AVG(CASE WHEN home_team_id = team_a_id THEN home_goals ELSE away_goals END), 2) AS team_a_avg_goals,
        ROUND(AVG(CASE WHEN home_team_id = team_b_id THEN home_goals ELSE away_goals END), 2) AS team_b_avg_goals
    FROM h2h
    GROUP BY team_a_id, team_b_id
)
SELECT
    ta.name AS team_a, tb.name AS team_b,
    hs.total_meetings,
    hs.team_a_wins, hs.draws, hs.team_b_wins,
    hs.team_a_avg_goals, hs.team_b_avg_goals,
    ROUND(hs.team_a_wins::numeric / NULLIF(hs.total_meetings, 0) * 100, 1) AS team_a_win_pct
FROM h2h_summary hs
JOIN teams ta ON ta.team_id = hs.team_a_id
JOIN teams tb ON tb.team_id = hs.team_b_id
ORDER BY hs.total_meetings DESC;

What This Returns

Each row represents the complete historical record between one pair of teams. team_a_win_pct above 50% means Team A has historically dominated this fixture. A draws count that's high relative to total_meetings suggests this is a closely contested rivalry where neither side dominates.

The LEAST() / GREATEST() technique ensures that "Team A vs Team B" and "Team B vs Team A" are always counted as the same fixture pair, regardless of who was home.