advanced · 24 minute study

Name intermediate relations; keep row-level context

A query is easier to debug when you can name its intermediate result and distinguish summarizing rows from annotating them.

Loading progressEvidence grows with practice

Useful preparation · 1 prerequisite

Use this as a map, not a lock. If these ideas are familiar, continue.

Keep missing relationships visible — Reports often become wrong by silently dropping the very customers with no activity. A join's row-preservation rule is part of the business question.

First, build a mental model

A CTE names a relation inside one statement. GROUP BY folds rows together; a window function adds a result to each row without folding it away.

Inspect a CTE's output independently while developing the query. A name improves clarity but is not a promise that PostgreSQL materializes or caches it.
PARTITION BY resets a window calculation for each group. ORDER BY inside OVER controls calculation order; final output ordering still needs its own ORDER BY.
An explicit ROWS frame makes a running total advance one ordered row at a time. Include a stable tie-breaker so the sequence is deterministic.
Window functions operate after filtering and grouping at their query level. Filter a computed rank in an outer query/CTE rather than assuming WHERE can read it at the same level.

Read the codesql · educational example

WITH paid AS (
  SELECT id, customer_id, total_cents
  FROM orders WHERE status = 'paid'
)
SELECT id, customer_id, total_cents,
  SUM(total_cents) OVER (
    PARTITION BY customer_id ORDER BY id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_cents
FROM paid ORDER BY customer_id, id;

Filter
The paid relation excludes draft and cancelled orders before window calculation.
Partition
Each customer gets an independent running total.
Frame
For an order, sum that customer's ordered rows up to the current row while retaining its ID.

Production thinking

Take this beyond the example.

First specify result cardinality and partition boundaries. Then examine execution plans. A correct-looking running total without deterministic ordering can change when rows tie.

Sources and further reading

Your evidence

unseen is a local learning signal, not a credential or proof of production competence.

Explain the mental model, then explicitly acknowledge it. Opening a page is not evidence.

Supported retries can complete this lesson. Independent evidence and delayed review determine the skill stage.

Next lesson · Explain why a query does work →