Premier League Predictor: Astro — Chapter 8, Exercise 2 ==================================================== TASK Explain why correct_scores and correct_results are computed as a real SUM of 1s and 0s first, and only nulled out afterward by an outer CASE, rather than writing the null-for-guest logic directly into the inner CASE that does the counting. SOLUTION There are two genuinely different reasons a source's own correct_scores value might come back as a non-positive number, and they need to stay distinguishable in the final result: 1. The source is user, expert, or ai, and it really did score zero exact predictions all season — a true, meaningful fact worth showing as a real 0. 2. The source is guest, whose own row is an average of several real scorelines' worth of points, never a literal 40-or-10-or-0 outcome in the first place — the whole concept of "was this an exact score" doesn't apply to it at all, and the honest answer is null, not 0. If the null-for-guest logic were folded directly into the counting CASE — for example, writing something like CASE WHEN source = 'guest' THEN NULL WHEN points_awarded = 40 THEN 1 ELSE 0 END — every individual row belonging to a non-guest source that simply didn't score 40 that fixture would still correctly produce a 0 or 1 per row, and SUM would still add them up fine for that group specifically. The problem only shows up structurally: mixing the "should this source get a null at all" decision into the same expression that's counting individual rows makes the query harder to read and easy to get wrong the moment the logic is refactored, since the null-ness of the whole aggregate and the per-row counting are two genuinely separate concerns being expressed in one line. Computing the real count first — CASE WHEN points_awarded = 40 THEN 1 ELSE 0 END, summed normally — guarantees every source's own correct_scores is calculated as an honest, real number regardless of which source it is. Only afterward does the outer CASE WHEN sfp.source = 'guest' THEN NULL ELSE END step in and override that already-correct number specifically for the one source where the underlying question genuinely doesn't apply. Each step does exactly one job: the inner SUM answers "how many exact scores did this source get," and the outer CASE answers "does that question even make sense for this source" — kept as two separate, individually simple decisions rather than one combined and more fragile one. WHY THIS WORKS AS AN ANSWER ---------------------------- It names the real distinction between "a genuine zero" and "not applicable," explains concretely why folding both decisions into one CASE expression is a fragile design even though it could technically be made to work, and correctly describes the two-step query as separating "count honestly" from "decide whether the count applies" into two independent, individually simple steps.