Window Functions

Window functions are the most powerful tool in SQL for analytics. They compute a value across a set of rows related to the current row — without collapsing the result like GROUP BY does. Every row stays visible, but each row now knows its rank, its running total, or how it compares to the previous period.

Learning Objectives

• Understand the difference between GROUP BY aggregation and window functions
• Use OVER(), PARTITION BY, and ORDER BY to define windows
• Apply ranking functions: ROW_NUMBER, RANK, DENSE_RANK
• Compute running totals and moving averages with SUM OVER and AVG OVER
• Access adjacent rows with LAG and LEAD

---

What Makes Window Functions Different

-- GROUP BY: collapses rows — you lose the individual order details
SELECT customer_id, SUM(order_total) AS total
FROM orders GROUP BY customer_id;
-- Returns 1 row per customer

-- Window function: keeps every row, adds aggregate as a column
SELECT order_id, customer_id, order_total,
       SUM(order_total) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;
-- Returns 1 row per order, with the customer's total added to each row

Sample output:

order_id	customer_id	order_total	customer_total
O001	C001	298.00	333.99
O004	C001	35.99	333.99
O002	C003	389.00	638.95
O007	C003	249.95	638.95

---

The OVER() Clause — Defining the Window

The OVER() clause is what makes a function a window function.

function_name() OVER (
    PARTITION BY column    -- divide rows into groups (optional)
    ORDER BY column        -- sort within each group (required for ranking/running totals)
    ROWS BETWEEN ...       -- define the frame (optional)
)

• PARTITION BY — like GROUP BY for the window. Omit it to treat all rows as one group.
• ORDER BY — sort within each partition. Required for ranking and cumulative functions.
• ROWS BETWEEN — the "frame" — which rows relative to the current row to include. Defaults to all preceding rows when using ORDER BY.

---

Ranking Functions

ROW_NUMBER() — unique sequential number

SELECT
    order_id,
    customer_id,
    order_date,
    order_total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_total DESC
    ) AS rank_within_customer
FROM orders
WHERE status = 'completed';

Sample output:

order_id	customer_id	order_date	order_total	rank_within_customer
O001	C001	2024-01-05	298.00	1
O004	C001	2024-01-10	35.99	2
O002	C003	2024-01-07	389.00	1
O007	C003	2024-01-15	249.95	2

RANK() vs DENSE_RANK() — handling ties

SELECT
    product_id,
    category,
    unit_price,
    RANK()          OVER (PARTITION BY category ORDER BY unit_price DESC) AS rank,
    DENSE_RANK()    OVER (PARTITION BY category ORDER BY unit_price DESC) AS dense_rank,
    ROW_NUMBER()    OVER (PARTITION BY category ORDER BY unit_price DESC) AS row_num
FROM products;

unit_price	rank	dense_rank	row_num
389.00	1	1	1
149.00	2	2	2
89.99	3	3	3
49.99	4	4	4
49.99	4	4	5
35.99	6	5	6

• RANK(): ties get the same rank, next rank skips (4, 4, 6)
• DENSE_RANK(): ties get the same rank, no skipping (4, 4, 5)
• ROW_NUMBER(): always unique, arbitrary tie-breaking

The N-th row per group pattern — top 1 order per customer:

WITH ranked_orders AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        order_total,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_total DESC
        ) AS rn
    FROM orders
    WHERE status = 'completed'
)
SELECT order_id, customer_id, order_date, order_total
FROM ranked_orders
WHERE rn = 1;   -- largest order per customer

For senior analysts

This "ROW_NUMBER + WHERE rn = 1" pattern is one of the most common in analytics — it's how you get the latest, first, or Nth record per group without a complicated GROUP BY.

---

Running Totals and Moving Averages

Cumulative SUM

SELECT
    order_date,
    order_total,
    SUM(order_total) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

Sample output:

order_date	order_total	running_total
2024-01-05	298.00	298.00
2024-01-07	389.00	687.00
2024-01-10	35.99	722.99
2024-01-14	179.98	902.97

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This is the default frame when you use ORDER BY in OVER(). You can write it explicitly or let it default. It means: "from the beginning of the partition to the current row."

Running total partitioned by customer

SELECT
    customer_id,
    order_date,
    order_total,
    SUM(order_total) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS cumulative_spend
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_date;

3-month moving average (monthly data)

WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m')    AS month,
        SUM(order_total)                    AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    month,
    revenue,
    AVG(revenue) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3m
FROM monthly_revenue
ORDER BY month;

---

LAG and LEAD — Accessing Adjacent Rows

LAG() accesses the previous row. LEAD() accesses the next row.

Business use case: month-over-month revenue change.

WITH monthly_revenue AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m')    AS month,
        SUM(order_total)                    AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)         AS prev_month_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)   AS mom_change,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 1
    )                                           AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

Sample output:

month	revenue	prev_month_revenue	mom_change	mom_pct_change
2024-01	4829.50	NULL	NULL	NULL
2024-02	6145.00	4829.50	1315.50	27.2
2024-03	5890.00	6145.00	-255.00	-4.1

For mid-level analysts

LAG(column, n, default) takes up to 3 arguments: the column, how many rows back (default 1), and a default value when there's no previous row (default NULL). LAG(revenue, 12) gives you the same month last year.

---

NTILE — Percentile Buckets

Divide rows into N equal groups:

-- Divide customers into quartiles by total spend
WITH customer_spend AS (
    SELECT customer_id, SUM(order_total) AS total_spend
    FROM orders WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spend,
    NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_spend;

Quartile 1 = top 25% spenders. Used in RFM segmentation.

---

Practice Exercises

Warm-up 1. Add a ROW_NUMBER() to the orders table, ordered by order_date ascending. 2. Rank products by unit_price descending within each category using DENSE_RANK(). 3. Calculate a running total of revenue ordered by order_date.

Main 4. For each customer, find their single highest-value completed order (using ROW_NUMBER + CTE). 5. Calculate month-over-month revenue change and percentage change for completed orders in 2024. 6. Add a column showing each customer's total spend alongside individual order details (use SUM OVER PARTITION BY).

Stretch 7. Calculate a 3-month moving average of monthly revenue. Identify which months were above the moving average. 8. Build an RFM-style query: for each customer, calculate total orders, total spend, and days since last order. Then use NTILE(5) to score each metric on a 1-5 scale.

---

Interview Questions

[Beginner] What is a window function and how is it different from GROUP BY?

[Mid-level] What does PARTITION BY do in a window function?

[Mid-level] What is the difference between RANK() and DENSE_RANK()? Give an example where the difference matters.

[Mid-level] Write a query that returns the most recent order for each customer.

[Senior] You need to calculate 7-day rolling average of daily revenue on a 500M-row table. The query runs in 12 minutes. What are your optimisation strategies?

---

← Subqueries and CASE WHEN · Next: Advanced Case Studies →