SQL Window Functions Explained: Complete Tutorial

The Problem They Solve

You have a list of orders and want to add a column showing each row's running total. Without window functions, you write a self-join on the order date — ugly, slow, and unreadable. With window functions:

SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

That single line replaces 20 lines of subquery hell and runs in one pass. This pattern shows up everywhere: ranking, percentiles, "compare to previous", "top N per group" — all became one-liners once window functions arrived.

Anatomy of OVER()

function(args) OVER ( PARTITION BY column -- optional: divide rows into groups ORDER BY column -- optional: define order within group ROWS BETWEEN... -- optional: define the window frame
)

Without PARTITION BY, the entire result set is one window. Add PARTITION BY to compute per-group. Add ORDER BY for anything that depends on row order (running totals, ranking, LAG/LEAD).

Ranking: ROW_NUMBER, RANK, DENSE_RANK

SELECT product, category, price, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn, RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rk, DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dr
FROM products;

For "top 3 products per category", wrap this in a CTE and filter WHERE rn <= 3. This single pattern replaces every "top N per group" hack you've ever written.

LAG and LEAD: Previous and Next Row

Compute differences between consecutive rows — daily revenue change, time between events, anything time-series:

SELECT order_date, daily_revenue, LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day, daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS delta
FROM daily_sales;

LAG(col, n) returns the value n rows back; LEAD(col, n) looks forward. Use them with PARTITION BY user_id for per-user time series.

Running Totals and Moving Averages

-- Running total
SUM(amount) OVER (ORDER BY date) -- Per-user running total
SUM(amount) OVER (PARTITION BY user_id ORDER BY date) -- 7-day moving average
AVG(amount) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

The ROWS BETWEEN clause defines the window frame — the slice of rows the function operates on. Default is "from the start to the current row" when ORDER BY is present; specify explicitly for moving averages.

Any Aggregate Can Be a Window Function

SUM, AVG, COUNT, MIN, MAX, STRING_AGG — all of them. The trick is using OVER() to keep the original rows:

SELECT order_id, user_id, amount, AVG(amount) OVER (PARTITION BY user_id) AS user_avg, amount - AVG(amount) OVER (PARTITION BY user_id) AS deviation
FROM orders;

Each row now shows its own amount, the user's average, and how much it deviates — in one query, no joins.

NTILE for Buckets

NTILE(N) divides rows into N approximately equal buckets — useful for quartiles, deciles, A/B test cohorts:

SELECT user_id, total_spent, NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_totals;

Performance Notes

Window functions are computed after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. Filter as early as possible — every row that survives WHERE becomes work for the window function. For huge tables, an index on the PARTITION BY columns dramatically speeds things up by letting the database compute each partition in isolation.

Practice Pattern

The fastest way to internalize window functions is to take queries you've written with self-joins or subqueries and rewrite them. Most "find users whose latest order was in the last 30 days" or "rank products by revenue per category" queries collapse to a CTE plus a window function. Try it on your own codebase — the rewrite is usually 60-80% shorter and faster. Pair with the online SQL editor to run examples instantly.