Advanced22 min read

Window Functions & CTEs

Combine window functions and CTEs for advanced analytics and readable SQL.

Window Function Fundamentals

OVER (PARTITION BY ... ORDER BY ...) defines windows for ROW_NUMBER, RANK, LAG, LEAD, and aggregate windows. Results keep one row per input row unlike GROUP BY.

PostgreSQL supports DISTINCT in window definitions and rich frame clauses ROWS/RANGE/GROUPS BETWEEN.

SELECT
  user_id,
  total,
  SUM(total) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  LAG(total) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order
FROM orders;

Top-N Per Group

Row_number in subquery or CTE filters rank <= N—clean replacement for correlated subqueries finding latest record per customer.

DISTINCT ON alternative PostgreSQL-specific for picking first row per group ordered by criteria—often faster with right index.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

Recursive CTEs

Anchor member seeds recursion; recursive member joins to anchor/previous until fixpoint. Cycle detection via path arrays prevents infinite loops in graphs.

Use for org hierarchies, threaded comments, routing graphs, and generating date series.

WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name, 1 AS depth FROM employees WHERE id = $1
  UNION ALL
  SELECT e.id, e.manager_id, e.name, s.depth + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Combining CTEs and Windows

Chain CTEs: filter early in first CTE, compute windows in second, filter ranks in outer query. Improves readability and lets planner optimize stages.

MATERIALIZED hint forces CTE storage when reuse across query plan beneficial—benchmark both ways on large data.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change
FROM monthly;

Performance Tips

Index columns in PARTITION BY and ORDER BY. Avoid window functions on unfiltered million-row tables—CTE filter first. EXPLAIN ANALYZE window nodes for sort cost.

Consider materialized views for dashboards recomputing heavy window analytics on schedule.