Window functions and CTEs

Window functions compute across a set of rows without collapsing them. CTEs name a subquery for clarity or recursion.

Window function anatomy

SELECT
    user_id,
    created_at,
    amount,
    SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS running_total
FROM transactions;

• OVER (...) is what makes it a window function. Without OVER, SUM is an aggregate that collapses rows.
• PARTITION BY — split into independent windows (like GROUP BY but rows aren’t merged).
• ORDER BY — order within each partition. Required for ranking and running aggregates.

Ranking functions

-- Top 3 most expensive products per category
SELECT * FROM (
    SELECT
        category, name, price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
    FROM products
) ranked
WHERE rn <= 3;

This is the canonical “top-N per group” pattern — much cleaner than JOIN against a MAX(price) subquery.

LAG and LEAD

Reach into adjacent rows.

SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS delta
FROM daily_revenue;

• LAG(col, n, default) — value n rows before in the window.
• LEAD(col, n, default) — value n rows after.

Running aggregates and frames

By default, ORDER BY in a window means “rows from start of partition through current row” (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Override with an explicit frame:

-- 7-day moving average
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_7d
FROM daily_revenue;

• ROWS — physical row count.
• RANGE — logical value range (e.g., date-based).
• GROUPS — peer groups (PG 11+).

CTEs (Common Table Expressions)

WITH active_users AS (
    SELECT id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS n FROM orders
    WHERE created_at > NOW() - INTERVAL '7 days'
    GROUP BY user_id
)
SELECT u.id, COALESCE(r.n, 0) AS orders_this_week
FROM active_users u
LEFT JOIN recent_orders r ON r.user_id = u.id;

Uses:

• Readability — name a subquery, reuse it.
• Refactor a long query — break it into named steps.
• Recursion — see below.

CTEs and the optimization fence

In PG <12, CTEs were always materialized — the planner couldn’t push predicates into them. Sometimes useful (force a plan), often a footgun.

PG 12+ inlines CTEs by default unless the CTE is recursive, has side effects, or is referenced multiple times. Force materialization with WITH foo AS MATERIALIZED (...), force inlining with AS NOT MATERIALIZED.

Recursive CTEs

For tree/graph traversal in pure SQL.

-- Find all descendants of a category
WITH RECURSIVE descendants AS (
    -- anchor: start node
    SELECT id, parent_id, name FROM categories WHERE id = 5

    UNION ALL

    -- recursive: join with anchor
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    JOIN descendants d ON c.parent_id = d.id
)
SELECT * FROM descendants;

Mechanics:

1. Run the anchor query, store results.
2. Run the recursive part using current results, append.
3. Repeat until the recursive part returns no new rows.

Cycles aren’t detected by default — infinite loop on graphs with cycles. Add a path column to track:

WITH RECURSIVE traversal AS (
    SELECT id, parent_id, ARRAY[id] AS path FROM categories WHERE id = 5
    UNION ALL
    SELECT c.id, c.parent_id, t.path || c.id
    FROM categories c
    JOIN traversal t ON c.parent_id = t.id
    WHERE NOT (c.id = ANY(t.path))  -- skip already-visited
)
SELECT * FROM traversal;

PG 14+ has CYCLE clause for this:

WITH RECURSIVE traversal AS (...)
CYCLE id SET is_cycle USING path

Common patterns

Pagination with stable ordering:

SELECT *, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn FROM events;
-- ORDER BY created_at alone is not stable on duplicates; add id as tiebreaker.

Deduplicate keeping latest:

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
) t WHERE rn = 1;

Gap and island detection (consecutive runs):

SELECT user_id, MIN(date), MAX(date)
FROM (
    SELECT user_id, date,
           date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) AS grp
    FROM activity
) t
GROUP BY user_id, grp;

Interview angle

• Q: “What’s a window function and how does it differ from GROUP BY?” — windows compute across rows without collapsing them.
• Q: “Difference between RANK, DENSE_RANK, ROW_NUMBER?” — ties.
• Follow-up: “How do you compute a 7-day moving average?” — window with ROWS frame.
• Follow-up: “When would you use a recursive CTE?” — tree/graph traversal in SQL — category trees, org charts, paths.

See 03_sql_processing.md for query execution order.