Premier League Predictor: Astro — Chapter 8, Exercise 1
====================================================

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

SOLUTION

The averaging genuinely has to happen at a different level of
granularity than the rest of the query operates at. user, expert, and
ai predictions each contribute exactly one row per fixture to
predictions, so reading them directly, one row at a time, already
gives the right unit of comparison. guest predictions don't: a
fixture with three guest predictions has three separate rows in
predictions, all with source = 'guest', and none of those three rows
on its own is "the" guest figure for that fixture — only the average
of all three is.

If guest rows were pulled into source_fixture_points without being
pre-averaged first, each individual guest prediction would count as
its own separate row toward the season total, exactly the same way a
single user prediction does. A fixture with three guests would then
contribute three rows to the aggregate instead of one, silently
inflating both the guest source's own fixtures_scored count and, more
seriously, its total_points sum relative to a fixture that only had a
single guest that week. The season total would then depend on how
many people happened to guest each week, which is precisely the
distortion Chapter 6 introduced averaging to avoid in the first
place.

Running guest_fixture_points as its own separate CTE first means the
averaging happens once, per fixture, before those rows are ever
combined with anything else. By the time source_fixture_points reads
from guest_fixture_points, it's reading exactly one row per fixture
that had at least one guest prediction — already in the same shape
and the same unit of comparison as every user, expert, and ai row it
sits alongside in the UNION ALL. Doing the averaging and the unioning
in a single combined step would require expressing "average these
guest rows down to one, but leave every other source's rows exactly
as they are" inside one query, which is a more complicated shape than
simply doing the averaging first and then unioning already-correct
data.

WHY THIS WORKS AS AN ANSWER
----------------------------
It correctly identifies the real unit-of-granularity mismatch between
guest predictions (many rows per fixture) and every other source (one
row per fixture), concretely describes the double-counting distortion
that would result from skipping the separate averaging pass, and
explains why doing the averaging first genuinely simplifies the
combining step rather than just asserting that two passes are
"cleaner."