The Prediction League Table: Aggregating Predictor Performance Across the Season

Premier League Predictor: FastAPI & PostgreSQL

Chapter 8 · The Prediction League Table: Aggregating Predictor Performance Across the Season

Chapter 7 answered "how good is each team." This chapter answers a genuinely different question: "how good is each predictor" — the user, the BBC's expert, that season's guests, and the BBC's AI — aggregated across every fixture Chapter 6 has scored.

Scoping Predictions to a Season, and Only Counting What's Actually Scored

WITH season_predictions AS (
    SELECT
        predictions.source,
        predictions.fixture_id,
        predictions.points_awarded
    FROM predictions
    JOIN fixtures ON fixtures.id = predictions.fixture_id
    JOIN gameweeks ON gameweeks.id = fixtures.gameweek_id
    WHERE gameweeks.season_id = :season_id
      AND predictions.points_awarded IS NOT NULL
)

predictions.points_awarded IS NOT NULL is the real gate: a prediction on a fixture that hasn't kicked off yet still has points_awarded = NULL (Chapter 6), so it never contributes to anyone's season total until a real result actually exists for it.

Three Straightforward Sources, One That Needs Two Passes

User, expert, and AI each contribute exactly one Prediction row per fixture (Chapter 5's own partial unique index guarantees it) — a single SUM(points_awarded) is their entire season total. Guest is different: Chapter 6 resolved that a fixture's own "guest" contribution is the average of however many individual guests predicted it, not a raw sum of every guest row. That means guest needs its own points averaged per fixture first, before those per-fixture averages get summed across the season — genuinely two passes of aggregation, not one.

# routers/tables.py (additions)
PREDICTION_LEAGUE_QUERY = text("""
    WITH season_predictions AS (
        SELECT predictions.source, predictions.fixture_id, predictions.points_awarded
        FROM predictions
        JOIN fixtures ON fixtures.id = predictions.fixture_id
        JOIN gameweeks ON gameweeks.id = fixtures.gameweek_id
        WHERE gameweeks.season_id = :season_id AND predictions.points_awarded IS NOT NULL
    ),
    non_guest_totals AS (
        SELECT
            source,
            COUNT(*) AS predictions_scored,
            SUM(points_awarded)::float AS total_points,
            SUM(CASE WHEN points_awarded = 40 THEN 1 ELSE 0 END) AS correct_scores,
            SUM(CASE WHEN points_awarded = 10 THEN 1 ELSE 0 END) AS correct_results
        FROM season_predictions
        WHERE source != 'guest'
        GROUP BY source
    ),
    guest_per_fixture AS (
        SELECT fixture_id, AVG(points_awarded) AS avg_points
        FROM season_predictions
        WHERE source = 'guest'
        GROUP BY fixture_id
    ),
    guest_totals AS (
        SELECT
            'guest' AS source,
            COUNT(*) AS predictions_scored,
            SUM(avg_points)::float AS total_points,
            NULL::int AS correct_scores,
            NULL::int AS correct_results
        FROM guest_per_fixture
    )
    SELECT * FROM non_guest_totals
    UNION ALL
    SELECT * FROM guest_totals
    ORDER BY total_points DESC
""")


@router.get("/seasons/{season_id}/prediction-table", response_model=list[schemas.PredictionLeagueRow])
def get_prediction_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(PREDICTION_LEAGUE_QUERY, {"season_id": season_id}).mappings().all()
    return [schemas.PredictionLeagueRow(**dict(row)) for row in rows]

# schemas.py (additions)
class PredictionLeagueRow(BaseModel):
    source: models.PredictionSource
    predictions_scored: int
    total_points: float
    correct_scores: Optional[int]
    correct_results: Optional[int]

NULL::int, not just NULL — a real UNION ALL typing requirement

UNION ALL requires every corresponding column across its branches to share a compatible type. non_guest_totals.correct_scores is a real integer count; guest_totals has no meaningful equivalent to put there (explained below), but a bare, untyped NULL gives PostgreSQL nothing to reconcile against the integer column from the other branch. NULL::int is an explicitly-typed null — genuinely absent, but typed as an integer specifically so the two branches line up.

"Correct scores" and "correct results" don't translate cleanly onto an averaged figure

For user, expert, and AI, "correct scores" and "correct results" are meaningful counts — each source has exactly one real prediction per fixture, so counting how many of those hit 40 or 10 points is a genuine, well-defined statistic. Guest doesn't have that: the season-total figure being aggregated is already an average of however many individual guest predictions existed for a given fixture, and an average like 16.67 was never a 40-or-10-or-0 outcome to begin with — it's not "sometimes a correct score, sometimes not," it's a number that was never in that category at all. Rather than inventing an arbitrary, misleading way to force a correct-score/correct-result count onto the guest row, this query leaves both columns honestly null for it.

predictions_scored means something different for the guest row

For user/expert/AI, predictions_scored counts real, individual Prediction rows — one per fixture, always. For guest, it counts rows from guest_per_fixture — one per fixture that had at least one guest prediction, regardless of whether that fixture had one guest or five. A season with 30 played fixtures, all with exactly one guest each, and a season with 30 played fixtures, all with three guests each, would report the identical predictions_scored: 30 for guest — a deliberate consequence of scoring "the guest slot," not "every individual guest," matching Chapter 6's own resolution directly.

Rendering the Prediction Table

// static/prediction-table.js
const SOURCE_LABELS = {
  user: 'You',
  expert: 'BBC Expert',
  guest: 'Guest(s)',
  ai: 'BBC AI',
};

async function loadPredictionTable(seasonId) {
  const res = await fetch(`/api/seasons/${seasonId}/prediction-table`);
  const rows = await res.json();

  const body = document.getElementById('prediction-table-body');
  body.innerHTML = '';
  rows.forEach((row, index) => {
    const tr = document.createElement('tr');
    const breakdown = row.correct_scores === null
      ? '—'
      : `${row.correct_scores} exact / ${row.correct_results} result`;
    tr.innerHTML = `
      <td>${index + 1}</td>
      <td>${SOURCE_LABELS[row.source]}</td>
      <td>${row.predictions_scored}</td>
      <td>${breakdown}</td>
      <td>${row.total_points.toFixed(1)}</td>
    `;
    body.appendChild(tr);
  });
}

row.correct_scores === null is what lets the frontend show a plain "—" for the guest row's own breakdown column, rather than a misleading "0 exact / 0 result" that would look like guests never got anything right.

Where This Course Is Headed

Promotion and relegation between seasons — real changes to season_teams, the same table both this chapter's and Chapter 7's own queries read from (Chapter 9); and a real gameweek/season selector, tying every route built across this course into one working interface (Chapter 10).

Hands-On Exercises

Exercise 1

Explain why guest_totals needs its own separate CTE built on top of guest_per_fixture, rather than just adding a WHERE source = 'guest' branch alongside non_guest_totals using the same SUM(points_awarded) pattern.

📄 View solution

Exercise 2

Explain why correct_scores and correct_results are left as NULL::int for the guest row instead of being computed the same way as the other three sources, and why a bare NULL (without the ::int cast) wouldn't work in this specific query.

📄 View solution

Exercise 3

Record and score predictions from all four sources across at least two fixtures, where one fixture has two guests and the other has only one, then call GET /api/seasons/{id}/prediction-table and confirm the guest row's predictions_scored equals 2, not 3.

📄 View solution

Chapter 8 Quick Reference

A genuinely different table from Chapter 7 — ranks predictors, not teams, built from points_awarded (Chapter 6)
points_awarded IS NOT NULL — the real gate keeping unplayed fixtures out of anyone's season total
User/expert/AI — a plain SUM(points_awarded) per source, one prediction per fixture, guaranteed by Chapter 5's partial unique index
Guest — averaged per fixture first (guest_per_fixture), then summed across the season — resolving Chapter 6's own deferred question
NULL::int — an explicitly-typed null, required so UNION ALL's branches line up on column type
correct_scores/correct_results — meaningful for user/expert/AI, honestly left null for guest since an averaged figure was never a 40-or-10-or-0 outcome to begin with
Next chapter: Promotion & relegation — real changes to season_teams, the table both league tables read from