Window Functions Explained: RANK, LAG, LEAD, and Running Totals

Window functions let you calculate across related rows without collapsing them into a single output row. This guide covers RANK(), LAG(), LEAD(), and cumulative sums with working SQL and PySpark code, plus a comparison of ranking functions and a set of production patterns.

Quick Takeaways

• Window functions keep every row. Unlike GROUP BY, they add a computed column alongside the original data.
• PARTITION BY defines the group; ORDER BY defines the sequence. Most bugs come from missing or incomplete ORDER BY clauses.
• LAG() and LEAD() replace self-joins for period-over-period comparisons.
• Running totals depend on the frame clause. The default frame changes when you add ORDER BY, and that default can produce wrong results on ties.

What Is a Window Function?

A window function performs a calculation over a set of rows (“the window”) related to the current row. The output keeps the original row count.

The general syntax:

function_name(expression) OVER (
    PARTITION BY partition_column
    ORDER BY sort_column
    ROWS BETWEEN frame_start AND frame_end
)

Each clause does one job:

• PARTITION BY splits the data into independent groups. Omit it and the whole table is one partition.
• ORDER BY sorts rows inside each partition. Ranking and offset functions require it.
• Frame clause (ROWS or RANGE) limits which rows inside the partition feed the calculation.

Window Functions vs. GROUP BY

Sample Dataset

The examples below use a sales table. Create it once and run every query against it.

CREATE TABLE sales (
    sale_id     INT,
    region      VARCHAR(20),
    sales_rep   VARCHAR(30),
    sale_month  DATE,
    revenue     DECIMAL(10,2)
);

INSERT INTO sales VALUES
(1, 'West', 'Ana',   '2026-01-01', 12000),
(2, 'West', 'Ben',   '2026-01-01', 12000),
(3, 'West', 'Cara',  '2026-01-01', 9500),
(4, 'East', 'Dev',   '2026-01-01', 15000),
(5, 'East', 'Elle',  '2026-01-01', 11000),
(6, 'West', 'Ana',   '2026-02-01', 14000),
(7, 'West', 'Ben',   '2026-02-01', 10500),
(8, 'East', 'Dev',   '2026-02-01', 13000),
(9, 'East', 'Elle',  '2026-02-01', 16500),
(10,'West', 'Ana',   '2026-03-01', 15500),
(11,'East', 'Dev',   '2026-03-01', 17000);

RANK(): Ordering With Ties

RANK() assigns a position to each row within its partition. Tied rows share a rank, and the next rank skips ahead by the number of ties.

SELECT
    region,
    sales_rep,
    revenue,
    -- Rank reps by revenue within each region, highest first
    RANK() OVER (
        PARTITION BY region
        ORDER BY revenue DESC
    ) AS revenue_rank
FROM sales
WHERE sale_month = '2026-01-01';

In the West region, Ana and Ben both earned 12,000. They share rank 1. Cara receives rank 3, not 2.

RANK vs. DENSE_RANK vs. ROW_NUMBER

Choose based on the business rule:

• Use ROW_NUMBER() for deduplication, where you need exactly one row per key.
• Use RANK() for competition-style leaderboards.
• Use DENSE_RANK() when you want “the top 3 distinct values.”

Top-N Per Group

You cannot filter on a window function in WHERE, because window functions run after WHERE is evaluated. Wrap the query in a CTE.

WITH ranked AS (
    SELECT
        region,
        sales_rep,
        sale_month,
        revenue,
        DENSE_RANK() OVER (
            PARTITION BY region, sale_month
            ORDER BY revenue DESC
        ) AS rnk
    FROM sales
)
SELECT *
FROM ranked
WHERE rnk <= 2;  -- Top 2 distinct revenue levels per region per month

Some engines (Snowflake, BigQuery, Teradata, DuckDB) support the QUALIFY clause, which removes the CTE:

SELECT region, sales_rep, revenue
FROM sales
QUALIFY RANK() OVER (PARTITION BY region ORDER BY revenue DESC) <= 2;

LAG() and LEAD(): Row Offsets Without Self-Joins

LAG() reads a value from a previous row. LEAD() reads from a following row. Both accept three arguments:

LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

• column: the value to retrieve.
• offset: how many rows to move. The default is 1.
• default_value: returned when no row exists at that offset. The default is NULL.

Month-Over-Month Growth With LAG()

WITH monthly AS (
    SELECT
        region,
        sale_month,
        SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY region, sale_month
)
SELECT
    region,
    sale_month,
    total_revenue,
    -- Pull the previous month's revenue within the same region
    LAG(total_revenue, 1) OVER (
        PARTITION BY region
        ORDER BY sale_month
    ) AS prev_month_revenue,
    -- Percent change; NULLIF prevents division by zero
    ROUND(
        100.0 * (total_revenue - LAG(total_revenue) OVER (PARTITION BY region ORDER BY sale_month))
        / NULLIF(LAG(total_revenue) OVER (PARTITION BY region ORDER BY sale_month), 0),
        2
    ) AS mom_growth_pct
FROM monthly
ORDER BY region, sale_month;

The first month in each region returns NULL for prev_month_revenue, since no prior row exists.

Forecasting Setup With LEAD()

LEAD() is the natural choice for building supervised learning targets, such as “next month’s revenue”:

SELECT
    region,
    sale_month,
    total_revenue,
    LEAD(total_revenue, 1) OVER (
        PARTITION BY region
        ORDER BY sale_month
    ) AS next_month_revenue  -- Target variable for a forecasting model
FROM (
    SELECT region, sale_month, SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY region, sale_month
) m;

LAG vs. LEAD

Warning: Never use LEAD() output as a model feature. It exposes future information and causes data leakage.

Running Totals With SUM() OVER

A running total accumulates a value across ordered rows.

SELECT
    region,
    sale_month,
    SUM(revenue) AS monthly_revenue,
    -- Cumulative revenue per region, ordered by month
    SUM(SUM(revenue)) OVER (
        PARTITION BY region
        ORDER BY sale_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales
GROUP BY region, sale_month
ORDER BY region, sale_month;

The nested SUM(SUM(revenue)) works because the inner aggregate runs first under GROUP BY, and the outer one runs as a window function over those grouped rows.

ROWS vs. RANGE: The Tie Trap

When you add ORDER BY without a frame clause, most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with equal ORDER BY values as peers and includes all of them. ROWS counts physical rows.

Always state the frame explicitly. It removes ambiguity and often improves performance, because ROWS avoids peer-group lookups.

Moving Averages

Change the frame to create a sliding window:

SELECT
    region,
    sale_month,
    revenue,
    -- 3-row trailing average
    AVG(revenue) OVER (
        PARTITION BY region
        ORDER BY sale_month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM sales;

Frame Clause Reference

Window Functions in Python

Analysts working in dataframes get the same logic from Pandas and PySpark.

Pandas

import pandas as pd

df = pd.read_csv("sales.csv", parse_dates=["sale_month"])

# Aggregate to region-month grain first
monthly = (
    df.groupby(["region", "sale_month"], as_index=False)["revenue"]
      .sum()
      .sort_values(["region", "sale_month"])
)

g = monthly.groupby("region")["revenue"]

monthly["rank"] = g.rank(method="min", ascending=False)  # method="min" mimics SQL RANK()
monthly["prev_revenue"] = g.shift(1)                     # LAG(1)
monthly["next_revenue"] = g.shift(-1)                    # LEAD(1)
monthly["running_total"] = g.cumsum()                    # Running total
monthly["mom_growth_pct"] = g.pct_change() * 100         # Period-over-period change
monthly["moving_avg_3"] = g.rolling(3, min_periods=1).mean().reset_index(level=0, drop=True)

Rank method mapping:

PySpark

from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("window_demo").getOrCreate()
df = spark.read.csv("sales.csv", header=True, inferSchema=True)

monthly = df.groupBy("region", "sale_month").agg(F.sum("revenue").alias("revenue"))

# Ordered window for LAG/LEAD
w_order = Window.partitionBy("region").orderBy("sale_month")

# Explicit frame for the running total
w_running = (
    Window.partitionBy("region")
          .orderBy("sale_month")
          .rowsBetween(Window.unboundedPreceding, Window.currentRow)
)

result = (
    monthly
    .withColumn("prev_revenue", F.lag("revenue", 1).over(w_order))
    .withColumn("next_revenue", F.lead("revenue", 1).over(w_order))
    .withColumn("running_total", F.sum("revenue").over(w_running))
    .withColumn(
        "rank",
        F.rank().over(Window.partitionBy("region").orderBy(F.desc("revenue")))
    )
)

result.show()

Performance note: Window functions in Spark trigger a shuffle on the partition key. A window with no partitionBy pulls all data into one partition on a single executor. Always partition on a column with sufficient cardinality.

Real-World Use Cases

Customer Churn: Days Since Last Purchase

Use LAG() to compute recency, a core feature in churn models:

SELECT
    customer_id,
    order_date,
    order_date - LAG(order_date) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS days_since_prev_order
FROM orders;

Customers whose gap exceeds their own historical average are churn candidates.

Sensor Streams: Detecting Spikes

Compare each reading to its predecessor to flag anomalies:

SELECT
    sensor_id,
    reading_ts,
    temperature,
    temperature - LAG(temperature) OVER (
        PARTITION BY sensor_id ORDER BY reading_ts
    ) AS delta
FROM sensor_readings;

Filter on ABS(delta) above a threshold derived from the sensor’s rolling standard deviation.

Sessionization

Split a clickstream into sessions when the gap between events exceeds 30 minutes:

WITH gaps AS (
    SELECT
        user_id,
        event_ts,
        CASE
            WHEN event_ts - LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts)
                 > INTERVAL '30 minutes'
            THEN 1 ELSE 0
        END AS new_session
    FROM events
)
SELECT
    user_id,
    event_ts,
    -- Running total of session flags produces a session ID
    SUM(new_session) OVER (
        PARTITION BY user_id
        ORDER BY event_ts
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_id
FROM gaps;

This pattern combines LAG() with a running SUM() and appears constantly in product analytics.

Missing Data: Forward Fill

Fill nulls with the last known value in Pandas:

df["value"] = df.groupby("sensor_id")["value"].ffill()

In SQL, use LAST_VALUE(value IGNORE NULLS) OVER (...) on engines that support it, such as Snowflake, BigQuery, and Oracle.

Common Mistakes and Fixes

Performance Optimization

• Index or cluster on partition and order columns. The engine sorts within each partition, so matching physical order cuts cost.
• Reuse named windows. The WINDOW clause avoids repeating definitions and lets the optimizer share one sort:

SELECT
    region, sale_month, revenue,
    LAG(revenue) OVER w AS prev_revenue,
    SUM(revenue) OVER w AS running_total
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_month);

• Filter early. Apply WHERE clauses before the window runs to shrink the sorted dataset.
• Prefer ROWS over RANGE when ties are irrelevant.
• Materialize intermediate results for repeated use in large pipelines.

Frequently Asked Questions

What is the difference between RANK() and DENSE_RANK()?

RANK() leaves gaps in the sequence after ties (1, 1, 3). DENSE_RANK() does not (1, 1, 2). Use DENSE_RANK() when you need “the top N distinct values.”

Can I use a window function in a WHERE clause?

No. SQL evaluates WHERE before window functions. Place the window function in a CTE or subquery and filter the outer query, or use QUALIFY where supported.

How do I calculate a running total in SQL?

Use SUM(column) OVER (PARTITION BY group ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Specify the ROWS frame explicitly to avoid tie-related errors from the default RANGE behavior.

What does LAG() return for the first row?

LAG() returns NULL for the first row in each partition, since no previous row exists. Pass a third argument, such as LAG(revenue, 1, 0), to substitute a default value.