Premier League Predictor: Astro — Chapter 7, Exercise 2
====================================================

TASK
Explain why the league-table query starts from season_teams with a
LEFT JOIN onto team_fixture_stats, rather than starting from
team_fixture_stats itself and grouping by team_id, and describe
concretely what would go wrong for a team with zero played fixtures
under the second approach.

SOLUTION

team_fixture_stats only ever contains a row for a team if that team
has actually appeared in at least one played fixture — it's built
entirely from the fixtures table, filtered to status = 'played'. If
the outer query started from team_fixture_stats and simply grouped by
team_id, the only teams that could ever appear in the result are
teams that already have at least one row in that CTE. A team that is
genuinely a member of the season (it has a real row in season_teams)
but hasn't played a single fixture yet — the very start of a season,
or a newly promoted club before its first match — would have zero
rows in team_fixture_stats, and GROUP BY can't produce a group for a
team that contributes no rows to group in the first place. That team
would simply be missing from the returned table entirely, rather than
showing up with a real, honest 0 played / 0 points row.

The fix is to flip which table the query actually starts from.
season_teams is the real source of truth for "which teams are
genuinely part of this season" — it doesn't care whether a team has
played yet, only whether it's competing. Starting the outer query
from season_teams (joined to teams for the display columns) and then
LEFT JOIN-ing team_fixture_stats onto it means every team that
belongs to the season is guaranteed to appear in the result exactly
once, whether or not it has any matching rows in the stats CTE. For a
team with zero played fixtures, the LEFT JOIN simply produces no
matching stats rows to join against, and every aggregate wrapped in
COALESCE(..., 0) then correctly reports 0 for played, won, drawn,
lost, goals_for, goals_against, and points, rather than the row being
silently dropped.

This is the exact same underlying pattern Chapter 3's own
GET /api/seasons/{id}/teams route already used: start from the table
that represents real membership, and join outward from there, rather
than starting from a table that only contains activity and hoping
every relevant row already happens to be present in it.

WHY THIS WORKS AS AN ANSWER
----------------------------
It correctly identifies that team_fixture_stats can only ever contain
rows for teams that have actually played, explains precisely why
GROUP BY on that table alone would drop a zero-fixture team from the
result rather than showing it with real zero values, and ties the fix
back to the same membership-first reasoning already established in
Chapter 3 rather than treating it as a new, unrelated technique.