SQL Analytics — Window Functions, CTEs & Set-Based Thinking

Overview

Senior interviews and production analytics share a failure mode: treating SQL like a loop over rows. Window functions, CTEs, and set-based thinking turn Top-N, running totals, and hierarchies into declarative plans the optimizer can rewrite.

This hub maps the cluster. It does not re-teach indexes or EXPLAIN. Those live in Indexes, cardinality, and EXPLAIN and EXPLAIN and EXPLAIN ANALYZE. Use that cluster for plans. Use this cluster for query shape before you tune.

By the end you should be able to:

• Choose window vs CTE vs LATERAL from the business question.
• Say why a window beats a self-join for ranking and previous-row logic.
• Treat CTE materialization as something you measure, not something you assume.
• Point at the sibling page instead of dumping every frame rule here.

flowchart TB
  a["1 Name the question"]
  b["2 Pick window, CTE, or LATERAL"]
  c["3 Write the query shape"]
  d["4 Confirm with EXPLAIN"]
  a -->|1 Name the question| b
  b -->|2 Pick window, CTE, or LATERAL| c
  c -->|3 Write the query shape| d

Single column. The three shapes are a choice in the next section, not three swimlanes.

What this cluster covers

1. Window functions — OVER (), frames, default-frame pitfalls.
2. Ranking and Top-N — ROW_NUMBER, RANK, DENSE_RANK, NTILE, Postgres DISTINCT ON.
3. Running totals, LAG/LEAD, and islands — cumulative sums, deltas, sessionization.
4. CTEs — readability vs materialization, trees, cycle guards.
5. LATERAL vs correlated subqueries — per-row Top-N, table functions, when windows win instead.

Set-based thinking vs row-by-row

• Row-by-row: app loops and cursors become N+1 round-trips. Little is shared with the optimizer.
• Self-join gymnastics: join a table to itself with inequalities. Readable poorly. Cardinality can explode.
• Set-based windows: one partition/sort pass. Clear Top-N, running totals, and lag. A wrong frame is a silent wrong answer.
• CTE staging: named steps for humans. Postgres may inline or materialize. Confirm with EXPLAIN.

Rule of thumb: prefer one sorted partition pass for ranking, running totals, and previous-row logic. Prefer CTEs for human staging, then measure materialization.

When windows beat self-joins

Classic self-join Top-1-per-group is fragile on ties. Prefer a window with a deterministic tie-break:

SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM orders o
) x WHERE rn = 1;

Windows make the tie-break obvious and Top-k is rn <= k. They still sort and partition. Supporting keys can make that sort cheaper — that design lives in the Indexes hub. The index does not replace the right window.

When CTEs help readability vs performance

• Help: multi-step analytics, recursive org and tree walks, documenting intent.
• Watch: since Postgres 12 the planner may inline non-recursive CTEs. MATERIALIZED and NOT MATERIALIZED override that. Confirm with EXPLAIN (ANALYZE, BUFFERS) on the EXPLAIN page.

Decision cheat sheet

Conceptual link to Indexes / EXPLAIN

Windows and LATERAL still sort, hash, and nested-loop. A Top-N window on an unindexed (customer_id, created_at) can seq-scan and sort. This cluster teaches shapes. Indexes and EXPLAIN teaches plans. Interview line: write the window for correctness, then check the plan. Do not recite access-method internals here.

Sandbox: partition, then order

Same ranking idea as ROW_NUMBER() OVER (PARTITION BY cust ORDER BY ts DESC).

Press Run. Snippets must be self-contained — no network, files, or native modules.

Press Run. Snippets must be self-contained — no network, files, or native modules.

Pitfalls

Interview Q&A

Go Deeper

• PostgreSQL docs: Window Functions
• PostgreSQL docs: WITH queries
• PostgreSQL docs: SELECT (DISTINCT ON)
• Use The Index, Luke: window functions
• Modern SQL
• YouTube: Learn basic window functions in PostgreSQL
• Next: Window Functions — PARTITION BY, ORDER BY, and Frames