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

TASK
Explain what would happen to a newly promoted team's own row in the
league table, at the very start of a season before it has played any
fixtures, if the query started from fixtures instead of from
season_teams.

SOLUTION

A team with zero played fixtures has no rows at all in team_fixtures,
since that CTE is built entirely from real fixture data. If the main
query started by selecting from fixtures (or from team_fixtures
directly) and joined out to teams from there, a team with no fixture
rows would simply never appear anywhere in that join at all — there's
nothing in the fixtures data to join it to teams through in the first
place.

Concretely: a newly promoted team, before its first gameweek has been
played, would be completely missing from the league table response —
not shown with 0 played and 0 points, just absent, as if it weren't a
real competing team in the season at all. Anyone viewing the table
before the season's first results are entered would see fewer than 20
teams, with no obvious explanation why some are missing.

Starting from season_teams instead avoids this because season_teams is
built from Chapter 3's own real "which 20 teams are competing this
season" data — a fact that exists the moment a team is added to a
season, independent of whether it's played anything yet. LEFT JOIN-ing
team_fixtures onto that base means every one of the 20 teams gets a
row no matter what, with COUNT and the COALESCE-wrapped SUMs correctly
producing 0 for played, won, drawn, lost, goals_for, goals_against, and
points when there's genuinely no fixture data to aggregate yet.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains precisely why a fixtures-first query structurally cannot
produce a row for a team with no fixtures (there's nothing to join
through), states the concrete real-world consequence (a promoted team
silently missing from the table before its season starts), and
explains how starting from season_teams with a LEFT JOIN avoids that
exact failure.