League Table and Standings Calculations

Building a complete league standings table from raw match data is one of the most instructive SQL challenges in sports analytics. Real-world match data comes in one row per match — but a league table requires combining each team's performance as both home and away side, then aggregating across an entire season. This demands careful thinking about data reshaping, conditional aggregation, and ranking.

In this lesson, you will build a full standings table from scratch, add home/away splits, compute rolling form guides, and apply proper tiebreaker rules — all in SQL.

The League Point System

Before writing any SQL, understand the rules:

• Win: 3 points
• Draw: 1 point
• Loss: 0 points

Tiebreakers (in order when points are equal):

1. Goal difference (GD = goals scored minus goals conceded)
2. Goals scored
3. Head-to-head record

The trickiest part of this query is that each match produces results for two teams simultaneously. The home team gets home_goals goals for and away_goals goals against. The away team gets the reverse. We handle this with a UNION ALL that creates one row per team per match.

Building the Full League Table

The query works in three stages:

Stage 1 (all_results CTE): Flatten each match into two rows — one from the home team's perspective, one from the away team's perspective. This is the core technique that makes everything else possible. Without this step, you'd need separate home and away aggregations and a complex join.

Stage 2 (standings CTE): Aggregate per team per season. The CASE WHEN gf > ga THEN 1 ELSE 0 END logic works because after the UNION ALL, gf always means "goals this team scored" and ga always means "goals this team conceded" — regardless of whether they were home or away.

Stage 3 (final SELECT): Apply RANK() OVER to position teams by points, then by tiebreakers. The PARTITION BY season ensures each season gets its own separate ranking.

WITH all_results AS (
    SELECT home_team_id AS team_id, m.season,
        home_goals AS gf, away_goals AS ga,
        CASE WHEN home_goals > away_goals THEN 3
             WHEN home_goals = away_goals THEN 1 ELSE 0 END AS pts
    FROM matches m WHERE m.competition IN ('Premier League', 'La Liga')
    UNION ALL
    SELECT away_team_id, m.season,
        away_goals, home_goals,
        CASE WHEN away_goals > home_goals THEN 3
             WHEN away_goals = home_goals THEN 1 ELSE 0 END
    FROM matches m WHERE m.competition IN ('Premier League', 'La Liga')
),
standings AS (
    SELECT team_id, season,
        COUNT(*) AS p,
        SUM(CASE WHEN gf > ga THEN 1 ELSE 0 END) AS w,
        SUM(CASE WHEN gf = ga THEN 1 ELSE 0 END) AS d,
        SUM(CASE WHEN gf < ga THEN 1 ELSE 0 END) AS l,
        SUM(gf) AS gf, SUM(ga) AS ga,
        SUM(gf) - SUM(ga) AS gd,
        SUM(pts) AS pts
    FROM all_results GROUP BY team_id, season
)
SELECT
    RANK() OVER (PARTITION BY season ORDER BY pts DESC, gd DESC, gf DESC) AS pos,
    t.name, s.season,
    s.p, s.w, s.d, s.l, s.gf, s.ga, s.gd, s.pts
FROM standings s JOIN teams t ON t.team_id = s.team_id
ORDER BY s.season, pos;

What This Returns

Each row represents one team's full-season record. Columns:

When two teams have the same points, gd breaks the tie, then gf. This mirrors real football league rules.