PostgreSQL / Intermediate: concurrency and maintainable SQL / Lesson 9

Views, CTEs and window queries

Structure a report into understandable relations and add per-row analytics without losing detail. Choose explicit scope, tie and frame rules.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Distinguish persistent view definitions from statement-local CTEs.
Compute deterministic rankings and cumulative totals.

A name clarifies a stage, not its runtime cost

A view stores a query definition, whereas a materialized view stores a result that must be refreshed according to a separate policy. A CTE names a relation within one statement. These tools can make grain and dependencies visible, but do not promise that a query is faster or that each named stage executes exactly once. PostgreSQL can fold suitable CTEs into a parent query; MATERIALIZED and NOT MATERIALIZED controls are available from version 12 with documented restrictions. Read the actual plan when reuse or filtering matters.

Separate summary grain from final selection

This complete CTE groups two payments for customer 7 into one total of twenty-five, then keeps totals above twenty. The final WHERE operates on the summary relation, not on individual payments. HAVING inside the grouped stage could also express that threshold, but a named summary becomes useful when later joins need exactly one row per customer. The CTE exists only for this statement, so a later SELECT cannot refer to totals unless it repeats the definition or uses a persistent object.

sql Illustrative SQL; expected results are reasoned, not measured.

WITH totals AS (
 SELECT customer_id, SUM(amount) AS paid
 FROM (VALUES (7, 10), (7, 15)) AS p(customer_id, amount)
 GROUP BY customer_id
) SELECT customer_id, paid FROM totals WHERE paid > 20;

Expected output

customer_id | paid
7           | 25

The input has payment grain; totals has customer grain.

A window preserves the detail row

Unlike GROUP BY, a window function keeps each input row and calculates across a related partition. PARTITION BY defines independent groups, the window ORDER BY defines analytic order and the outer ORDER BY defines display order. ROW_NUMBER assigns a distinct number to each row, while RANK and DENSE_RANK encode different tie policies. Filter a computed window result from an outer query or CTE because WHERE in the same SELECT runs before the window result is available.

Make a running frame explicit

For scan amounts five, eight and two, the explicit ROWS frame accumulates five, thirteen and fifteen. A default ordered frame can include peer rows with equal sort values, so timestamps alone may not express an event-by-event balance. Add a unique event identifier when that is the intended sequence. SUM(amount) OVER () instead repeats the complete partition total on every row. The chosen frame is part of the business calculation, not just a cosmetic ordering detail.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT scan_id, amount,
 SUM(amount) OVER (ORDER BY scan_id
 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM (VALUES (1, 5), (2, 8), (3, 2)) AS s(scan_id, amount)
ORDER BY scan_id;

Expected output

scan_id | amount | running
1       | 5      | 5
2       | 8      | 13
3       | 2      | 15

The analytic and displayed order are both explicit. No table fixture is required.

Review recursive and persistent boundaries

Recursive CTEs combine an anchor with repeated expansion and require explicit termination and cycle semantics. UNION can remove duplicate complete rows, but adding a changing depth or path can defeat naive duplicate-based termination. PostgreSQL provides SEARCH and CYCLE clauses from version 14 for supported recursive patterns. Persistent views also need security review: ordinary views use owner-related access rules, and security_invoker views are available from version 15. Neither a friendly view name nor a CTE replaces application authorization or a bounded traversal policy.

Common errors and fixes

relation "totals" does not exist (42P01)

Why it happens: A statement-local CTE was referenced after its statement ended.
How to fix it: Repeat WITH or use a deliberately managed view.
Prevent it next time: Document the lifetime of each named relation.

A running total jumps across tied times

Why it happens: Peer/frame behavior differs from the intended event sequence.
How to fix it: Use a stable unique order and an explicit ROWS frame.
Prevent it next time: Include tied values in analytic fixtures.

A recursive query never reaches the intended end

Why it happens: Cycles or changing projected paths keep producing distinct rows.
How to fix it: Define cycle and depth handling before traversing the graph.
Prevent it next time: Test cyclic and maximum-depth inputs, not only a simple tree.

Check your understanding

1. Fees are 50, 50 and 40. Compare ROW_NUMBER, RANK and DENSE_RANK for top selection.

Show worked solution

With a unique tie-breaker ROW_NUMBER can select exactly two rows. RANK on fee alone gives 1,1,3, while DENSE_RANK gives 1,1,2; DENSE_RANK <= 2 therefore includes all three. Specify whether the requirement is a fixed row count or a number of distinct score groups before choosing the function. Keep tie-breaking separate when ties must remain genuine rank ties.

2. A recursive query uses UNION but projects node_id and increasing depth. Why can a cycle still grow?