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.

Why Window Functions Are Not Just Fancy GROUP BYs

A window function computes a value across a set of rows related to the current row, without collapsing them into a single output row. Unlike aggregate functions (SUM, AVG) that reduce N rows to 1, window functions preserve every row while adding a computed column. The core mechanic is the OVER() clause, which defines the window frame — the subset of rows the function operates on. Without PARTITION BY, the frame defaults to the entire result set, meaning every row sees the same aggregate over all 50 million rows. That single-pass scan over the full dataset is O(n) per row, turning a query into a full table scan with a massive memory footprint. In practice, this means a query that should run in milliseconds can balloon into a multi-minute operation, spilling to disk and hammering tempdb or swap. The key property: window functions execute after WHERE, GROUP BY, and HAVING but before ORDER BY, so they operate on the filtered, grouped intermediate result. Use them when you need running totals, moving averages, row rankings, or comparisons to a group-level value (like 'each employee's salary vs. department average') without losing detail. In real systems, they replace self-joins and correlated subqueries, cutting query complexity from O(n²) to O(n log n) or better.

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;

SQL Evaluation Order — Where Window Functions Live

Understanding the logical order of SQL clause evaluation is essential for writing correct window function queries. The order is: FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → DISTINCT → ORDER BY → LIMIT. Window functions are computed after WHERE, GROUP BY, and HAVING but before ORDER BY and SELECT. This means:

• You cannot reference a window function alias in the same SELECT's WHERE clause — it hasn't been computed yet. If you try, you get an 'column not found' error.
• You cannot filter on a window function result in WHERE. Wrap in a CTE or subquery.
• ORDER BY happens after window functions, so you can ORDER BY a window function alias without issues.
• GROUP BY aggregates are available before window functions — your window can reference GROUP BY output (like SUM(salary) for that group).

The WINDOW clause in the evaluation order refers to the window function computation phase, not the WINDOW clause keyword (which is syntactic sugar to name a window definition for reuse).

-- This query will fail because window function alias 'rn' is not available in WHERE
SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
FROM sales
WHERE rn = 1;  -- ERROR: column 'rn' does not exist

-- Proper pattern: wrap in a CTE (WITH) to allow filter after window computation
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
SELECT * FROM ranked WHERE rn = 1;

-- ORDER BY can use window function alias directly
SELECT *,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_val
FROM sales
ORDER BY rank_val;  -- works fine

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;

The WINDOW Clause — Reusing Window Definitions

When you use the same window definition in multiple aggregate functions, repeating the full OVER clause becomes error-prone and hard to maintain. The WINDOW clause (introduced in SQL:2003 and supported by PostgreSQL, MySQL 8+, SQL Server, and Oracle) lets you name a window specification and reference it by name in multiple OVER clauses.

Instead of writing: SUM(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_region_total, AVG(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg

You can define the window once and reuse it: WINDOW w AS (PARTITION BY region ORDER BY sale_date)

Then refer to it with a frame variation: SUM(amount) OVER w AS running_total, SUM(amount) OVER (w ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg

This reduces duplication, improves readability, and ensures the partition/order definition is consistent across all uses. If you need to change the partition key later, you only modify the WINDOW clause.

-- Without WINDOW clause: repetitive OVER definitions
SELECT
    region,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total,
    AVG(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales
ORDER BY region, sale_date;

-- With WINDOW clause: define once, reuse
SELECT
    region,
    sale_date,
    amount,
    SUM(amount) OVER w                           AS running_total,
    AVG(amount) OVER (w ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg,
    COUNT(*) OVER w                              AS sales_count_in_region_so_far
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_date)
ORDER BY region, sale_date;

FIRST_VALUE, LAST_VALUE, and NTH_VALUE — Row Navigation Within Partitions

FIRST_VALUE(column), LAST_VALUE(column), and NTH_VALUE(column, n) return values from specified positions within a window partition. FIRST_VALUE gets the first row's value according to the window's ORDER BY. LAST_VALUE gets the last row's value — but careful: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the value from the current row within the current frame, not necessarily the partition's absolute last. To get the true last value in the partition, specify a frame of ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

NTH_VALUE(column, n) returns the value from the nth row (1-indexed) within the window. It's useful for getting, say, the second highest sale per region without a subquery.

These functions are most valuable when you need to compare the current row to the first or last row of a group — for example, showing the first order date for a customer alongside every subsequent order, or the percentage change from the first value.

-- FIRST_VALUE and LAST_VALUE with proper frame
SELECT
    rep_name,
    sale_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY rep_name
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_sale_amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY rep_name
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_sale_amount,
    NTH_VALUE(amount, 2) OVER (
        PARTITION BY rep_name
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS second_sale_amount
FROM sales
ORDER BY rep_name, sale_date;

Statistical Window Functions — NTILE and CUME_DIST

NTILE(n) divides the window into n as-equal-as-possible buckets (tiles) and assigns a bucket number from 1 to n to each row. It's commonly used for quartile analysis (NTILE(4)), decile analysis (NTILE(10)), or percentiles. Rows are evenly distributed across the buckets; if the count doesn't divide evenly, the first buckets get one extra row.

CUME_DIST() computes the cumulative distribution — the relative position of a row within the window. It returns a value between 0 and 1, representing the proportion of rows with a value less than or equal to the current row's value. For the row with the highest value, CUME_DIST = 1. For the lowest, it's 1/N (where N is the partition size). This is different from PERCENT_RANK(), which uses (rank-1)/(N-1).

NTILE is excellent for creating A/B test groups, splitting data into training and test sets, or identifying top/bottom deciles. CUME_DIST is ideal for percentile-based thresholds (top 10%, above median, etc.).

-- NTILE: Divide reps into 4 quartiles by total sales
SELECT
    rep_name,
    SUM(amount) AS total_sales,
    NTILE(4) OVER (ORDER BY SUM(amount) DESC) AS sales_quartile,
    CUME_DIST() OVER (ORDER BY SUM(amount) DESC) AS cumulative_dist
FROM sales
GROUP BY rep_name
ORDER BY total_sales DESC;

-- Use NTILE for A/B test bucket assignment (deterministic with ORDER BY)
SELECT
    user_id,
    NTILE(3) OVER (ORDER BY user_id) AS test_group  -- 1,2,3
FROM users;

-- Find top 10% of customers by revenue using CUME_DIST
WITH customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS total_revenue,
        CUME_DIST() OVER (ORDER BY SUM(amount) DESC) AS percentile
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_revenue
FROM customer_revenue
WHERE percentile <= 0.1
ORDER BY total_revenue DESC;

Types of Window Functions — A Battlefield Taxonomy

You don't need a PhD in SQL to understand window functions. You need a clear head and a mental map of which tool does what. Competitors love to dump you into 'Aggregate vs Ranking' and call it a day. That's like handing a recruit a toolbox with 'hammer' and 'saw' labels — it's technically true, but it doesn't help you pick the right one when the server's on fire.

There are three real categories, not two. Aggregate window functions (SUM, AVG, COUNT, MAX, MIN) let you run rolling totals or moving averages without collapsing rows. They answer "What's the running sum up to this row?" — a GROUP BY killer. Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE, CUME_DIST) assign positions. They answer "Where does this row sit relative to its peers?" — essential for leaderboards, deduplication, percentile splits. Value functions (LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE) reach across rows within the same partition. They answer "What's the previous order value?" or "Who was the first customer this month?" — row-level comparisons without a self-join.

Why this matters: Pick the wrong category and your query either collapses into a single row (using GROUP BY when you meant a window) or explodes into a cartesian product (reaching outside your partition). Know your categories before you write a single OVER clause.

// io.thecodeforge — database tutorial

WITH sales AS (
  SELECT '2024-01-01' AS sale_date, 100.0 AS amount
  UNION ALL SELECT '2024-01-02', 150.0
  UNION ALL SELECT '2024-01-03', 200.0
  UNION ALL SELECT '2024-01-04', 120.0
)
SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS rolling_total,      -- aggregate
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rank_by_amount,  -- ranking
  LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_day_amount -- value
FROM sales;

PERCENT_RANK and CUME_DIST — Your Stats Toolkit for Real Data

Every dashboard has a percentile request. "Show me customers in the top 10%." "Flag orders above the 95th percentile." Your juniors will reach for NTILE and then fight with bucket boundaries. Stop them. PERCENT_RANK and CUME_DIST are the right tools for this job, and most tutorials skip them entirely.

PERCENT_RANK gives you the relative rank of a row within a partition, normalized to a 0-to-1 scale. It computes as (RANK - 1) / (total_rows - 1). First row gets 0. Last gets 1. Perfect for "top 10% high spenders" when you need a continuous score, not fixed buckets.

CUME_DIST stands for cumulative distribution. It's simpler: it tells you the proportion of rows with values less than or equal to the current row. So the 100th percentile row shows 1.0, meaning all rows are <= its value. That's your "95th percentile threshold" calculator for anomaly detection.

Real talk: PERCENT_RANK is better than NTILE when you need precise cutoffs. NTILE divides into equal buckets — if you have 101 rows, NTILE(10) creates buckets of 10 or 11 rows. That's lumpy. PERCENT_RANK gives you a smooth gradient. Use it in WHERE clauses to filter your outliers.

Here's how they differ on the same data — watch the output closely.

// io.thecodeforge — database tutorial

WITH scores AS (
  SELECT 50 AS score UNION ALL SELECT 60 UNION ALL SELECT 70
  UNION ALL SELECT 80 UNION ALL SELECT 90
)
SELECT
  score,
  RANK() OVER (ORDER BY score) AS simple_rank,
  PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
  CUME_DIST() OVER (ORDER BY score) AS cum_distribution
FROM scores
ORDER BY score;