The Real League Table: Points, Goal Difference, Won/Drawn/Lost

Premier League Predictor: Astro

Chapter 7 · The Real League Table: Points, Goal Difference, Won/Drawn/Lost

Every fixture entered via Chapter 6 now carries a real home_score/away_score once played. This chapter turns those results into the table anyone actually recognizes as "the league table" — points, goal difference, won/drawn/lost — built entirely from real results, with no prediction data involved at all. That's a genuinely separate thing from the prediction league table Chapter 8 builds next, which ranks how well the user, expert, guests, and AI predicted, not how the real teams actually performed.

The Real Problem: A Team Is Sometimes Home, Sometimes Away

fixtures stores exactly one row per match, with home_team_id/home_score on one side and away_team_id/away_score on the other. But a league table needs a row per team, not per fixture — and a given team's own goals-for and goals-against sit in different columns depending on whether that team happened to be playing at home or away in a particular fixture. A single fixture row can't be read directly as "one team's result"; it has to be looked at twice, once from each side.

Normalizing Home and Away Rows With UNION ALL

The real fix is to turn every fixture into two rows — one from the home team's own perspective, one from the away team's — each carrying just team_id, goals_for, and goals_against in the same shape regardless of which side that team was actually on. SQLite's own UNION ALL does exactly this, and a WITH clause names the combined result so the rest of the query can treat it as one ordinary table:

-- The normalizing CTE — every played fixture becomes two rows, one per side
WITH team_fixture_stats AS (
  SELECT
    f.home_team_id AS team_id,
    f.home_score AS goals_for,
    f.away_score AS goals_against
  FROM fixtures f
  JOIN gameweeks g ON g.id = f.gameweek_id
  WHERE g.season_id = ? AND f.status = 'played'

  UNION ALL

  SELECT
    f.away_team_id AS team_id,
    f.away_score AS goals_for,
    f.home_score AS goals_against
  FROM fixtures f
  JOIN gameweeks g ON g.id = f.gameweek_id
  WHERE g.season_id = ? AND f.status = 'played'
)

Once team_fixture_stats exists, every row in it already answers "how did this one team do in this one fixture," in the same three columns no matter which side of the original fixture it came from. Aggregating it per team from here is ordinary GROUP BY arithmetic.

Starting From season_teams, Not From Fixtures

If the outer query started from team_fixture_stats and grouped by team_id, a team with genuinely zero played fixtures — the very first gameweek of a season, before anything has kicked off — would simply be absent from the result entirely, since there'd be no rows for it to group. A real league table shouldn't omit a team that hasn't played yet; it should show that team with 0 played, 0 points, sitting at the bottom alongside everyone else who also hasn't played. The fix is the same one Chapter 3's own GET /api/seasons/{id}/teams route already leaned on: start from membership — season_teams — and LEFT JOIN the stats onto it, rather than starting from the stats and hoping every team happens to already be represented in them.

-- src/lib/leagueTable.ts
import { db } from './db';

export interface LeagueTableRow {
  team_id: number;
  name: string;
  short_name: string;
  played: number;
  won: number;
  drawn: number;
  lost: number;
  goals_for: number;
  goals_against: number;
  goal_difference: number;
  points: number;
}

const LEAGUE_TABLE_SQL = `
  WITH team_fixture_stats AS (
    SELECT f.home_team_id AS team_id, f.home_score AS goals_for, f.away_score AS goals_against
    FROM fixtures f
    JOIN gameweeks g ON g.id = f.gameweek_id
    WHERE g.season_id = ? AND f.status = 'played'

    UNION ALL

    SELECT f.away_team_id AS team_id, f.away_score AS goals_for, f.home_score AS goals_against
    FROM fixtures f
    JOIN gameweeks g ON g.id = f.gameweek_id
    WHERE g.season_id = ? AND f.status = 'played'
  )
  SELECT
    t.id AS team_id,
    t.name,
    t.short_name,
    COUNT(tfs.team_id) AS played,
    COALESCE(SUM(CASE WHEN tfs.goals_for > tfs.goals_against THEN 1 ELSE 0 END), 0) AS won,
    COALESCE(SUM(CASE WHEN tfs.goals_for = tfs.goals_against THEN 1 ELSE 0 END), 0) AS drawn,
    COALESCE(SUM(CASE WHEN tfs.goals_for < tfs.goals_against THEN 1 ELSE 0 END), 0) AS lost,
    COALESCE(SUM(tfs.goals_for), 0) AS goals_for,
    COALESCE(SUM(tfs.goals_against), 0) AS goals_against,
    COALESCE(SUM(tfs.goals_for - tfs.goals_against), 0) AS goal_difference,
    COALESCE(SUM(
      CASE
        WHEN tfs.goals_for > tfs.goals_against THEN 3
        WHEN tfs.goals_for = tfs.goals_against THEN 1
        ELSE 0
      END
    ), 0) AS points
  FROM season_teams st
  JOIN teams t ON t.id = st.team_id
  LEFT JOIN team_fixture_stats tfs ON tfs.team_id = t.id
  WHERE st.season_id = ?
  GROUP BY t.id, t.name, t.short_name
  ORDER BY points DESC, goal_difference DESC, goals_for DESC
`;

export function getLeagueTable(seasonId: number): LeagueTableRow[] {
  // seasonId is bound three times — twice inside the CTE's own UNION ALL, once in the outer WHERE
  return db.prepare(LEAGUE_TABLE_SQL).all(seasonId, seasonId, seasonId) as LeagueTableRow[];
}

The same seasonId parameter, bound three separate times

? placeholders in better-sqlite3 are positional, not named — each one is a genuinely separate slot that needs its own bound value, even when the actual value happens to be identical every time. This query has three ? placeholders (two inside the CTE's two WHERE g.season_id = ? clauses, one in the outer WHERE st.season_id = ?), so .all(seasonId, seasonId, seasonId) passes the same number three times rather than once. Passing it only once, or forgetting one of the three, throws a real RangeError: too few parameter values were provided the moment the statement is executed.

A Real Consequence of Never Having an ORM Here

The Django & MySQL sibling course found a genuinely severe bug attempting this exact same aggregation: annotating Sum() across two separate reverse relations — home_fixtures and away_fixtures — in a single Django ORM call silently cross-joins them, inflating a true 14 goals into a wrong 56 in that chapter's own worked example. The fix there was to drop to Team.objects.raw() and hand-write the identical UNION ALL shape built above.

This course was never at risk of that bug in the first place

This stack has no ORM query-generation layer sitting between the code above and the SQL that actually runs — the UNION ALL/WITH query is the code, typed out by hand, string for string. The Django bug happened because its ORM tried to combine two separate annotated aggregates automatically and generated an unintended cross join while doing it; there's no equivalent automatic-join-generation step here that could go wrong the same way, because nothing here is generated at all. This isn't "SQLite is smarter than Django's ORM" — it's that a hand-written query has no automatic translation step standing between the intent and the SQL that could introduce a bug neither the intent nor the SQL itself contains.

A Real, Verified SQLite Version Check

Chapter 5 verified directly against SQLite's own documentation that partial indexes have been supported since SQLite 3.8.0, released in August 2013. The WITH clause this chapter's own query depends on is a genuinely close neighbor in SQLite's real release history:

WITH clause support: SQLite 3.8.3, released 2014-02-03

Checked directly against SQLite's own official release log for version 3.8.3: its first listed feature addition reads "Added support for common table expressions and the WITH clause." That release landed about six months after 3.8.0's own partial-index support — both genuinely old, stable features by any modern measure, not something requiring an unusually recent SQLite build to rely on.

The Real League Table API Route

// src/pages/api/seasons/[seasonId]/table.ts
import type { APIRoute } from 'astro';
import { db } from '../../../../lib/db';
import { json } from '../../../../lib/http';
import { getLeagueTable } from '../../../../lib/leagueTable';

export const GET: APIRoute = async ({ params }) => {
  const seasonId = Number(params.seasonId);

  const season = db.prepare('SELECT * FROM seasons WHERE id = ?').get(seasonId);
  if (!season) {
    return json({ error: 'Season not found' }, 404);
  }

  return json(getLeagueTable(seasonId));
};

Splitting the query itself into src/lib/leagueTable.ts, separate from the route file that calls it, is deliberate: Chapter 9's own promotion/relegation logic needs the identical league table — specifically to find the bottom three teams — and importing getLeagueTable() there is simpler and less error-prone than either duplicating the whole query a second time or making one API route call another one internally.

A Worked Example

Three played fixtures, one unplayed, across a four-team mini-season: Team A beat Team B 3-1, Team C drew Team D 1-1, and Team A beat Team D 2-0. Team B and Team C haven't yet played each other.

Team	P	W	D	L	GF	GA	GD	Pts
Team A	2	2	0	0	5	1	+4	6
Team C	1	0	1	0	1	1	0	1
Team D	2	0	1	1	1	3	-2	1
Team B	1	0	0	1	1	3	-2	0

Team C and Team D are genuinely tied on points, and this query's own ORDER BY resolves it purely by goal difference (0 beats -2) — Team D's own single win over nobody doesn't come into it, since goal difference is the very next tiebreaker after points, ahead of goals scored.

Real tiebreakers this query doesn't implement

A genuine Premier League table also uses head-to-head results as a real tiebreaker in some situations, and has historically applied a fair-play points deduction in others. Neither is implemented here — ORDER BY points DESC, goal_difference DESC, goals_for DESC covers the three criteria that decide the overwhelming majority of real placings, and is an honest, deliberately incomplete stand-in for the full, more intricate real rule set rather than a claim that every possible tie is resolved exactly the way the real Premier League would resolve it.

Where This Course Is Headed

The prediction league table — a genuinely different aggregation, over points_awarded rather than real results, with Chapter 6's own guest-averaging principle finally put to use across a real season (Chapter 8); promotion and relegation, reusing getLeagueTable()'s own output directly to find the real bottom three (Chapter 9); Astro Islands and interactivity (Chapter 10); deployment (Chapter 11); and the capstone (Chapter 12).

Hands-On Exercises

Exercise 1

Explain, in your own words, why a single fixtures row can't be read directly as one team's own league-table result, and describe exactly what the UNION ALL inside team_fixture_stats does to fix that.

📄 View solution

Exercise 2

Explain why the league-table query starts from season_teams with a LEFT JOIN onto team_fixture_stats, rather than starting from team_fixture_stats itself and grouping by team_id, and describe concretely what would go wrong for a team with zero played fixtures under the second approach.

📄 View solution

Exercise 3

Set up the four-team, three-fixture mini-season from this chapter's own worked example against a real database, call GET /api/seasons/{seasonId}/table, and confirm the returned table matches the worked-example rows exactly — including the Team C vs. Team D tie being broken by goal difference rather than points.

📄 View solution

Chapter 7 Quick Reference

The real problem — a team's own goals-for/against live in different columns depending on home vs. away, so one fixtures row can't be read as one team's result directly
UNION ALL normalization — team_fixture_stats turns every played fixture into two rows, one per side, in the same team_id/goals_for/goals_against shape
Start from season_teams, LEFT JOIN the stats — echoing Chapter 3's own membership-first reasoning, so a team with zero played fixtures still shows a real 0-played row instead of being omitted
No ORM cross-join bug possible here — a direct contrast with the Django & MySQL sibling's own real Sum()-over-two-reverse-relations bug (14 goals inflated to 56); there's no automatic join-generation layer in this stack that could make the same mistake
WITH clause support: SQLite 3.8.3 (2014-02-03) — verified directly against SQLite's own release log, landing about six months after Chapter 5's own verified 3.8.0 partial-index support
seasonId bound three times — twice inside the CTE's own UNION ALL, once in the outer WHERE; better-sqlite3's ? placeholders are positional, not named
getLeagueTable() lives in its own module — reused directly by Chapter 9's promotion/relegation logic, not duplicated
ORDER BY points DESC, goal_difference DESC, goals_for DESC — real head-to-head and fair-play tiebreakers are honestly not implemented
Next chapter: The prediction league table — aggregating predictor performance across a full season