Multi-Step Analysis — CTEs and Window Functions Together

CTEs and window functions are individually powerful. Combined, they let you build sophisticated analytical pipelines where each step builds cleanly on the previous — without nested subquery hell. This lesson focuses on the patterns that appear constantly in real-world analytical SQL.

Why CTEs + window functions?

Window functions can't filter on their own results (no window function in WHERE). CTEs solve this by materialising an intermediate result that the next step can filter, join, or transform:

Raw data
  → CTE 1: aggregate with GROUP BY
  → CTE 2: compute window functions
  → CTE 3: filter/rank based on window results
  → Final SELECT: present the output

Each step is readable, testable, and independent.

Pattern 1: Rank → filter → enrich

The most common pattern: compute a ranking, keep only the top N, then join back for details.

-- Top 3 products by revenue in each category, with full product details
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        p.price                              AS list_price,
        SUM(oi.quantity * oi.unit_price)     AS revenue,
        SUM(oi.quantity)                     AS units_sold
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category, p.price
),
ranked AS (
    SELECT
        *,
        RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS cat_rank
    FROM product_revenue
)
SELECT
    name,
    category,
    list_price,
    revenue,
    units_sold,
    cat_rank,
    ROUND(revenue / NULLIF(units_sold, 0), 2) AS avg_selling_price
FROM ranked
WHERE cat_rank <= 3
ORDER BY category, cat_rank;

The product_revenue CTE does the GROUP BY. The ranked CTE adds window functions. The final SELECT filters and computes derived columns on the ranked result.

Pattern 2: Running totals with threshold detection

-- At what point in the product list do we hit 80% of total revenue? (Pareto analysis)
WITH product_revenue AS (
    SELECT
        p.name,
        p.category,
        SUM(oi.quantity * oi.unit_price) AS revenue
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.name, p.category
),
cumulative AS (
    SELECT
        name,
        category,
        revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_revenue,
        SUM(revenue) OVER ()                                                  AS total_revenue,
        ROUND(
            SUM(revenue) OVER (ORDER BY revenue DESC
                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            * 100.0 / NULLIF(SUM(revenue) OVER (), 0),
            1
        )                                                                     AS cum_pct
    FROM product_revenue
)
SELECT
    name,
    category,
    revenue,
    cum_revenue,
    cum_pct,
    CASE WHEN cum_pct <= 80 THEN 'Core 80%' ELSE 'Tail 20%' END AS pareto_group
FROM cumulative
ORDER BY revenue DESC;