SQL Query Reasoning: JOIN, CTE, Window, Aggregate, EXPLAIN

1. First screen — the query can look plausible and still answer the wrong question

A support team asks for a customer report. The report must contain one row per customer, recent paid spend, the date of the latest recent paid order, and the customer’s rank inside the plan tier. The first SQL draft runs. It even returns familiar customer names. Yet two defects hide in plain sight: a one-to-many join multiplies already-aggregated money, and a predicate in WHERE silently removes customers that the outer join was supposed to preserve.

This lesson treats SQL as a reasoning problem, not a string-building problem. Before choosing JOIN, CTE, GROUP BY, a window function, or EXPLAIN, state the result grain and the relationship cardinalities. Then use the smallest SQL mechanism that preserves that grain. Finally, collect plan evidence without turning planner estimates or one tiny fixture into universal performance claims.

Your evidence artifact is SQL Reasoning & Plan Evidence Record v1. Each important decision must connect a business question to intended grain, join cardinality, CTE role, aggregate/window choice, planner estimate, actual observation when a real PostgreSQL runtime is available, a bounded decision, and one residual limitation.

The lesson does not own transaction isolation, production index strategy, zero-downtime deployment, or a universal “fastest query.” Those belong elsewhere.

2. Route orientation — different support, identical evidence bar

Foundation route. Start with the grain sentence and the relationship map. Write “one row per customer” before touching the query. Mark customers → orders and orders → line_items as one-to-many. Use the guided order-report case and fill the evidence matrix one row at a time.

Repair route. Start from the first violated dimension. If total money doubles, do not add DISTINCT reflexively. Trace which join changes multiplicity. If a customer disappears, inspect the null-rejecting predicate around the outer join. Repair one dimension, rerun the observation, then restore the complete report.

Experienced route. Skip syntax introductions. You still must state grain, defend the aggregation boundary, distinguish grouped aggregation from window calculation, explain the CTE’s semantic role without claiming that it is always an optimization fence, and separate EXPLAIN estimates from EXPLAIN ANALYZE observations.

All routes finish with the same independent transfer. AI may suggest candidate SQL after you state the grain and invariant. AI may not invent PostgreSQL output or make the final accountable decision for you.

3. Mental model — business question → grain → cardinality → row-shaping stages → evidence

Use this sequence before debugging a complex query:

1. Business question. What must one output row mean?
2. Result grain. State it explicitly: one row per customer, per order, per day, or another bounded entity.
3. Input cardinality. For every join edge, record whether the relationship is one-to-one, one-to-many, many-to-one, or potentially many-to-many under the current predicates.
4. Preservation rule. Decide whether unmatched rows must survive. This determines whether an outer join is part of the contract rather than a stylistic choice.
5. Aggregation boundary. Decide which many-side rows must be reduced before they are combined with other many-side relations.
6. Window stage. Apply a window function when you need a calculation across related output rows without collapsing those rows. PostgreSQL documents window functions through the OVER clause. [PG18-WINDOW]
7. CTE role. Use WITH when it improves the reasoning boundary or enables a particular recursive/data-modifying structure. Do not assume every CTE is always materialized. PostgreSQL can fold or materialize eligible CTEs depending on semantics and references. [PG18-WITH]
8. Plan evidence. EXPLAIN gives planner estimates. EXPLAIN ANALYZE executes the statement and adds actual run statistics. [PG18-EXPLAIN]
9. Decision boundary. State what the observation supports and what it does not support.

The compact chain is:

business question → result grain → join cardinality → filter/group → window stage → SQL result → EXPLAIN estimate ↔ EXPLAIN ANALYZE observation → bounded decision

4. Foundation layer — five distinctions that prevent most reasoning mistakes

Grain comes before syntax

A syntactically valid query can still answer the wrong question. If the intended output is one row per customer, any intermediate operation that can create several rows per customer must either be intentionally preserved for a later stage or reduced before the final projection. PostgreSQL’s query pipeline includes table expressions, filtering, grouping, and projection; the SQL text order is not a substitute for reasoning about the row set. [PG18-QUERIES]

Join type and join condition are semantic

An INNER JOIN keeps matched row pairs. A LEFT JOIN preserves rows from the left side and supplies null-extended columns when no right-side match exists. But a later WHERE right_table.column = ... can reject those null-extended rows and effectively destroy the preservation you intended. The question is not “which join is faster?” but “which row survival rule matches the report?”

Aggregation collapses; windows annotate

GROUP BY customer_id reduces many input rows into one grouped row per customer (or per full grouping key). A window function such as RANK() OVER (...) evaluates across a window of rows while retaining row identity. PostgreSQL explicitly distinguishes ordinary aggregate use from aggregate-as-window use through OVER. [PG18-WINDOW]

A CTE is a reasoning boundary, not a universal performance instruction

A CTE may make stages explicit: order totals, then customer rollup, then ranking. That can improve reviewability. PostgreSQL may fold a non-recursive, side-effect-free CTE into its parent query under eligible conditions, while MATERIALIZED and NOT MATERIALIZED can influence that choice within documented boundaries. [PG18-WITH] Therefore “CTEs are always optimization fences” and “CTEs are always inlined” are both unsafe rules.

Plan cost is not elapsed time

Planner cost values are estimates expressed in planner cost units. They are not milliseconds and are not portable performance scores across machines or configurations. [PG18-USING-EXPLAIN] EXPLAIN ANALYZE adds actual timing/row observations by executing the statement. That makes it stronger evidence for this fixture, but still not proof of production behavior.

5. Deep explanation — reason through the order report one stage at a time

The fixture contains customers, orders, and line_items.

• One customer can have many orders.
• One order can have many line items.
• The report must keep every customer, even a customer with no paid order inside the recent window.
• Money must be summed from line items into an order total and then into a customer total.
• Ranking must happen after the report has one row per customer.

A fragile query often performs this sequence:

customers → orders → precomputed order totals → line_items again → GROUP BY customer

If an order has two line items, joining the already-computed order total back to those two line-item rows repeats the order total twice. SUM(order_total) now doubles money. Adding DISTINCT to the outer SELECT does not repair that arithmetic; the multiplication happened before the final row projection.

A stronger sequence is:

1. Create one row per qualifying order with order_total.
2. Left-join those order rows to customers.
3. Aggregate to one row per customer, using COALESCE for customers with no qualifying order.
4. Apply the tier rank as a window over the customer-level rows.
5. Order the presentation separately from the ranking expression.

The important property is not that this must be written as exactly two CTEs. Equivalent subqueries can preserve the same semantics. The evidence requirement is that you can identify the grain after every row-shaping stage.

Filters around outer joins

Suppose the query says:

FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

A customer with no order receives null-extended o.* values. The WHERE condition is not true for that row, so it is removed. If “keep every customer” is the contract, the qualifying-order predicate must be placed so that nonmatches can survive—for example inside a qualifying-order subquery/CTE or in the ON condition, depending on the rest of the design.

Grouped aggregate versus window aggregate

Consider two questions:

• “What is each customer’s recent spend?” — grouped reduction is natural.
• “What is each customer’s rank within plan tier?” — ranking is naturally a window over the already customer-grain result.

If you replace the grouped reduction with SUM(...) OVER (PARTITION BY customer_id) while leaving multiple line rows in the result, the numeric total may repeat correctly on several rows, but the result grain is still wrong. Correct values on duplicate rows are not the same contract as one row per customer.

6. Worked example — repair the bounded fixture

The starter intentionally contains two defects: redundant rejoining of line_items after order totals have been computed, and a qualifying-order predicate in WHERE after a LEFT JOIN.

Before editing, write this prediction in the evidence record:

• Intended grain: one row per customer.
• Expected preservation: a customer with no recent paid order remains with recent spend 0 and no latest-order timestamp.
• Expected mutation signal: a redundant one-to-many join can multiply a previously computed order total.

Then inspect the starter output when a real PostgreSQL runtime is available. Do not copy expected hidden rows from an oracle. Record what your runtime actually produced.

A justified repair may use an order_totals stage and a customer_rollup stage, followed by RANK() OVER (PARTITION BY plan_tier ORDER BY recent_spend DESC). The exact formatting is not important. The semantic checkpoints are:

• each qualifying order contributes its line total once;
• customers without qualifying orders survive;
• the rollup has one row per customer before ranking;
• ranking does not change the number of customer rows.

After the query is semantically correct, collect an estimate with EXPLAIN (FORMAT JSON, COSTS TRUE) and an observation with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on the SELECT query. Record PostgreSQL version, estimated rows at a relevant node, actual rows at the corresponding node when available, and the limitation that this tiny deterministic fixture is not production-scale evidence. [PG18-EXPLAIN] [PG18-USING-EXPLAIN]

7. Failure modes — deliberate wrong shortcuts

Failure A: hide a cardinality error with DISTINCT

If a join multiplies monetary values, DISTINCT may remove identical final rows while leaving the wrong sum. Repair the join/aggregation boundary, not the cosmetic symptom.

Failure B: move a right-side filter from ON/subquery into WHERE

Prediction: customers with no qualifying order disappear. Observation: compare row count and customer IDs before and after the move. Diagnosis: the null-extended row became null-rejected.

Failure C: treat a CTE name as proof of materialization

A readable WITH block does not prove a particular execution boundary. Use current PostgreSQL plan evidence for this fixture and keep the claim bounded. [PG18-WITH]

Failure D: replace grouped aggregation with a window and forget grain

A window can compute a repeated customer total while retaining multiple detail rows. If the contract is one row per customer, verify row count and key uniqueness separately from the numeric value.

Failure E: call EXPLAIN ANALYZE harmless on any statement

EXPLAIN ANALYZE executes the statement. For data-modifying statements that means side effects can occur. PostgreSQL documentation recommends using a transaction and rolling it back when you need to inspect such a statement without keeping the effects. [PG18-EXPLAIN] In this lesson, the normal plan evidence uses a SELECT; the side-effect case is a safety concept, not an invitation to modify shared data.

8. Experienced bridge — what to compress and what not to compress

An experienced engineer may already know join syntax, CTE syntax, and window syntax. Compress those. Do not compress these review questions:

• What does one output row mean?
• Which join edge changes multiplicity?
• Which unmatched rows must survive?
• At what stage is each many-side relation reduced?
• Does the window annotate customer-grain rows or accidentally preserve detail-grain rows?
• Is the CTE claim semantic, readability-oriented, or plan-specific?
• Is a number from EXPLAIN an estimate, and is a number from EXPLAIN ANALYZE an observed value from this specific execution?
• What changed between fixture and production conditions?

The faster route removes tutorial syntax, not evidence.

9. Alternatives and trade-offs — several SQL shapes can satisfy the same contract

CTE versus derived table

Both can express staged reasoning. A CTE often makes evidence boundaries easier to discuss. A derived table can be equally correct. Do not award correctness merely for choosing WITH.

Aggregate-before-join versus aggregate-after-join

If the join graph contains independent many-side relationships, pre-aggregation can prevent multiplicative rows. If there is only one many-side relation and every joined row legitimately contributes to the aggregate, aggregating later may be correct. The decision follows cardinality and grain, not a fixed style rule.

RANK, DENSE_RANK, and ROW_NUMBER

They encode different tie semantics. This lab asks for a rank within plan tier and uses RANK as the reference mechanism. A production report must define how ties should behave rather than choosing a function by habit.

EXPLAIN versus EXPLAIN ANALYZE

Use EXPLAIN when you need planner estimates without running the statement. Use EXPLAIN ANALYZE when actual execution evidence is required and execution is safe. The latter is stronger observational evidence but carries execution cost and, for data-modifying statements, side effects. [PG18-EXPLAIN]

10. Guided and independent practice

Guided pass

1. Open learner/sql/starter_broken.sql and learner/model-contract.json.
2. State the intended result grain before editing.
3. Predict which customer class can disappear because of the WHERE predicate.
4. Predict which monetary total can multiply because an already-aggregated order total is joined to line items again.
5. Edit learner/sql/query.sql without copying an internal solution.
6. Run python src/static_preflight.py.
7. With a disposable PostgreSQL instance, set MAKNOW_DATABASE_URL, set MAKNOW_DAT006_DISPOSABLE_CONFIRM=YES, and run python src/run_postgres.py. The runner uses a generated isolated schema and rejects non-read-only or multi-statement learner SQL.
8. Copy only observed output into your evidence record.

Independent pass

Change the domain to organizations and support tickets. The target is one row per organization, latest ticket information, and rank inside plan tier. You must derive grain, cardinality, reduction stage, and window stage independently. Do not copy the order-report SQL mechanically; the transfer is about reasoning structure.

11. Verification and evidence — close the loop without overclaiming

A complete evidence record contains at least:

• the business question and intended grain;
• join cardinality for each relationship used;
• the role of each CTE/subquery boundary;
• aggregate and window choices;
• a positive observation from the repaired query;
• a negative observation from one deliberate mutation;
• the repair result;
• EXPLAIN estimate evidence;
• EXPLAIN ANALYZE actual evidence if a real runtime was available and execution was safe;
• PostgreSQL version;
• one changed-context transfer decision;
• one residual limitation.

Mark every statement as observation, inference, or design decision. “The fixture returned four customer rows” is an observation only if you actually ran it. “The query therefore scales to 100 million rows” is not supported by this fixture. Plans depend on data distribution, statistics, parameters, configuration, and version. [PG18-USING-EXPLAIN]

12. Assessment and transfer

You should be able to answer five kinds of question:

1. Retrieval: state the result-grain contract and one non-goal.
2. Application: repair the bounded order report and produce evidence.
3. Diagnosis: predict, observe, and repair one deliberate cardinality/filter/window mutation.
4. Decision: defend your SQL shape against a plausible alternative without claiming universal performance.
5. Transfer: solve the organization/ticket case independently.

A passing answer does not depend on matching one formatting style. It depends on preserving the bounded contract and producing traceable evidence.

13. References and freshness boundary

The source boundary is intentionally narrow and PostgreSQL-specific:

• [PG18-QUERIES] — PostgreSQL 18 query/table-expression, grouping, and query-structure documentation.
• [PG18-WINDOW] — current PostgreSQL window-function documentation and OVER semantics.
• [PG18-WITH] — current PostgreSQL WITH/CTE folding and materialization boundaries.
• [PG18-EXPLAIN] — PostgreSQL 18 EXPLAIN / ANALYZE behavior, including execution/side-effect warning.
• [PG18-USING-EXPLAIN] — PostgreSQL 18 interpretation of estimates, costs, actual rows, and plan limits.

The frozen authority was retrieved 2026-09-02. Tool-volatile claims are scheduled for review by 2026-10-17; practice-evolving query/window claims by 2026-12-31. This authoring run occurs before those review dates and does not expand the frozen source boundary.