Named Windows — The WINDOW Clause

When a query uses the same window definition multiple times, repeating the full OVER (PARTITION BY ... ORDER BY ... ROWS BETWEEN ...) clause for every function is verbose and error-prone. The WINDOW clause lets you name a window once and reference it across multiple functions.

The problem: repetition

-- Without named windows — same definition repeated 4 times
WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT
    name,
    category,
    revenue,
    RANK()       OVER (PARTITION BY category ORDER BY revenue DESC) AS cat_rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS cat_dense_rank,
    PERCENT_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS cat_pct_rank,
    FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY revenue DESC
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS cat_leader
FROM product_revenue;

Four identical (or near-identical) window definitions. If you need to change the partition or sort, you change it in four places — a maintenance hazard.

The WINDOW clause

Define windows once at the bottom of the query, then reference them by name in OVER:

WITH product_revenue AS (
    SELECT
        p.product_id,
        p.name,
        p.category,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue
    FROM products p
    LEFT JOIN order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.category
)
SELECT
    name,
    category,
    revenue,
    RANK()         OVER cat_window                               AS cat_rank,
    DENSE_RANK()   OVER cat_window                               AS cat_dense_rank,
    ROUND((PERCENT_RANK() OVER cat_window)::NUMERIC, 3)                     AS cat_pct_rank,
    FIRST_VALUE(name) OVER (cat_window
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS cat_leader
FROM product_revenue
WINDOW cat_window AS (PARTITION BY category ORDER BY revenue DESC)
ORDER BY category, revenue DESC;

The WINDOW clause lives between HAVING and ORDER BY. You can define multiple named windows:

WINDOW
    cat_window    AS (PARTITION BY category ORDER BY revenue DESC),
    global_window AS (ORDER BY revenue DESC)

Extending a named window

You can reference a named window inside OVER() and add more clauses — but you cannot override clauses already defined in the named window:

WINDOW base_w AS (PARTITION BY category ORDER BY revenue DESC)

-- Valid: add a frame clause to the named window
OVER (base_w ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- Invalid: cannot re-specify ORDER BY if already in named window
OVER (base_w ORDER BY revenue ASC)   -- ERROR

This lets you define a base partition + sort once, then customise the frame per function.