SQL Window Functions for Beginners: ROW_NUMBER, RANK, LAG & Running Totals

What Makes Window Functions Different

Normal aggregation (GROUP BY) collapses rows — 10 sales rows become 1 summary row. Window functions keep every row and compute a value across a set of related rows. That's why they're the tool for rankings, running totals, and "previous row" comparisons: the detail stays visible.

The Syntax: OVER() Explained

SELECT employee_id, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees; — the window function runs after FROM/WHERE/GROUP BY but before ORDER BY. Three parts matter: the function (RANK, ROW_NUMBER...), PARTITION BY (split into groups), and ORDER BY (ordering within each group). PARTITION BY department resets the ranking per department; omitting it ranks across the whole table.

ROW_NUMBER vs RANK vs DENSE_RANK

All three number rows, but ties differ: ROW_NUMBER() assigns unique sequential numbers (ties broken arbitrarily — add ORDER BY columns to make it deterministic). RANK() gives ties the same number and skips: 1,2,2,4. DENSE_RANK() gives ties the same number but doesn't skip: 1,2,2,3. Use ROW_NUMBER for pagination/dedup, RANK for "top N per group" with ties, DENSE_RANK for dense leaderboards.

LAG and LEAD: Comparing to Neighbors

LAG(salary, 1) OVER (ORDER BY hire_date) returns the previous row's salary — the foundation of month-over-month growth, price deltas, and session analysis. LEAD looks forward instead. Both accept an offset (default 1) and a default value for the boundary rows: LAG(salary, 1, 0) fills the first row with 0 instead of NULL. Combined with salary - LAG(salary) OVER (...) you get per-row deltas — see our month-over-month guide for the full pattern.

Running Totals and Moving Averages

Sum with ORDER BY inside OVER() creates a running total: SUM(amount) OVER (ORDER BY order_date) adds up progressively. Add PARTITION BY for per-customer running totals. Moving averages use the frame clause: AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — a 7-day rolling average. The frame (ROWS BETWEEN ... AND ...) is the part most people never learn; it's what separates basic window usage from real analytics. Our running total guide covers the classic cases.

Window Functions vs GROUP BY: When to Use What

GROUP BY collapses rows and works with any aggregate; window functions keep rows and work with aggregates plus ranking/offset functions. Rule of thumb: if the output needs the original rows with a computed value alongside, use a window function; if it needs one row per group, use GROUP BY. A common hybrid: aggregate with GROUP BY into a CTE, then window-function over the CTE — that's how you rank groups ("top 3 departments by sales").

Try It Yourself

Type "rank employees by salary within each department" into our free SQL generator and pick your dialect — you'll get a working window query with the PARTITION BY already in place.