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

TASK
Record and score predictions from all four sources across at least two
fixtures, where one fixture has two guests and the other has only one,
then call prediction_table for that season and confirm the guest row's
predictions_scored equals 2, not 3.

SOLUTION

Using two fixtures in the current season, fixture_id 42 and
fixture_id 43:

1. Record predictions on fixture 42 (via upsert_prediction from
   Chapter 5): one user, one expert, one AI, and two separately-named
   guests ("Micah Richards", "Jamie Carragher").

2. Record predictions on fixture 43: one user, one expert, one AI, and
   a single guest ("Alex Scott").

3. Enter a real result for both fixtures via enter_result (Chapter 6),
   which fills in points_awarded for every prediction on each fixture,
   including all three guest rows on fixture 42 and the one guest row
   on fixture 43.

4. Call prediction_table for this season. Trace what the query does
   with the guest rows specifically:

   - season_predictions includes all 3 guest rows from fixture 42 and
     the 1 guest row from fixture 43 — 4 raw guest Prediction rows in
     total, since points_awarded is set on all of them by step 3.

   - guest_per_fixture groups those 4 rows by fixture_id and averages
     points_awarded within each group. This produces exactly 2 rows:
     one row for fixture_id 42 (the average of its two guest
     predictions' points), and one row for fixture_id 43 (which,
     averaged over just its single guest, is simply that guest's own
     points_awarded unchanged).

   - guest_totals then runs COUNT(*) FROM guest_per_fixture — and
     guest_per_fixture has exactly 2 rows, one per fixture, not 4 (the
     number of underlying guest predictions) and not 3 (fixture 42's
     own 2 guests plus fixture 43's 1).

5. In the returned JSON, the guest row's predictions_scored is 2 —
   confirming that predictions_scored counts fixtures with at least
   one guest prediction, not individual guest predictions, exactly as
   Chapter 8's own tip-box describes. A fixture contributing two guest
   predictions and a fixture contributing one both count as a single
   "1" toward this total, since both are still just one fixture each.

WHY THIS WORKS AS AN ANSWER
----------------------------
It sets up a real, deliberately asymmetric scenario (2 guests on one
fixture, 1 on another) specifically designed to distinguish "count of
fixtures" from "count of individual predictions," traces the value
through both stages of the two-pass aggregation (season_predictions ->
guest_per_fixture -> guest_totals), and confirms the returned value
(2) matches the fixture count rather than the raw prediction count
(3), which is exactly the distinction the task is testing for.