SQL Window Functions Cheatsheet

The syntax, the functions, and the three patterns that come up in almost every real query. For the full explanations, see the SQL guides.

Syntax pattern

function_name(expression) OVER (
    PARTITION BY column      -- reset the window for each group
    ORDER BY column          -- order within the window
    ROWS/RANGE frame_spec    -- optional: define the window size
)

GROUP BY collapses to one row per group. OVER (PARTITION BY ...) keeps one row per original row, with the aggregate computed alongside it.

Ranking functions

SELECT
    employee_id,
    department,
    salary,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_dense_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Function	Behavior on ties
`ROW_NUMBER()`	Always unique, no ties — arbitrary tiebreak
`RANK()`	Ties get the same number, next number skips (1, 1, 3)
`DENSE_RANK()`	Ties get the same number, next number doesn’t skip (1, 1, 2)
`NTILE(n)`	Splits the partition into `n` roughly-equal buckets, numbered 1..n

-- NTILE: split employees into 4 salary quartiles per department
SELECT employee_id, department, salary,
    NTILE(4) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_quartile
FROM employees;

Offset functions — LAG / LEAD

SELECT
    product_id,
    sale_date,
    revenue,
    LAG(revenue, 1)  OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS day_over_day_change
FROM product_sales;

-- LEAD: mirror of LAG — looks ahead instead of back
SELECT
    product_id,
    sale_date,
    revenue,
    LEAD(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_date) AS next_day_revenue
FROM product_sales;

Both take an optional offset (default 1) and an optional default value: LAG(revenue, 1, 0).

First/last value in a window

SELECT
    employee_id,
    department,
    salary,
    FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_top_salary,
    LAST_VALUE(salary)  OVER (
        PARTITION BY department ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS dept_bottom_salary
FROM employees;

LAST_VALUE() needs an explicit full-partition frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) — the default frame only looks back to the current row, which makes LAST_VALUE() return the current row’s value, not the partition’s actual last value.

Running totals & moving averages

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS seven_day_moving_avg
FROM daily_sales;

-- Aggregate window without collapsing rows
SELECT
    order_id, customer_id, amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_lifetime_value,
    amount / SUM(amount) OVER (PARTITION BY customer_id) AS pct_of_customer_total
FROM orders;

-- COUNT(*) OVER () — total row count alongside every row, one round-trip instead of two
SELECT product_id, name, price, COUNT(*) OVER () AS total_count
FROM products ORDER BY price DESC LIMIT 20 OFFSET 0;

Frame clause reference

Clause	Meaning
`UNBOUNDED PRECEDING`	From the start of the partition
`N PRECEDING`	N rows before the current row
`CURRENT ROW`	Up to and including the current row
`N FOLLOWING`	N rows after the current row
`UNBOUNDED FOLLOWING`	To the end of the partition

Default frame (when ORDER BY is present, no explicit frame given): RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Common patterns

Dedupe with ROW_NUMBER (CDC / keep-latest pattern)

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
    FROM raw_orders
)
SELECT order_id, customer_id, status, amount, created_at, updated_at
FROM ranked
WHERE rn = 1;

Top-N per group

WITH ranked AS (
    SELECT product_id, name, category, sales_count,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_count DESC) AS rn
    FROM products
)
SELECT product_id, name, category, sales_count
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

LIMIT alone can’t do this — it caps the whole result set, not per group.

Month-over-month growth

WITH monthly_totals AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY 1
),
ranked_months AS (
    SELECT month, revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_totals
)
SELECT month, revenue,
    revenue - prev_revenue AS change,
    ROUND(100.0 * (revenue - prev_revenue) / NULLIF(prev_revenue, 0), 1) AS pct_change
FROM ranked_months
ORDER BY month DESC;

NULLIF(prev_revenue, 0) avoids a divide-by-zero when the prior month had no revenue.