The Prediction League Table: Aggregating Predictor Performance Across the Season

Premier League Predictor: Django & MySQL

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 already scored.

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

WITH season_predictions AS (
    SELECT p.source, p.fixture_id, p.points_awarded
    FROM predictor_prediction p
    JOIN predictor_fixture f ON f.id = p.fixture_id
    JOIN predictor_gameweek gw ON gw.id = f.gameweek_id
    WHERE gw.season_id = %s AND p.points_awarded IS NOT NULL
)

p.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. MySQL 8.0's own WITH clause support — a real requirement Chapter 2 already anchored this course to (Django 4.2+ requires MySQL 8 outright) — is what makes writing this as a genuine named CTE possible at all, rather than nesting it as an anonymous subquery.

Three Straightforward Sources, One That Needs Two Passes

User, expert, and AI each contribute exactly one Prediction row per fixture — Chapter 5's own unique_key generated column 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.

# predictor/views.py (additions)
from django.db import connection

PREDICTION_LEAGUE_SQL = """
    WITH season_predictions AS (
        SELECT p.source, p.fixture_id, p.points_awarded
        FROM predictor_prediction p
        JOIN predictor_fixture f ON f.id = p.fixture_id
        JOIN predictor_gameweek gw ON gw.id = f.gameweek_id
        WHERE gw.season_id = %s AND p.points_awarded IS NOT NULL
    ),
    non_guest_totals AS (
        SELECT
            source,
            COUNT(*) AS predictions_scored,
            SUM(points_awarded) 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) AS total_points,
            NULL AS correct_scores,
            NULL AS correct_results
        FROM guest_per_fixture
    )
    SELECT * FROM non_guest_totals
    UNION ALL
    SELECT * FROM guest_totals
    ORDER BY total_points DESC
"""


def dictfetchall(cursor):
    # Django's own documented pattern for turning a raw cursor into plain dicts
    columns = [col[0] for col in cursor.description]
    return [dict(zip(columns, row)) for row in cursor.fetchall()]


def prediction_table(request, season_id):
    with connection.cursor() as cursor:
        cursor.execute(PREDICTION_LEAGUE_SQL, [season_id])
        rows = dictfetchall(cursor)
    return JsonResponse(rows, safe=False)

connection.cursor(), not Model.objects.raw() — because these rows aren't rows of any model

Chapter 7's own raw query used Team.objects.raw() because every row it returned genuinely was a real Team instance, with a real primary key column present in the result. This query's rows have no such anchor — source is a plain string ('user', 'expert', 'guest', 'ai'), not a foreign key to anything, and there's no model anywhere in this app whose primary key a "prediction league" row could map onto. .raw() specifically requires a model class and its pk column; without one, Django's own lower-level tool is connection.cursor() — a genuine, direct DB-API cursor, with dictfetchall() (copied almost verbatim from Django's own documented example) turning its plain tuple rows into ordinary dicts using cursor.description for the column names.

A real MySQL-vs-PostgreSQL difference — no explicit cast needed here

plpredict-fastapi1's own PostgreSQL sibling needs an explicitly-typed NULL::int for its guest row's correct_scores/correct_results columns, since PostgreSQL requires a UNION ALL's corresponding columns to resolve to a matching type and won't always infer one from a bare, untyped NULL on its own. MySQL is more lenient here: a plain NULL AS correct_scores in guest_totals above works with no cast at all — MySQL resolves each result column's real type from whichever UNION branch actually supplies a concrete value for it. The underlying reason for including the column at all is identical between both databases (explained below); only the SQL syntax needed to express "this column genuinely doesn't apply here" differs.

"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/predictor/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(`/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>${Number(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. Number(row.total_points) is a small, real MySQL-driver detail: MySQL's own DECIMAL aggregate results (what SUM()/AVG() over integer columns actually return) frequently arrive at the Python driver as decimal strings rather than native floats, so JsonResponse serializes them as JSON strings — wrapping with Number() on the frontend is what makes .toFixed(1) safe to call regardless of which shape came back.

Where This Course Is Headed

Promotion and relegation between seasons — real changes to SeasonTeam, 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 for the guest row instead of being computed the same way as the other three sources, and why MySQL doesn't need an explicit type cast on that NULL the way the PostgreSQL sibling course does.

📄 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 prediction_table for that season 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 unique_key
Guest — averaged per fixture first (guest_per_fixture), then summed across the season — resolving Chapter 6's own deferred question
connection.cursor() + dictfetchall() — used instead of .raw() since these rows aren't real model instances with a primary key
MySQL vs. PostgreSQL — a bare NULL in the UNION ALL works fine here; no ::int-style cast needed, unlike the sibling course
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 SeasonTeam, the table both league tables read from