Weather Type in Each Country

Mechanism

The engine joins each Weather row to its Countries row on country_id, keeps only the November-2019 rows, collapses the survivors into one group per country with GROUP BY, computes AVG(weather_state) over each group, and a CASE maps that single average onto a Cold/Warm/Hot label. The whole query is one AVG per country wrapped in a three-way threshold test.

The correct query

SELECT c.country_name,
       CASE WHEN AVG(w.weather_state) <= 15 THEN 'Cold'
            WHEN AVG(w.weather_state) >= 25 THEN 'Hot'
            ELSE 'Warm'
       END AS weather_type
FROM   Countries c
JOIN   Weather w ON w.country_id = c.country_id
WHERE  w.day >= '2019-11-01' AND w.day < '2019-12-01'
GROUP BY c.country_id, c.country_name;

Two deliberate choices beyond the original answer. First, the date filter lives in WHERE, not in the join's ON — for this INNER JOIN both produce the same rows, but the placement encodes intent and survives a later change to LEFT JOIN (the pitfall below). Second, the predicate is a half-open range day >= '2019-11-01' AND day < '2019-12-01' rather than YEAR(day)=2019 AND MONTH(day)=11. Wrapping the column in YEAR()/MONTH() makes the predicate non-sargable: the engine must call the function on every row and cannot seek an index on day. A bare-column range comparison can use a B-tree index on day. Group by country_id (the key) and carry country_name along; grouping by name alone breaks if two countries share a name.

Why the naive trace is misleading

The original walkthrough invents tables called Step1, Step2, Step3 and selects FROM Step1. Those tables do not exist — you cannot run those snippets. SQL has no such intermediate relations you can name; the optimizer produces internal pipeline stages, not queryable tables. The trace below uses the real evaluation order instead.

Worked trace on the real rows

The logical clause order is FROM/JOIN → WHERE → GROUP BY → aggregate → SELECT/CASE. Every November row already passes the date filter, so after the join + WHERE the engine groups by country and averages:

Note the boundaries: 15 hits the <= 15 arm (Cold, not Warm) and 25 hits >= 25 (Hot). The CASE is evaluated top-to-bottom and stops at the first true arm, so an average of exactly 15 can never reach the Warm ELSE. Joining country_id back to names gives USA=Cold, Australia=Cold, China=Warm, Peru=Hot, Morocco=Hot.

Pitfalls

• ON vs WHERE is the real lesson. On this INNER JOIN they are interchangeable, which is exactly why it is a trap: the day you change to LEFT JOIN to also list countries with no November readings, a date predicate left in WHERE will discard those countries (their w.day is NULL, and NULL >= '2019-11-01' is unknown, which WHERE treats as false). The same predicate in ON keeps them. Decide by intent: filters on the preserved table go in WHERE; filters on the optional table go in ON.
• Non-sargable date filters. YEAR(day)=2019 AND MONTH(day)=11 forces a full scan because the column is buried in a function. The half-open range day >= '2019-11-01' AND day < '2019-12-01' is sargable and can use an index. Prefer < '2019-12-01' over BETWEEN ... '2019-11-30' so you don't miss 2019-11-30 14:00 timestamps.
• Boundary off-by-one in CASE. The spec says ≤15 is Cold and ≥25 is Hot. Writing < 15 or > 25 would misclassify the exactly-15 and exactly-25 countries (here country 2 and country 7). The order of the WHEN arms also matters — list the Cold and Hot tests before the Warm ELSE.
• AVG ignores NULLs, COUNT(*) does not. AVG(weather_state) divides by the count of non-NULL states, not the row count. If some readings were NULL, SUM/COUNT(*) would give a different (wrong) answer. Trust AVG for this.

Takeaways

• Aggregation pipeline: JOIN → WHERE → GROUP BY → AVG → CASE turns many daily rows into one labeled row per country.
• ON filters during the join; WHERE filters after. Identical on INNER JOIN, decisive on outer joins — a right-table filter in WHERE quietly demotes a LEFT JOIN to an INNER JOIN.
• Keep date columns bare in predicates (sargable half-open ranges) so the optimizer can use an index instead of scanning.
• Match CASE boundaries to the spec exactly (≤ and ≥), and group by the key, not a possibly-duplicated name.

Based on LeetCode 1294 “Weather Type in Each Country” and its editorial. The ON-vs-WHERE / outer-join semantics follow the SQL standard's logical clause-evaluation order (FROM/ON → JOIN NULL-padding → WHERE → GROUP BY → SELECT) as described in PostgreSQL and MySQL documentation and in Markus Winand's Use The Index, Luke on sargable predicates. Re-authored and deepened for this guide: corrected the imaginary Step1/Step2/Step3 tables into a real evaluation trace, moved the date filter to a sargable WHERE range, and added the ON-vs-WHERE systems lesson the original omitted.