SQL window functions, explained with real examples

Window functions let you calculate across a set of rows related to the current row while keeping every row in the result. That is the key difference from GROUP BY, which collapses rows into one per group. If you have ever needed "each order, plus the customer's running total" or "each month, plus last month's figure", window functions are the answer.

The anatomy of a window function

Every window function follows the same shape: a function, then OVER, then an optional partition, order and frame.

function_name(expression) OVER (
    PARTITION BY group_column      -- restart the calculation for each group
    ORDER BY sort_column           -- the order rows are processed in
    ROWS BETWEEN ... AND ...       -- which rows around the current one to include
)

All the examples below use a simple orders table with order_id, customer_id, order_date and amount.

Numbering and ranking rows

ROW_NUMBER, RANK and DENSE_RANK look similar but treat ties differently.

SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS row_num,
    RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS dense_rnk
FROM orders;

Top N per group

A classic interview question and an everyday task: the latest order for each customer. Rank inside a subquery or CTE, then filter.

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

Snowflake, BigQuery and DuckDB support QUALIFY, which filters on a window function directly and removes the need for the CTE:

SELECT *
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;

Comparing with the previous row: LAG and LEAD

LAG reads a value from an earlier row, LEAD from a later one. Perfect for month-on-month change or the time between a customer's orders.

WITH monthly AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
          / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly
ORDER BY month;

NULLIF(x, 0) turns a zero into NULL, so a month with no previous revenue returns an empty value instead of a division-by-zero error.

Running totals and moving averages

Add ORDER BY to an aggregate and it becomes cumulative. Add a frame and you control exactly how many rows it looks at.

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

Always write the frame explicitly. When you only give ORDER BY, most databases default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same date as one block and can produce surprising running totals.

Share of total without a second query

An empty OVER () means "the whole result". That makes percentage-of-total calculations a one-liner.

SELECT
    product,
    SUM(amount) AS revenue,
    ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_total
FROM orders
GROUP BY product
ORDER BY revenue DESC;

SUM(SUM(amount)) OVER () looks odd at first: the inner SUM is the grouped total per product, and the outer windowed SUM adds those totals across all products.

Common mistakes

• Filtering in WHERE: window functions are calculated after WHERE, so you cannot reference them there. Use a CTE or QUALIFY.
• Forgetting PARTITION BY: without it, rankings run across the whole table instead of per customer or per product.
• Non-deterministic ORDER BY: if two rows share the same date, ROW_NUMBER may pick either. Add a tie-breaker such as order_id.

Keep the SQL cheat sheet open while you practise. It has every pattern above on one printable page.

Written by Alessandro Ecclesie Agazzi, freelance analytics engineer in London. Updated 30 September 2026.