SQL Window Functions: No PARTITION BY Crashes 50M Rows

Every senior data engineer has hit the same wall: you need running totals, percentile ranks, or 'compare this row to its neighbours' logic, and suddenly GROUP BY feels like trying to paint a wall with a toothbrush. Window functions are the feature that separates analysts who write five nested subqueries from those who write one clean, efficient query.

The key mental model: a window function computes a value for each row using a defined set of related rows (the 'window') without removing any rows from the result. GROUP BY collapses rows into summaries. Window functions add derived columns to each row while leaving all rows intact.

The OVER Clause — How Windows Are Defined

Every window function requires an OVER clause — this is what makes it a window function rather than a regular aggregate. The OVER clause defines the window: the set of rows the function can 'see' when computing its value for the current row.

OVER() with nothing inside it means the window is the entire result set. PARTITION BY inside OVER divides the result set into groups — the function computes separately within each partition. ORDER BY inside OVER defines the ordering within each partition, which is required for ranking and running total functions.

The critical difference from GROUP BY: GROUP BY collapses multiple rows into one summary row. OVER keeps all rows intact and adds a computed column alongside each row. You can see the individual transactions AND the running total on every row simultaneously.

-- Setup: monthly sales by salesperson
CREATE TABLE sales (
    sale_id    INT PRIMARY KEY,
    rep_name   VARCHAR(100),
    region     VARCHAR(50),
    sale_date  DATE,
    amount     DECIMAL(10,2)
);

-- GROUP BY collapses to one row per rep -- individual sales lost
SELECT rep_name, SUM(amount) AS total
FROM sales
GROUP BY rep_name;

-- Window function: all rows preserved + running total added
SELECT
    sale_id,
    rep_name,
    sale_date,
    amount,
    -- Running total per rep, ordered by date
    SUM(amount) OVER (
        PARTITION BY rep_name      -- separate window per rep
        ORDER BY sale_date         -- accumulate in date order
    ) AS running_total,
    -- Grand total for comparison (no PARTITION BY = whole table)
    SUM(amount) OVER () AS grand_total
FROM sales
ORDER BY rep_name, sale_date;

Ranking Functions — ROW_NUMBER, RANK, and DENSE_RANK

Three ranking functions, three subtly different behaviours when ties exist.

ROW_NUMBER() assigns a unique sequential integer to every row, even when rows are tied. The order assigned to tied rows is arbitrary (unless you add a tiebreaker to ORDER BY). Use ROW_NUMBER() when you need to pick exactly one row per group — for example, the most recent order per customer.

RANK() assigns the same number to tied rows, then skips numbers. If two rows tie for position 2, both get rank 2 and the next row gets rank 4. The gap represents the number of tied rows.

DENSE_RANK() also assigns the same number to ties, but does not skip. Two rows tied at position 2 both get 2, and the next row gets 3. Use DENSE_RANK() when you want contiguous rank values regardless of ties — 'what is the 3rd highest salary' works with DENSE_RANK, not RANK.

-- Compare all three ranking functions on the same data
SELECT
    rep_name,
    region,
    SUM(amount)  AS total_sales,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS row_num,
    RANK()       OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) AS dense_rank_val
FROM sales
GROUP BY rep_name, region
ORDER BY region, total_sales DESC;

-- Common pattern: get the top 1 row per group
-- ROW_NUMBER guarantees exactly one row per region regardless of ties
WITH ranked_sales AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT rep_name, region, amount
FROM ranked_sales
WHERE rn = 1;  -- cannot use window function directly in WHERE -- must wrap in CTE

LAG and LEAD — Comparing Rows to Their Neighbours

LAG(column, n, default) returns the value from n rows before the current row within the window. LEAD(column, n, default) returns the value from n rows ahead. Both default n to 1 if omitted. The third argument sets what to return when there is no previous or next row (otherwise NULL).

These are indispensable for period-over-period analysis: month-over-month revenue change, day-over-day user growth, previous order date for a customer. Before LAG/LEAD existed, this required a self-join — and self-joins on large tables for row comparison are notoriously slow.

-- Month-over-month revenue change using LAG
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount)                    AS revenue
    FROM sales
    GROUP BY DATE_TRUNC('month', sale_date)
)
SELECT
    month,
    revenue,
    LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month_revenue,
    revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1) AS pct_change
FROM monthly_revenue
ORDER BY month;

-- Find customers whose next order came >90 days after the previous
-- LEAD gives us the next order date without a self-join
SELECT
    customer_id,
    order_date                                              AS this_order,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order,
    LEAD(order_date) OVER (PARTITION BY customer_id ORDER BY order_date)
        - order_date                                        AS days_to_next
FROM orders
HAVING days_to_next > 90 OR days_to_next IS NULL;

Window Frames — ROWS vs RANGE and the Subtle Default

The frame clause in an OVER definition controls exactly which rows are included in the window for each row's computation. This is the least understood part of window functions and the source of subtle bugs.

The default frame when you specify ORDER BY inside OVER is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — not ROWS. The RANGE default includes all rows that are tied on the ORDER BY value up to and including the current row. For dates with no duplicates this makes no visible difference. For financial data where multiple transactions share the same timestamp, the RANGE default groups all tied rows together — your running total will jump by the combined amount of all tied rows simultaneously, which is often not what you want.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW processes each physical row individually, regardless of ties. For running totals and moving averages, ROWS is almost always the correct choice.

-- RANGE default vs ROWS explicit -- spot the difference
SELECT
    sale_date,
    amount,
    -- Default: RANGE -- ties share the same cumulative value
    SUM(amount) OVER (
        ORDER BY sale_date
        -- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (implicit default)
    ) AS running_range,
    -- Explicit ROWS: each physical row accumulated independently
    SUM(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_rows
FROM sales
WHERE rep_name = 'Alice Tanaka'
ORDER BY sale_date;

-- 7-day moving average: ROWS frame with sliding window
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- current row + 6 before it
    ) AS moving_avg_7day
FROM daily_sales
ORDER BY sale_date;