Premier League Predictor: FastAPI & PostgreSQL — Chapter 8, Exercise 2
====================================================

TASK
Explain why correct_scores and correct_results are left as NULL::int
for the guest row instead of being computed the same way as the other
three sources, and why a bare NULL (without the ::int cast) wouldn't
work in this specific query.

SOLUTION

correct_scores and correct_results, for user/expert/AI, count how many
of that source's own real predictions individually scored exactly 40
or exactly 10 points — a meaningful statistic, because each of those
sources has one genuine prediction per fixture that either did or
didn't hit one of those two categories.

The guest row's own total_points figure isn't built from individual
guest predictions at all by the time it reaches this final SELECT —
it's built from guest_per_fixture's own AVG(points_awarded) per
fixture, already averaged before guest_totals ever sums it. An average
like 25 (from two guests scoring 40 and 10, since (40+10)/2 = 25, or
any other real combination) was never itself a 40-or-10-or-0 outcome —
it's not that it "doesn't count" as a correct score or correct result,
it's that averaged points were never in that category system to begin
with, since only a single, whole, unaveraged prediction can be exactly
correct-score or exactly correct-result. Computing a "correct_scores"
count for guest would require picking some arbitrary rule for what an
averaged, fractional figure "counts as," which would be a genuinely
invented number, not a real statistic — so this query leaves both
columns honestly null instead of manufacturing a misleading one.

A bare NULL wouldn't work because UNION ALL requires every branch's
corresponding columns to share a compatible type. non_guest_totals'
own correct_scores column is a real integer (from
SUM(CASE ... THEN 1 ELSE 0 END)). An untyped NULL on its own carries no
type information for PostgreSQL to reconcile against that integer
column — NULL::int is a null value explicitly typed as an integer,
which is what actually lets the two branches' correct_scores columns
line up as the same underlying type in the combined result.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains precisely why an averaged figure was never eligible for the
correct-score/correct-result categorization in the first place (not
just "it's guest, so we skip it"), and separately explains the real
UNION ALL type-matching requirement that makes the ::int cast necessary
rather than optional styling.