Premier League Predictor: Django & MySQL — Chapter 8, Exercise 1
====================================================

TASK
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.

SOLUTION

non_guest_totals works because user, expert, and AI each contribute
exactly one Prediction row per fixture, guaranteed by Chapter 5's own
unique_key generated column. For those three sources, "sum every
points_awarded value for this source across the season" and "sum this
source's real per-fixture contribution across the season" are the
same operation, because each row already IS that source's one true
per-fixture contribution.

Guest breaks that assumption. A single fixture can have zero, one,
two, or more guest predictions, each its own separate Prediction row.
If guest were added to non_guest_totals with the same
SUM(points_awarded) pattern, that SUM would add up every individual
guest row directly — meaning a fixture with three guests would
contribute three times as much to the season total as a fixture with
only one guest, purely because more people happened to guess that
week. That's not what Chapter 6 established the guest figure to mean:
the guest "slot" contributes one averaged value per fixture, not one
raw value per guest.

To get that averaged-per-fixture value, the aggregation genuinely has
to happen in two separate steps. guest_per_fixture is the first pass:
it groups guest predictions by fixture_id and computes AVG(points_awarded)
for each fixture, producing exactly one row per fixture that had at
least one guest, regardless of how many guests it had. guest_totals is
the second pass: it takes those already-averaged, one-row-per-fixture
values and SUMs them across the whole season. A single-pass
SUM(points_awarded) WHERE source = 'guest' has no way to express "average
first, within each fixture, before summing across fixtures" — SQL's
own GROUP BY only supports one level of grouping per query, so the
per-fixture averaging step has to be its own separate CTE that the
season-level SUM then reads from.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains the specific assumption non_guest_totals relies on (one
row already equals one source's per-fixture contribution), shows
exactly why that assumption fails for guest (a variable number of
rows per fixture), and traces why a single SUM can't express
"average per fixture, then sum across fixtures" — only two separate,
sequential aggregation passes can.