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

Premier League Predictor: FastAPI & PostgreSQL

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

Chapter 6 scored predictions against real results. This chapter is a genuinely separate table: the actual Premier League standings — played, won, drawn, lost, goal difference, points — built entirely from the real results those fixtures now carry, with no reference to anyone's predictions at all.

Two Roles, One Team: Why This Isn't a Simple GROUP BY

A team's own record depends on fixtures where it appears as either home_team_id or away_team_id — two structurally different columns holding the same underlying fact ("this team played"). A plain GROUP BY home_team_id would only ever capture half of every team's real season. The fix is to normalize both roles into one shared shape first, with a UNION ALL, before aggregating anything:

WITH team_fixtures AS (
    SELECT home_team_id AS team_id, home_score AS goals_for, away_score AS goals_against
    FROM fixtures
    JOIN gameweeks ON gameweeks.id = fixtures.gameweek_id
    WHERE gameweeks.season_id = :season_id AND fixtures.status = 'played'

    UNION ALL

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

Every played fixture contributes exactly two rows to team_fixtures — one from each side's own perspective, with goals_for/goals_against already flipped correctly for whichever team the row represents. From here, a single, ordinary GROUP BY team_id sees a team's entire season, home and away combined.

Starting From SeasonTeam, Not From Fixtures

A team with zero played fixtures — the very start of a season, or simply a gameweek not yet reached — should still appear in the table, with a real, honest zero-played row, not vanish because it has no team_fixtures rows to aggregate. Building the table by starting from fixtures and working backward to teams would silently drop exactly those teams. Starting from season_teams instead — Chapter 2's own real list of the 20 competing teams — and LEFT JOIN-ing the fixture data onto it keeps every team present regardless of how many games it's actually played:

# routers/tables.py
from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy import text
from sqlalchemy.orm import Session
from database import get_db
import models, schemas

router = APIRouter(prefix="/api", tags=["tables"])

LEAGUE_TABLE_QUERY = text("""
    WITH team_fixtures AS (
        SELECT home_team_id AS team_id, home_score AS goals_for, away_score AS goals_against
        FROM fixtures
        JOIN gameweeks ON gameweeks.id = fixtures.gameweek_id
        WHERE gameweeks.season_id = :season_id AND fixtures.status = 'played'
        UNION ALL
        SELECT away_team_id AS team_id, away_score AS goals_for, home_score AS goals_against
        FROM fixtures
        JOIN gameweeks ON gameweeks.id = fixtures.gameweek_id
        WHERE gameweeks.season_id = :season_id AND fixtures.status = 'played'
    )
    SELECT
        teams.id AS team_id,
        teams.name,
        teams.short_name,
        COUNT(team_fixtures.team_id) AS played,
        COALESCE(SUM(CASE WHEN team_fixtures.goals_for > team_fixtures.goals_against THEN 1 ELSE 0 END), 0) AS won,
        COALESCE(SUM(CASE WHEN team_fixtures.goals_for = team_fixtures.goals_against THEN 1 ELSE 0 END), 0) AS drawn,
        COALESCE(SUM(CASE WHEN team_fixtures.goals_for < team_fixtures.goals_against THEN 1 ELSE 0 END), 0) AS lost,
        COALESCE(SUM(team_fixtures.goals_for), 0) AS goals_for,
        COALESCE(SUM(team_fixtures.goals_against), 0) AS goals_against,
        COALESCE(SUM(team_fixtures.goals_for), 0) - COALESCE(SUM(team_fixtures.goals_against), 0) AS goal_difference,
        COALESCE(SUM(CASE
            WHEN team_fixtures.goals_for > team_fixtures.goals_against THEN 3
            WHEN team_fixtures.goals_for = team_fixtures.goals_against THEN 1
            ELSE 0
        END), 0) AS points
    FROM season_teams
    JOIN teams ON teams.id = season_teams.team_id
    LEFT JOIN team_fixtures ON team_fixtures.team_id = teams.id
    WHERE season_teams.season_id = :season_id
    GROUP BY teams.id, teams.name, teams.short_name
    ORDER BY points DESC, goal_difference DESC, goals_for DESC, teams.name ASC
""")


@router.get("/seasons/{season_id}/table", response_model=list[schemas.LeagueTableRow])
def get_league_table(season_id: int, db: Session = Depends(get_db)):
    season = db.get(models.Season, season_id)
    if not season:
        raise HTTPException(status_code=404, detail="Season not found")

    rows = db.execute(LEAGUE_TABLE_QUERY, {"season_id": season_id}).mappings().all()
    return [schemas.LeagueTableRow(**dict(row)) for row in rows]

# schemas.py (additions)
class LeagueTableRow(BaseModel):
    team_id: int
    name: str
    short_name: str
    played: int
    won: int
    drawn: int
    lost: int
    goals_for: int
    goals_against: int
    goal_difference: int
    points: int

The same LEFT JOIN reasoning as Chapter 3, applied to a whole table this time

Chapter 3 already made this exact call once, at smaller scale: GET /api/seasons/{id}/teams joins from Team outward through SeasonTeam, not the other way around, precisely so a team's own existence never depends on something else (a fixture, a prediction) having happened yet. This query is the same principle at the scale of an entire table — season_teams is the real source of truth for "which 20 teams get a row," and LEFT JOIN lets the fixture data fill in around that membership rather than define it.

Why raw SQL here, when every earlier chapter used the ORM

Every route before this one mapped cleanly onto SQLAlchemy's own query builder — filters, joins, counts. A UNION ALL feeding a multi-branch CASE-based aggregate genuinely doesn't map as cleanly; forcing it through the ORM's query API would produce something harder to read than the SQL it's generating underneath. text() with bound parameters (:season_id, never raw string interpolation — the same discipline as every parameterized query so far in this course, not a special exception for raw SQL) is the more honest choice for a query this genuinely relational, and .mappings() returns each row as a real dict-like object instead of a positional tuple, which is what lets schemas.LeagueTableRow(**dict(row)) unpack it directly by column name.

Real Premier League tiebreakers go further than this table does

The actual Premier League breaks a tie on points and goal difference using head-to-head results between the tied teams, then head-to-head goal difference, before finally reaching for fair play record. This table stops at points → goal difference → goals for → team name — a genuine, deliberate simplification for a personal prediction tracker, not an oversight. A real tie beyond goals for would be ordered alphabetically here, which won't always match the actual official table in a genuinely close season.

Rendering the Table

// static/table.js
async function loadLeagueTable(seasonId) {
  const res = await fetch(`/api/seasons/${seasonId}/table`);
  const rows = await res.json();

  const body = document.getElementById('table-body');
  body.innerHTML = '';
  rows.forEach((row, index) => {
    const tr = document.createElement('tr');
    tr.innerHTML = `
      <td>${index + 1}</td>
      <td>${row.short_name}</td>
      <td>${row.played}</td>
      <td>${row.won}</td>
      <td>${row.drawn}</td>
      <td>${row.lost}</td>
      <td>${row.goal_difference}</td>
      <td>${row.points}</td>
    `;
    body.appendChild(tr);
  });
}

The table's own rank is just the row's own position in the already-sorted response — index + 1 — since ORDER BY points DESC, goal_difference DESC, goals_for DESC, teams.name ASC in the SQL query already did the real work; the frontend never re-sorts anything.

Where This Course Is Headed

A second table, built on a genuinely different question — not "how good is each team," but "how good is each predictor" — aggregating every source's own points_awarded from Chapter 6 across the season, including the guest-averaging-by-fixture that chapter resolved (Chapter 8); promotion and relegation, which will remove and add real rows in season_teams — exactly the table this query already reads from (Chapter 9); and a real gameweek/season selector tying every chapter's own routes together in one interface (Chapter 10).

Hands-On Exercises

Exercise 1

Explain why team_fixtures is built with a UNION ALL rather than a single SELECT with an OR condition on home_team_id/away_team_id, and what would go wrong with a team's own goals_for/goals_against totals if the UNION ALL's two branches didn't flip which score column is "for" and which is "against."

📄 View solution

Exercise 2

Explain what would happen to a newly promoted team's own row in the league table, at the very start of a season before it has played any fixtures, if the query started from fixtures instead of from season_teams.

📄 View solution

Exercise 3

Enter real results for at least three fixtures in a real season (some involving the same team as both home and away across different gameweeks), call GET /api/seasons/{id}/table, and confirm by hand that at least one team's played/won/drawn/lost/points figures correctly combine both its home and away results.

📄 View solution

Chapter 7 Quick Reference

UNION ALL — normalizes home and away fixture rows into one shared team_id/goals_for/goals_against shape before any aggregation happens
Starts from season_teams — a team with zero played fixtures still gets a real, honest zero-played row, echoing Chapter 3's own LEFT JOIN reasoning
Real sort order — points DESC, goal_difference DESC, goals_for DESC, team name ASC
Raw SQL via text() — a deliberate choice for this one genuinely complex aggregate, still using bound parameters, not string interpolation
Real limit — no head-to-head or fair-play tiebreakers; ties beyond goals for resolve alphabetically
Next chapter: The prediction league table — ranking predictor performance, not team performance