Window Functions with GROUP BY — The Execution Order Trap

One of the most confusing aspects of window functions is how they interact with GROUP BY. The rules are specific, the errors are cryptic, and the patterns — once learned — are extremely powerful.

The execution order

FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY → LIMIT

Window functions run after GROUP BY. This means:

1. You can use a GROUP BY aggregate as the input to a window function
2. You cannot use a window function in a WHERE or HAVING clause
3. Every column referenced in a window function's OVER() must be available post-GROUP BY

Pattern 1: Window function over a GROUP BY aggregate

This is the double-aggregate pattern — the inner aggregate computes per-group totals, the outer window function operates on those totals:

-- Per-product revenue AND the category total, in one query
SELECT
    p.name,
    p.category,
    SUM(oi.quantity * oi.unit_price)                          AS product_revenue,
    SUM(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY p.category
    )                                                         AS category_total,
    ROUND(
        SUM(oi.quantity * oi.unit_price) * 100.0
        / NULLIF(SUM(SUM(oi.quantity * oi.unit_price)) OVER (PARTITION BY p.category), 0),
        1
    )                                                         AS pct_of_category
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name, p.category
ORDER BY p.category, product_revenue DESC;

SUM(SUM(...)) OVER (...) — the inner SUM is the GROUP BY aggregate (runs first, produces one row per product), the outer SUM is the window function (runs after, sums across the per-product rows within each category partition).

Pattern 2: Ranking GROUP BY results

After GROUP BY produces one row per group, window functions can rank those groups:

-- Monthly revenue ranked, with running total
WITH monthly AS (
    SELECT
        DATE_TRUNC('month', o.created_at)    AS month,
        SUM(oi.quantity * oi.unit_price)     AS revenue
    FROM orders o
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY DATE_TRUNC('month', o.created_at)
)
SELECT
    month,
    revenue,
    RANK() OVER (ORDER BY revenue DESC)                           AS revenue_rank,
    SUM(revenue) OVER (ORDER BY month
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM monthly
ORDER BY month;

The CTE makes the two-step nature explicit: GROUP BY first (in the CTE), window functions second (in the outer query). This is often cleaner than doing both in one query.