Performance 10 min readMar 2025

Window Functions in SQL: The Complete Guide with Real Examples

ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE — window functions are the most powerful and underused feature in SQL. This guide covers every function with practical, production-ready examples.

Window Functions T-SQL Analytics Advanced SQL

What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row — without collapsing them into a single aggregate. Unlike GROUP BY, which reduces multiple rows to one, window functions preserve every row while adding a computed column.

The syntax follows a consistent pattern:

sql

function_name() OVER (
  [PARTITION BY column1, column2]
  [ORDER BY column3 DESC]
  [ROWS/RANGE BETWEEN ...]
)

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition.

sql

-- Get the most recent order per customer
SELECT customer_id, order_id, created_at,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders
-- Wrap in CTE to filter:
-- WHERE rn = 1

RANK() vs DENSE_RANK()

RANK leaves gaps after ties; DENSE_RANK does not.

sql

SELECT product_name, revenue,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_with_gaps,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS rank_no_gaps
FROM products;

-- If two products tie at position 2:
-- RANK:       1, 2, 2, 4   (gap at 3)
-- DENSE_RANK: 1, 2, 2, 3   (no gap)

NTILE(n)

Divides rows into n equal buckets — useful for percentile analysis.

sql

-- Divide customers into 4 revenue quartiles
SELECT customer_id, total_revenue,
  NTILE(4) OVER (ORDER BY total_revenue DESC) AS quartile
FROM customer_summary;

Offset Functions

LAG() and LEAD()

Access values from previous or following rows without a self-join.

sql

-- Month-over-month revenue change
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 mom_change
FROM monthly_revenue;

FIRST_VALUE() and LAST_VALUE()

sql

-- Compare each order to the customer's first order amount
SELECT customer_id, order_id, amount,
  FIRST_VALUE(amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS first_order_amount
FROM orders;

Aggregate Window Functions

Any aggregate function can be used as a window function by adding OVER().

sql

-- Each order's amount as a percentage of the customer's total
SELECT customer_id, order_id, amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
  ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 2) AS pct_of_total
FROM orders;

Running Totals and Moving Averages

sql

-- 7-day moving average of daily revenue
SELECT date, daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(daily_revenue) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_revenue;

Performance Considerations

Window functions are generally efficient, but watch for:

1. **Missing ORDER BY in OVER()** — required for ranking and offset functions; without it, results are non-deterministic.

2. **Large PARTITION BY cardinality** — partitioning on a high-cardinality column (e.g., user_id with millions of users) can be memory-intensive.

3. **Multiple passes** — each window function may require a separate sort pass. Reuse the same OVER() clause where possible.

SQL Querywise Advisor detects inefficient window function usage and suggests optimisations, including index recommendations for the ORDER BY columns in your OVER() clauses.

*SQL Querywise Advisor analyses window function usage and recommends performance improvements automatically.*

Try SQL Querywise on your own queries

3 free analyses — DocGen, Advisor, Reviewer, Explainer, Converter, and Analysis. No credit card required.

Try the live demo