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

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

SOLUTION

For user, expert, and AI, correct_scores and correct_results count how
many of that source's real, individual predictions landed on exactly
40 points or exactly 10 points. That's meaningful because each row
being counted really was one prediction that either did or didn't hit
one of those two outcomes.

Guest's own total_points figure is built differently: it's the SUM of
per-fixture AVERAGES from guest_per_fixture, not a sum of individual
prediction rows. An averaged value like 16.67 was never itself a
40-point correct score or a 10-point correct result — it's a blended
number that doesn't correspond to any single real outcome at all.
Counting "how many times the guest average hit exactly 40" would
either always be zero (since an average of several different guest
scores rarely lands on exactly 40) or would require inventing some
arbitrary rounding rule that doesn't reflect anything real about what
actually happened. Rather than fabricate a misleading statistic, the
query leaves both columns honestly NULL for guest, signaling "this
concept doesn't apply here" instead of a fake, always-zero count that
would look like guests simply never got anything right.

On why MySQL needs no explicit cast: PostgreSQL requires every branch
of a UNION ALL to resolve to a single, mutually compatible column type
for each position, and in some cases a bare, untyped NULL literal
gives it nothing concrete to reconcile against the INTEGER type
coming from the non_guest_totals branch — hence the sibling course's
own NULL::int, an explicitly-typed null. MySQL's type resolution
across UNION branches is more permissive: when one branch supplies a
plain NULL and the other supplies a real value for the same result
column, MySQL simply infers the column's type from whichever branch
actually has a concrete value, with no explicit cast required. The
column still correctly comes back as NULL for the guest row in both
databases — only the SQL syntax needed to make that happen differs.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains the real semantic reason correct_scores/correct_results
can't be meaningfully computed for an averaged figure (not "it's
complicated" but the specific fact that an average was never a single
scored outcome), and separately addresses the SQL-syntax question by
naming the real behavioral difference between MySQL's more lenient
UNION type inference and PostgreSQL's stricter requirement for an
explicitly-typed NULL.