SQL Window Functions: ROW_NUMBER, RANK, LAG & Running Totals Explained

GROUP BY is every SQL learner's first aggregation tool — and its biggest limitation. It collapses rows: you get the average per department, but lose the individual employees. Window functions solve this elegantly: they compute aggregates across rows while keeping every row intact.

If you write reports, dashboards, or analytics queries, window functions will change how you think in SQL. Let's build them up from first principles with a single orders table.

1. The Anatomy: OVER() and PARTITION BY

Every window function follows the same shape: the function, then OVER (...) defining the “window” of rows it can see. PARTITION BY splits rows into groups (like GROUP BY, but without collapsing):

-- total per customer, but keep every order row
SELECT order_id, customer_id, amount,
       SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
       SUM(amount) OVER () AS grand_total
FROM orders;

Notice: each order still appears as its own row, now carrying its customer's total and the grand total alongside. That is the magic — detail plus context in one pass.

2. Ranking: ROW_NUMBER, RANK, and DENSE_RANK

The classic “top-N per group” problem — top 3 products per category — is painful with GROUP BY and trivial with ranking functions:

SELECT category, product_name, revenue,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn,
       RANK()       OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS dense_rnk
FROM product_revenue;

-- top 3 per category: wrap it
SELECT * FROM (
  SELECT category, product_name, revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
  FROM product_revenue
) t WHERE rn <= 3;

Know the difference: ROW_NUMBER never ties (1,2,3,4), RANK leaves gaps on ties (1,2,2,4), DENSE_RANK does not (1,2,2,3).

3. Time Travel: LAG and LEAD

Month-over-month growth requires comparing each row with its neighbor. LAG peeks at the previous row, LEAD at the next — no self-joins needed:

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
       ROUND(
         (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month) * 100, 1
       ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

4. Running Totals and Moving Averages

Frames make windows even more powerful: ROWS BETWEEN defines exactly which rows participate — perfect for cumulative sums and rolling averages on dashboards:

SELECT day, sales,
       -- running total since the beginning
       SUM(sales) OVER (ORDER BY day
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       ) AS running_total,
       -- 7-day moving average
       AVG(sales) OVER (ORDER BY day
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
                       ) AS moving_avg_7d
FROM daily_sales
ORDER BY day;

5. Bucketing with NTILE

Need quartiles, deciles, or A/B test groups? NTILE(n) distributes rows into n roughly equal buckets — ideal for cohort analysis:

-- split customers into 4 spend quartiles
SELECT customer_id, total_spent,
       NTILE(4) OVER (ORDER BY total_spent DESC) AS spend_quartile
FROM customer_totals;

6. Five Mistakes Everyone Makes

Window functions are easy to start and easy to misuse. Watch for these:

• Forgetting ORDER BY inside OVER(): without it, row order is nondeterministic — your ROW_NUMBER() results will shuffle between runs.
• Using a window function in WHERE: it is illegal. Compute in a subquery or CTE first, then filter.
• Mixing up RANK and DENSE_RANK: pick deliberately — leaderboards usually want DENSE_RANK, pagination wants ROW_NUMBER.
• Windowing before filtering: apply WHERE clauses first so the window scans fewer rows on large tables.
• Ignoring NULLS: NULLs sort first in ascending order by default in PostgreSQL — add NULLS LAST when ranking metrics.

Frequently Asked Questions (FAQ)

Conclusion

Window functions turn SQL from a blunt aggregation tool into a precise analytical instrument: rankings, period comparisons, running totals, and cohorts — all while preserving row-level detail. Master OVER (), PARTITION BY, and frames, and most reporting queries become single, readable statements.