MySQL 8.0 Window Functions: ROW_NUMBER, LAG, LEAD, and Running Totals

Window functions, added in MySQL 8.0, enable powerful analytical queries that previously required complex self-joins or application-level processing.

What Are Window Functions?

Window functions compute a value for each row based on a set of related rows (the window), without collapsing rows like GROUP BY does.

ROW_NUMBER, RANK, DENSE_RANK

-- Rank customers by total spend within each region
SELECT
  customer_id, region, total_spend,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_spend DESC) AS row_num,
  RANK()       OVER (PARTITION BY region ORDER BY total_spend DESC) AS rank_pos,
  DENSE_RANK() OVER (PARTITION BY region ORDER BY total_spend DESC) AS dense_rank
FROM customer_summary
ORDER BY region, total_spend DESC;

Running Totals with SUM() OVER

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

LAG and LEAD: Compare Rows

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month), 2
  ) AS mom_pct_change
FROM monthly_revenue
ORDER BY month;

NTILE: Percentile Buckets

-- Divide customers into quartiles by spend
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customers
ORDER BY total_spend DESC;

FIRST_VALUE / LAST_VALUE

-- For each order, show the customer's first and latest order amount
SELECT
  customer_id, order_date, amount,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  LAST_VALUE(amount)  OVER w AS latest_order_amount
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY customer_id, order_date;

Make Ordering Deterministic

The ORDER BY inside OVER defines analytical order; the outer ORDER BY only controls presentation. If the window order contains ties, ROW_NUMBER() may assign peers in either order. Add a stable unique tie-breaker whenever downstream logic selects one row, paginates, or compares adjacent records. RANK() intentionally leaves gaps after ties, while DENSE_RANK() does not. Decide which business meaning is required and test duplicate sort values instead of relying on a sample where every timestamp happens to be unique.

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

Specify Aggregate Frames Explicitly

With an ordered window and no frame clause, MySQL's default is equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That frame includes peers with the same ordering value, so a running total can jump by several rows at once. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when the calculation should advance one physical row at a time, paired with a deterministic order. Conversely, whole-partition values need an unbounded following boundary. Adding ORDER BY later can change both determinism and the default frame, so frame behavior belongs in code review.

Handle Missing Neighbors and Zero Denominators

LAG and LEAD return their default value, normally NULL, when the requested neighbor does not exist. Preserve that distinction unless the business definition truly treats missing history as zero. Calculate the prior value once in a CTE or derived table and guard percentage divisions with NULLIF; repeating a window expression makes logic harder to review and can hide inconsistent casts. Also define whether gaps in dates mean compare with the previous observed row or with a calendar period. Window functions operate on rows present after earlier query processing; they do not manufacture missing dates.

WITH monthly AS (
  SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prior_revenue
  FROM monthly_revenue
)
SELECT month, revenue, prior_revenue,
       100.0 * (revenue - prior_revenue) / NULLIF(prior_revenue, 0) AS pct_change
FROM monthly;

Filter in a Later Query Block

Window results are not available to the same query block's WHERE clause. Compute them in a CTE or derived table, then filter in the outer query, as in the latest-order example. MySQL also does not support nested window functions or DISTINCT inside aggregate window functions, and window functions cannot directly provide values used to update rows in an UPDATE or DELETE; a selecting subquery can be used where appropriate. IGNORE NULLS, GROUPS frames, and frame EXCLUDE are parsed or standardized elsewhere but are not supported features in MySQL 8.4.

Plan and Resource Checks

Window processing may require sorting and internal temporary tables. Reuse one named window for functions with identical partitioning and ordering because MySQL does not merge equivalent window definitions automatically, while it can process windows with the same ordering consecutively. Use EXPLAIN and production-like data to inspect sort and temporary-table behavior; test partitions with heavy skew because one very large customer or tenant can dominate memory and latency. A composite index matching filters, partition keys, and ordering may reduce work, but verify the actual plan rather than assuming it eliminates every sort.

Validation Matrix

Test empty input, one-row partitions, ties, null measures, zero prior values, duplicate dates, missing periods, and the first and last row of every frame. Compare totals with a trusted grouped query and assert exactly one selected row per partition where required. Window SQL is concise; correctness depends on making its peer, frame, and boundary rules explicit.

Official MySQL References

• Window function concepts and ordering
• Window frame defaults and peer behavior
• LAG, LEAD, and ranking function behavior
• MySQL window function restrictions
• Window function optimization