Premier League Predictor: FastAPI & PostgreSQL — 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 real Prediction row per fixture — summing points_awarded
directly across all of a source's rows for the season is already the
correct season total, since there's nothing to average first.

Guest is structurally different, by Chapter 6's own deliberate design:
a single fixture can have multiple separate guest Prediction rows (one
per named guest), and the season total is supposed to be the SUM of
each fixture's own AVERAGED guest points, not the sum of every
individual guest row. If guest_totals just filtered
season_predictions WHERE source = 'guest' and summed points_awarded
directly, a fixture with three guests would contribute all three of
their raw points to the total, while a fixture with one guest would
only contribute one — silently weighting fixtures with more guest
predictors more heavily in the season total, which is exactly the
outcome Chapter 6 explicitly decided against ("so a fixture with three
guests doesn't count three times as much toward the season total as a
fixture with only one").

guest_per_fixture exists specifically to do the averaging step first —
GROUP BY fixture_id, AVG(points_awarded) — producing exactly one
averaged figure per fixture regardless of how many guests contributed
to it. guest_totals then sums THOSE already-averaged figures across
the season, which is the two-pass aggregation this chapter's own intro
describes: average per fixture first, then sum across fixtures.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains the real structural difference between guest and the other
three sources (multiple rows per fixture vs. exactly one), traces
concretely what would go wrong if the averaging step were skipped
(fixtures with more guests silently counting more), and describes what
the two-CTE structure actually accomplishes as a result.