Advanced SQL: Window Functions, CTEs and Recursive Queries

The Query That Should Not Be This Hard

You need the top 3 products by revenue for each category. With basic SQL, this becomes a nightmare:

-- The painful way: correlated subquery for each row
SELECT p.product_id, p.name, p.category_id, SUM(oi.amount) AS revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name, p.category_id
HAVING (
    SELECT COUNT(DISTINCT p2.product_id)
    FROM products p2
    JOIN order_items oi2 ON p2.product_id = oi2.product_id
    WHERE p2.category_id = p.category_id
      AND SUM(oi2.amount) >= SUM(oi.amount)
) <= 3;
-- Executes a subquery for every row. Slow, hard to read, fragile.

With a window function, this becomes:

-- The clean way: window function + CTE
WITH ranked AS (
    SELECT
        p.product_id,
        p.name,
        p.category_id,
        SUM(oi.amount)                                          AS revenue,
        RANK() OVER (PARTITION BY p.category_id
                     ORDER BY SUM(oi.amount) DESC)              AS rnk
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category_id
)
SELECT product_id, name, category_id, revenue
FROM ranked
WHERE rnk <= 3;
-- One pass. Readable. Fast.

Window functions and CTEs are not advanced features for edge cases. They are the right tool for a whole class of problems that basic SQL handles badly. This post teaches you all of them.

Part 1: Window Functions

A window function performs a calculation across a set of rows related to the current row — without collapsing those rows into a single output row. This is the fundamental difference from GROUP BY.

-- GROUP BY: collapses rows, loses individual row data
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
-- Result: one row per department

-- Window function: keeps all rows, adds the aggregate alongside
SELECT
    employee_id,
    name,
    salary,
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
FROM employees;
-- Result: one row per employee, with dept average alongside their own salary

The OVER clause defines the window — the set of rows the function looks at for each output row.

The OVER Clause: Defining the Window

function_name() OVER (
    PARTITION BY col1, col2   -- divide rows into groups (like GROUP BY, but doesn't collapse)
    ORDER BY col3 DESC        -- order within each partition
    ROWS BETWEEN ...          -- frame: which rows within the partition to include
)

PARTITION BY divides rows into groups. The function is applied independently within each partition. Omitting PARTITION BY means the entire result set is one partition.

ORDER BY within OVER determines the order of rows within a partition. Required for ranking functions and running totals. Changes the meaning of aggregate functions (from whole-partition to cumulative).

Frame clause (ROWS/RANGE BETWEEN) specifies which rows relative to the current row are included. Defaults vary by function — important to know.

-- No PARTITION: one window across all rows
AVG(salary) OVER ()

-- Partition by department: separate window per department
AVG(salary) OVER (PARTITION BY department_id)

-- Partition + order: running total within each department
SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date)

-- Explicit frame: 3-row moving average (previous, current, next)
AVG(salary) OVER (
    PARTITION BY department_id
    ORDER BY hire_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)

Ranking Functions

Ranking functions assign a position to each row within its partition.

SELECT
    employee_id,
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank,
    NTILE(4)     OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

The difference between ROW_NUMBER, RANK, and DENSE_RANK:

Salary    ROW_NUMBER    RANK    DENSE_RANK
──────────────────────────────────────────
90000         1           1         1
85000         2           2         2
85000         3           2         2       ← same salary, same RANK and DENSE_RANK
80000         4           4         3       ← RANK skips 3, DENSE_RANK does not
75000         5           5         4

• ROW_NUMBER — always unique, ties broken arbitrarily

• RANK — ties get the same rank, next rank skips (1, 2, 2, 4)

• DENSE_RANK — ties get the same rank, no gaps (1, 2, 2, 3)

• NTILE(n) — divides rows into n roughly equal buckets

When to use which:

• Deduplicate rows (keep latest per group) → ROW_NUMBER

• Top-N per group where ties should all be included → RANK or DENSE_RANK

• Percentile buckets (quartiles, deciles) → NTILE

-- Classic: get the most recent order per customer (deduplicate)
WITH latest AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT * FROM latest WHERE rn = 1;

-- Top 3 products per category (ties all included at rank 3)
WITH ranked AS (
    SELECT *,
        DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS dr
    FROM product_revenue
)
SELECT * FROM ranked WHERE dr <= 3;

Aggregate Window Functions

Any aggregate function (SUM, AVG, COUNT, MIN, MAX) can be used as a window function with OVER. The key insight: adding ORDER BY to the OVER clause changes the aggregate from a whole-partition aggregate to a running (cumulative) aggregate.

SELECT
    order_id,
    customer_id,
    amount,
    created_at,
    -- Whole-partition aggregate (no ORDER BY): same value for all rows in partition
    SUM(amount)   OVER (PARTITION BY customer_id)                            AS customer_total,
    COUNT(*)      OVER (PARTITION BY customer_id)                            AS customer_order_count,
    -- Running aggregate (with ORDER BY): cumulative up to current row
    SUM(amount)   OVER (PARTITION BY customer_id ORDER BY created_at)        AS running_total,
    AVG(amount)   OVER (PARTITION BY customer_id ORDER BY created_at
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)            AS moving_avg_3,
    -- Percent of total (combine both)
    ROUND(
        amount / SUM(amount) OVER (PARTITION BY customer_id) * 100, 2
    )                                                                        AS pct_of_total
FROM orders;

Frame clauses for moving windows:

-- Last 7 days (including current row) — RANGE uses ORDER BY value distance
SUM(amount) OVER (
    ORDER BY created_at
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
)

-- Last 3 rows (including current) — ROWS uses physical row position
AVG(price) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)

-- Cumulative from start to current row (default when ORDER BY present)
SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Whole partition (default when no ORDER BY)
SUM(amount) OVER (PARTITION BY customer_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Value Window Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

These functions access values from other rows within the window without a self-join.

SELECT
    order_id,
    customer_id,
    amount,
    created_at,
    -- Previous row's value
    LAG(amount)  OVER (PARTITION BY customer_id ORDER BY created_at)       AS prev_order_amount,
    LAG(amount, 2) OVER (PARTITION BY customer_id ORDER BY created_at)     AS two_orders_ago,
    -- Next row's value
    LEAD(amount) OVER (PARTITION BY customer_id ORDER BY created_at)       AS next_order_amount,
    -- Amount change from previous order
    amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS delta,
    -- First and last order amount for this customer
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS first_order_amt,
    LAST_VALUE(amount)  OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- need explicit frame for LAST_VALUE
    )                                                                        AS last_order_amt
FROM orders;

LAST_VALUE trap: Without the explicit frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE defaults to the frame ending at the current row — giving the current row's value, not the partition's last. Always specify the frame when using LAST_VALUE.

LAG/LEAD with default values:

-- Return 0 instead of NULL when there is no previous row
LAG(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY created_at)
--            ↑ default value when no prior row exists

Part 2: CTEs and Recursive Queries

CTEs: Named Subqueries

A Common Table Expression (WITH clause) names a subquery so the rest of the query can reference it by name. Introduced in the previous post — here we go deeper.

Multiple CTEs: building a pipeline

-- Monthly revenue, growth rate, and rolling 3-month average
WITH
monthly AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount)                      AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
),
with_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month)                              AS prev_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (ORDER BY month))
            / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1
        )                                                               AS growth_pct
    FROM monthly
),
with_rolling_avg AS (
    SELECT
        month,
        revenue,
        growth_pct,
        ROUND(
            AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0
        )                                                               AS rolling_3m_avg
    FROM with_growth
)
SELECT * FROM with_rolling_avg
ORDER BY month;

Each CTE is computed once and referenced by name. The query reads top to bottom like a pipeline — far cleaner than nesting subqueries inside subqueries.

Materialized vs non-materialized CTEs (PostgreSQL 12+):

-- Default: PostgreSQL decides whether to inline or materialize
WITH stats AS (SELECT ...)

-- Force materialization (computed once, result stored temporarily)
WITH stats AS MATERIALIZED (SELECT ...)

-- Force inlining (CTE is substituted into main query — optimizer can push predicates in)
WITH stats AS NOT MATERIALIZED (SELECT ...)

Use MATERIALIZED when the CTE is expensive and referenced multiple times — compute once, reuse. Use NOT MATERIALIZED when the optimizer needs to push WHERE conditions into the CTE for better performance.

Recursive CTEs: Traversing Hierarchies

Recursive CTEs are the SQL solution for hierarchical data — org charts, category trees, bill of materials, threaded comments, file systems.

The structure:

WITH RECURSIVE cte_name AS (
    -- Anchor: the starting rows (non-recursive, runs once)
    SELECT ... FROM table WHERE <base condition>

    UNION ALL

    -- Recursive: joins cte_name to itself (runs repeatedly until no new rows)
    SELECT ... FROM table JOIN cte_name ON <join condition>
)
SELECT * FROM cte_name;

Example 1 — Org chart: find all reports under a manager

-- employees table: id, name, manager_id (NULL for CEO)
WITH RECURSIVE reports AS (
    -- Anchor: start with the target manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 5  -- find everyone under employee #5

    UNION ALL

    -- Recursive: find direct reports of everyone already in the CTE
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
)
SELECT id, name, depth,
       REPEAT('  ', depth) || name AS indented_name  -- visual hierarchy
FROM reports
ORDER BY depth, name;

Example 2 — Category tree: find all subcategories

-- categories table: id, name, parent_id
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id,
           name::TEXT          AS path,
           1                   AS level
    FROM categories
    WHERE parent_id IS NULL   -- start from root categories

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           ct.path || ' > ' || c.name,
           ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, path, level
FROM category_tree
ORDER BY path;
-- Result:
-- Electronics
-- Electronics > Phones
-- Electronics > Phones > Smartphones
-- Electronics > Phones > Feature Phones
-- Electronics > Laptops

Example 3 — Generate a date series

-- Generate every day in a range (useful for filling gaps in time-series data)
WITH RECURSIVE dates AS (
    SELECT '2025-01-01'::DATE AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < '2025-01-31'
)
SELECT d FROM dates;

-- PostgreSQL has generate_series() for this, but the recursive pattern
-- works in any SQL database that supports recursive CTEs

Cycle detection — prevent infinite loops:

-- If data has cycles (e.g. corrupted org chart where A reports to B and B reports to A),
-- the recursive CTE will loop forever. Add a cycle guard:
WITH RECURSIVE reports AS (
    SELECT id, name, manager_id,
           ARRAY[id] AS visited   -- track visited IDs
    FROM employees WHERE id = 5

    UNION ALL

    SELECT e.id, e.name, e.manager_id,
           r.visited || e.id
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
    WHERE e.id <> ALL(r.visited)   -- stop if we've seen this ID before
)
SELECT * FROM reports;

-- PostgreSQL 14+ has native cycle detection:
WITH RECURSIVE reports AS (
    SELECT id, name, manager_id FROM employees WHERE id = 5
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e JOIN reports r ON e.manager_id = r.id
)
CYCLE id SET is_cycle USING path   -- built-in cycle detection
SELECT * FROM reports WHERE NOT is_cycle;

The Patterns That Appear in Every Production Codebase

Pattern 1 — Gaps and Islands (consecutive sequences)

Find contiguous runs of rows (e.g. consecutive login days, uninterrupted uptime periods):

-- Find consecutive login streaks per user
WITH daily AS (
    SELECT DISTINCT user_id, DATE(login_at) AS login_date
    FROM logins
),
grouped AS (
    SELECT
        user_id,
        login_date,
        -- Subtract row number from date: same value for consecutive days
        login_date - ROW_NUMBER() OVER (
            PARTITION BY user_id ORDER BY login_date
        )::INT * INTERVAL '1 day' AS grp
    FROM daily
)
SELECT
    user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_length
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Pattern 2 — Pivoting rows to columns

-- Monthly revenue per product category as columns
SELECT
    DATE_TRUNC('month', created_at)                         AS month,
    SUM(amount) FILTER (WHERE category = 'Electronics')     AS electronics,
    SUM(amount) FILTER (WHERE category = 'Clothing')        AS clothing,
    SUM(amount) FILTER (WHERE category = 'Books')           AS books
FROM orders
JOIN products USING (product_id)
GROUP BY 1
ORDER BY 1;

Pattern 3 — Running totals with reset

-- Cumulative sales that reset each month
SELECT
    created_at::DATE                                                 AS date,
    amount,
    SUM(amount) OVER (
        PARTITION BY DATE_TRUNC('month', created_at)
        ORDER BY created_at
    )                                                                AS monthly_running_total
FROM orders
ORDER BY created_at;

Window Function Quick Reference

🧭 What's Next

• Post 07: Query Optimization & EXPLAIN Plans — a slow query in production is a fire; learn to read EXPLAIN output, identify the exact bottleneck, and fix it with indexes, rewrites, or schema changes