The Prediction League Table: Aggregating Predictor Performance Across the Season

Premier League Predictor: Astro

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

Chapter 7's real league table ranks the 20 actual teams by what they actually did on the pitch. This table asks a genuinely different question: across the whole season, which of the four prediction sources — user, expert, guest, AI — has actually been the best predictor? Every fixture already carries a real points_awarded value per prediction since Chapter 6; this chapter sums it correctly across an entire season.

Pass One: Averaging Guest Points Per Fixture

Chapter 6 established the real principle — a fixture with several guest predictions gets one comparable figure by averaging each guest's own points_awarded, never their raw scorelines. That averaging has to happen once per fixture, before anything gets summed across the season, or a fixture with three guests would otherwise be counted three separate times against a fixture with only one:

-- Pass 1: one averaged "guest" points figure per fixture
WITH guest_fixture_points AS (
  SELECT
    fixture_id,
    AVG(points_awarded) AS points_awarded
  FROM predictions
  WHERE source = 'guest' AND points_awarded IS NOT NULL
  GROUP BY fixture_id
)

points_awarded IS NOT NULL excludes any fixture that hasn't had a result entered yet — Chapter 6's own PATCH route only fills that column in once a fixture is marked 'played', so an unplayed fixture's guest predictions simply don't contribute to anyone's total yet, on either side of this table.

Pass Two: One Shared Shape for Every Source

User, expert, and AI predictions already sit one row per fixture in predictions — no averaging needed. A second UNION ALL puts those rows alongside the newly-averaged guest rows from Pass One, in the same (fixture_id, source, points_awarded) shape:

-- Pass 2: every source, one row per fixture, in the same shape
source_fixture_points AS (
  SELECT fixture_id, source, points_awarded
  FROM predictions
  WHERE source != 'guest' AND points_awarded IS NOT NULL

  UNION ALL

  SELECT fixture_id, 'guest' AS source, points_awarded
  FROM guest_fixture_points
)

No NULL-typing question to run into here at all

Both siblings' own equivalent queries had to carry each prediction's real predicted_home_score/predicted_away_score through their own UNION ALL, needing a placeholder NULL in whichever branch didn't have a real scoreline to offer for the averaged guest row — the FastAPI & PostgreSQL course needed an explicit NULL::int cast there, and the Django & MySQL course found MySQL infers the same bare NULL's type automatically. This query never runs into that question at all, for a much simpler reason than either database's own typing rules: because Chapter 6 already computed and stored a real points_awarded value on every prediction row, both branches of this UNION ALL select a genuine number — there's no column here that only has meaning for one branch and needs a placeholder in the other.

SQLite wouldn't have needed the cast even if a NULL were involved

Checked directly against SQLite's own documented compound-SELECT rules: "no affinity transformations are applied to any values when comparing rows as part of a compound SELECT." SQLite's own columns are dynamically typed to begin with — there's no fixed column type for a UNION's own result set the way PostgreSQL enforces one, so a bare, untyped NULL sitting in one branch next to a real integer in another would simply work, with nothing to cast in the first place. This isn't a case of SQLite being cleverer than either sibling database — it genuinely never asks the question those two answer in opposite ways.

Aggregating: Totals, and an Honestly Nulled Breakdown

The outer query sums points per source across the whole season, and separately counts how many of those points came from an exact score (40) versus a correct result (10) — but only for the three sources where that breakdown actually means something. A guest's own row is an average, and an average was never really 40, 10, or 0 to begin with, so counting how many "guest" rows equal exactly 40 would be a real number that answers a question nobody actually asked.

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

export interface PredictionTableRow {
  source: string;
  fixtures_scored: number;
  total_points: number;
  correct_scores: number | null;
  correct_results: number | null;
}

const PREDICTION_TABLE_SQL = `
  WITH guest_fixture_points AS (
    SELECT fixture_id, AVG(points_awarded) AS points_awarded
    FROM predictions
    WHERE source = 'guest' AND points_awarded IS NOT NULL
    GROUP BY fixture_id
  ),
  source_fixture_points AS (
    SELECT fixture_id, source, points_awarded
    FROM predictions
    WHERE source != 'guest' AND points_awarded IS NOT NULL

    UNION ALL

    SELECT fixture_id, 'guest' AS source, points_awarded
    FROM guest_fixture_points
  )
  SELECT
    sfp.source,
    COUNT(*) AS fixtures_scored,
    SUM(sfp.points_awarded) AS total_points,
    CASE WHEN sfp.source = 'guest' THEN NULL
         ELSE SUM(CASE WHEN sfp.points_awarded = 40 THEN 1 ELSE 0 END)
    END AS correct_scores,
    CASE WHEN sfp.source = 'guest' THEN NULL
         ELSE SUM(CASE WHEN sfp.points_awarded = 10 THEN 1 ELSE 0 END)
    END AS correct_results
  FROM source_fixture_points sfp
  JOIN fixtures f ON f.id = sfp.fixture_id
  JOIN gameweeks g ON g.id = f.gameweek_id
  WHERE g.season_id = ?
  GROUP BY sfp.source
  ORDER BY total_points DESC
`;

export function getPredictionTable(seasonId: number): PredictionTableRow[] {
  return db.prepare(PREDICTION_TABLE_SQL).all(seasonId) as PredictionTableRow[];
}

The inner SUM has to stay honest even for sources it's about to null out

CASE WHEN sfp.points_awarded = 40 THEN 1 ELSE 0 END is deliberately written to sum real 1s and 0s, not 1s and NULLs — that's what makes a genuine "zero correct scores this season" come back as a real 0 for user, expert, or AI, rather than being indistinguishable from a group with no rows at all. The outer CASE WHEN sfp.source = 'guest' THEN NULL is what actually does the nulling, and it's applied once, after the real count has already been computed correctly — not baked into the counting logic itself, where it would have wrongly turned every real "user got 0 correct scores" result into a meaningless NULL too.

A Single-parameter Query, Unlike Chapter 7's Own Three

seasonId is bound exactly once here, in the outer WHERE g.season_id = ? — a genuine contrast with Chapter 7's league-table query, which needed the identical value bound three separate times across its own UNION ALL. The difference is structural: Chapter 7's UNION ALL filters by season inside both of its own branches (home rows and away rows each need the same season filter applied independently), while this chapter's season filter is applied once, in the outer query, after both CTEs have already produced their rows — there's simply nowhere else in this particular query that season_id needs to be checked a second time.

A Real API Route

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

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(getPredictionTable(seasonId));
};

A Worked Example, Extending Chapter 6's Own Fixture 42

Fixture 42, scored back in Chapter 6: user 40, expert 0, both guests scored 10 (averaging to exactly 10), AI 10. A second fixture, 43, later the same season: user 0, expert 40, three guests scoring 40, 10, and 0 (averaging to 50 / 3 = 16.67 — the exact figure Chapter 6's own Exercise 2 already computed by hand), AI 10.

Source	Fixtures scored	Total points	Correct scores	Correct results
user	2	40	1	0
expert	2	40	1	0
guest	2	26.67	null	null
ai	2	20	0	2

user and expert genuinely tie on 40 total points, arrived at through opposite fixtures — user nailed fixture 42 and missed fixture 43 entirely, expert did the reverse. Both really did get exactly one correct score across the two fixtures, and the table shows that plainly rather than hiding it behind the tied total.

Ties have no tiebreaker in this query

ORDER BY total_points DESC is the query's only sort key. When two sources tie exactly, as user and expert do above, which one appears first in the returned array is whatever order SQLite happens to produce them in — not a documented, guaranteed ordering, and not something this chapter resolves with a second sort key the way Chapter 7's real league table resolves its own ties with goal difference. A real UI built on this route should treat a tie as a tie visually (e.g. showing both at "1st"), rather than implying one source is definitively ranked above the other.

Where This Course Is Headed

Promotion and relegation, reusing Chapter 7's own getLeagueTable() 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 why guest_fixture_points has to run as its own separate pass before the UNION ALL in source_fixture_points, rather than averaging guest points and unioning everything together in one single query.

📄 View solution

Exercise 2

Explain why correct_scores and correct_results are computed as a real SUM of 1s and 0s first, and only nulled out afterward by an outer CASE, rather than writing the null-for-guest logic directly into the inner CASE that does the counting.

📄 View solution

Exercise 3

Set up fixtures 42 and 43 from this chapter's own worked example against a real database, call GET /api/seasons/{seasonId}/prediction-table, and confirm the response matches the worked-example table exactly, including guest's own averaged total of 26.67 and null correct_scores/correct_results for guest specifically.

📄 View solution

Chapter 8 Quick Reference

Two passes, not one — guest_fixture_points averages guest points per fixture first, then source_fixture_points UNION ALLs every source into one shared (fixture_id, source, points_awarded) shape
No NULL-typing question here — unlike both sibling courses' own placeholder-NULL predicted-score columns, this query's two UNION ALL branches both select a real, already-computed points_awarded value
SQLite wouldn't have required a cast anyway — verified directly against SQLite's own compound-SELECT documentation: no affinity transformations are applied when comparing rows in a UNION, unlike PostgreSQL's own stricter typing
Honest correct_scores/correct_results — computed as a genuine SUM of 1s and 0s first, then nulled specifically for the guest source by an outer CASE, so a real "zero correct scores" for user/expert/ai is never confused with "this number doesn't apply"
One bound parameter, not three — season_id is filtered once, in the outer query, unlike Chapter 7's own three-times-bound league-table query
getPredictionTable() — GET /api/seasons/{id}/prediction-table, the direct counterpart to Chapter 7's getLeagueTable()
Real ties are just ties — ORDER BY total_points DESC has no second sort key; a tied pair's own returned order isn't guaranteed or documented
Next chapter: Promotion & relegation — reusing Chapter 7's own league table to find the real bottom three