PostgreSQL Basics #8 Views, CTEs, Window Functions: Keeping Complex Queries Readable

Infrastructure PostgreSQL Database
4 min read

Once chapter 3’s joins and aggregations start combining, queries quickly outgrow a screen. This chapter is three tools for handling that complexity. Views give a query a name, CTEs split a query into steps, and window functions unlock “rank and compare within groups” — the computation aggregation can’t do.

Views: a name for a repeated query #

Create a view 
CREATE VIEW paid_order_stats AS
SELECT user_id, count(*) AS order_count, sum(amount_usd) AS total_usd
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

SELECT * FROM paid_order_stats WHERE total_usd >= 1000;

A view is a stored SELECT. It is not a copy of data but a label whose underlying query runs at every read — always current, with zero storage. Views belong where a join-and-filter combination repeats across the codebase (“paid orders,” “active users”), turning it into shared team vocabulary.

If an aggregation is too heavy to run every time, there are materialized views. CREATE MATERIALIZED VIEW stores the result physically, so reads are fast — but after the source changes, the values are stale until you run REFRESH MATERIALIZED VIEW. The criterion: need “exactly now”? Regular view. Fine with “as of five minutes ago, but fast”? Materialized view plus periodic REFRESH.

CTEs: narrating a query top to bottom #

In chapter 3 we avoided double counting by joining subqueries, but as subqueries multiply, the query becomes a puzzle you read inside out. The WITH clause (CTE, Common Table Expression) does the same job top to bottom.

Narrating with CTEs 
WITH order_stats AS (
    SELECT user_id, sum(amount_usd) AS total_usd
    FROM orders GROUP BY user_id
),
review_stats AS (
    SELECT user_id, count(*) AS review_count
    FROM reviews GROUP BY user_id
)
SELECT u.name, coalesce(os.total_usd, 0) AS total_usd, coalesce(rs.review_count, 0) AS review_count
FROM users u
LEFT JOIN order_stats os ON os.user_id = u.id
LEFT JOIN review_stats rs ON rs.user_id = u.id;

“Build the order aggregate, build the review aggregate, attach them to users” — the order of thought becomes the order of the query. On performance: the planner’s current default behavior is to inline CTEs into the body and treat them like subqueries, so there is no need to worry that readability is being bought with performance. When you want to check, the answer is, as always, EXPLAIN.

Window functions: aggregation that doesn’t collapse rows #

GROUP BY folds each group into one row. But a requirement like “on each order row, also show that user’s total” must not fold rows. A window function leaves the rows as they are and computes within each row’s field of view (its window).

Window function basics 
SELECT id, user_id, amount_usd,
       sum(amount_usd) OVER (PARTITION BY user_id) AS user_total_usd,
       rank() OVER (ORDER BY amount_usd DESC)      AS overall_rank
FROM orders;

OVER is the marker of a window function; PARTITION BY says “who counts as my group,” and the ORDER BY inside says “counted in what order.” Unlike GROUP BY, the row count stays the same.

The canonical pattern is the top N per group promised in chapter 3.

Latest 3 orders per user 
-- each user's 3 most recent orders
SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn <= 3;

ROW_NUMBER() numbers each user’s rows 1, 2, 3… from most recent, and the outer query keeps 3 and under. For a single row, chapter 3’s DISTINCT ON is shorter; from N rows up, this pattern is the standard. Window function results can’t be used directly in WHERE (WHERE runs earlier in the execution order), so wrapping once in a subquery or CTE is part of the package.

The time-series staple LAG has the same anatomy.

Month-over-month with LAG 
-- monthly revenue with month-over-month difference
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS diff_from_prev
FROM monthly_revenue;

LAG fetches the previous row’s value in window order (NULL for the first row). Month-over-month deltas, gaps since the previous event — every “compare with a neighboring row” requirement dissolves into this one shape.

Summary #

  • A view is a stored SELECT and always current. To precompute heavy aggregation, use a materialized view plus REFRESH.
  • CTEs let a query narrate top to bottom. The planner inlines them, so readability costs no performance.
  • Window functions are aggregation that keeps rows. OVER + PARTITION BY + ORDER BY are the only three parts.
  • Top N per group is ROW_NUMBER plus a subquery filter — the standard pattern. For exactly one row, DISTINCT ON is the shortcut.
  • LAG/LEAD make neighbor-row comparisons (month over month and the like) a one-liner. The next chapter wraps up the basics with roles, permissions, and backups.
X