SQL Window Functions: Ranking, Running Totals, and Analytics

Published: 2026-03-24

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into a single output row like GROUP BY does. They are one of the most powerful features in modern SQL, supported in SQLite (3.25+), PostgreSQL, MySQL (8.0+), SQL Server, and Oracle.

Window Function Syntax

Every window function uses the OVER clause to define which rows to operate on:

function_name(...) OVER (
  [PARTITION BY column]
  [ORDER BY column]
  [frame_clause]
)

• PARTITION BY — divides rows into groups (like GROUP BY, but keeps all rows)
• ORDER BY — defines row ordering within each partition
• Frame clause — limits the window to a range of rows relative to the current row

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a partition. Numbers always start at 1 with no gaps or ties.

Example: Number All Employees by Salary

Example: Number Employees Within Each Department

Using PARTITION BY, numbering restarts at 1 for each department:

RANK() and DENSE_RANK()

RANK() and DENSE_RANK() handle ties differently than ROW_NUMBER():

• ROW_NUMBER() — always unique, arbitrary tiebreak
• RANK() — same value gets the same rank, then skips numbers (1, 2, 2, 4)
• DENSE_RANK() — same value gets the same rank, no gaps (1, 2, 2, 3)

Example: Compare All Three Ranking Functions

Bob and Carol both earn 85000 — notice how each function handles the tie:

Example: Rank Employees Within Department

NTILE()

NTILE(n) distributes rows into n approximately equal groups (buckets), numbered 1 through n.

Example: Divide Employees into Salary Quartiles

Example: Split Employees into Two Tiers per Department

LAG() and LEAD()

LAG(column, offset) accesses a value from a previous row. LEAD(column, offset) accesses a value from a following row. Both default to offset 1 and return NULL when there is no row.

Example: Month-over-Month Revenue Comparison

Compare each month’s revenue with the previous month for each employee:

Example: Preview Next Month’s Revenue

Aggregate Window Functions

Standard aggregate functions (SUM, AVG, COUNT, MIN, MAX) can be used as window functions with OVER. Unlike GROUP BY, every row is preserved in the output.

Example: Each Employee’s Salary vs. Department Average

Example: Percentage of Total Salary

Calculate what fraction of the total payroll each employee represents:

Example: Running Total and Cumulative Count

Running Totals per Partition

Combine PARTITION BY and ORDER BY to compute running totals within groups.

Example: Cumulative Revenue per Employee

Example: Monthly Revenue Rank Across All Employees

Find how each employee ranked each month:

Window Frames

A window frame defines exactly which rows relative to the current row are included in the calculation. The frame clause comes after ORDER BY inside OVER.

Syntax

ROWS BETWEEN <start> AND <end>

Common boundaries:

• UNBOUNDED PRECEDING — from the first row of the partition
• N PRECEDING — N rows before the current row
• CURRENT ROW — the current row
• N FOLLOWING — N rows after the current row
• UNBOUNDED FOLLOWING — to the last row of the partition

Example: 2-Month Moving Average

Example: Running Min and Max Revenue

FIRST_VALUE and LAST_VALUE

FIRST_VALUE(column) returns the first value in the window frame. LAST_VALUE(column) returns the last value. Be careful: LAST_VALUE with the default frame (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) only sees up to the current row.

Example: Compare Each Month to the First Month

Example: Compare Each Month to Best Month

Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to see the full partition:

Practical Example: Top Earner per Department

A common pattern: use a window function in a subquery, then filter by rank.

Example: Highest-Paid Employee in Each Department

Example: Top Revenue Month per Employee

Window Functions vs. GROUP BY

A key difference: GROUP BY collapses rows into summary rows, while window functions keep every row and add a computed column.

Example: Side-by-Side Comparison

GROUP BY returns one row per department:

Window functions keep all rows:

Common Use Cases for Window Functions

• Rankings and leaderboards — ROW_NUMBER, RANK, DENSE_RANK for top-N queries
• Running totals — Cumulative SUM for financial and inventory tracking
• Period-over-period comparison — LAG/LEAD for month-over-month or year-over-year analysis
• Moving averages — Smoothing data with frame-based AVG
• Percentile analysis — NTILE for splitting data into equal groups
• Deduplication — ROW_NUMBER to pick one row per group
• Gap analysis — LAG to detect missing sequences or unusual changes

Performance Tips

1. Index ORDER BY columns — Window functions sort data internally; indexes help
2. Minimize partitions — Large numbers of partitions increase computation
3. Reuse window definitions — Many databases support WINDOW clauses to avoid repeating the same OVER specification
4. Filter after windowing — Window functions run before WHERE, so use subqueries or CTEs to filter by rank
5. Avoid unnecessary frames — Only specify frame clauses when you need non-default behavior

Summary

• Window functions compute values across related rows without collapsing the result set
• ROW_NUMBER, RANK, DENSE_RANK assign ordinal positions to rows
• LAG and LEAD access values from previous or following rows
• NTILE distributes rows into equal-sized buckets
• FIRST_VALUE and LAST_VALUE return boundary values from the window
• Aggregate functions (SUM, AVG, COUNT, MIN, MAX) with OVER keep all rows while computing group-level metrics
• PARTITION BY creates independent groups; ORDER BY defines row sequence
• Window frames (ROWS BETWEEN ... AND ...) fine-tune which rows are included
• Use subqueries to filter by window function results (e.g., top-N per group)
• Supported in SQLite 3.25+, PostgreSQL, MySQL 8.0+, SQL Server, and Oracle

Try modifying the examples to experiment with different partitions, orderings, and frame clauses to see how window functions can simplify your analytical queries!