SQL Window Functions Explained: The Complete Guide for Data Analysts

Window functions are the most powerful SQL feature that separates junior analysts from senior ones. They come up in almost every technical interview at top companies — and most candidates fail them completely.

This guide teaches every important window function with real examples, interview questions, and the exact patterns that get asked at Deloitte, Amazon, and McKinsey.

What Is a Window Function?

A window function performs a calculation across a set of rows related to the current row — without collapsing them like GROUP BY does. You keep all your original rows and get the calculation.

The key is the OVER() clause:

-- GROUP BY: collapses to one row per department
SELECT dept, AVG(salary)
FROM employees
GROUP BY dept;

-- Window function: keeps all rows, adds avg per dept
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

The second query gives every employee's name, their salary, AND their department average — all in one row. That's the window function difference.

PARTITION BY vs ORDER BY

PARTITION BY divides rows into groups (without collapsing). ORDER BY controls the order within each group — essential for ranking and running totals.

-- Running total of sales, reset per product
SELECT product, sale_date, amount,
       SUM(amount) OVER (
         PARTITION BY product
         ORDER BY sale_date
         ROWS UNBOUNDED PRECEDING
       ) AS running_total
FROM sales;

ROW_NUMBER, RANK, and DENSE_RANK

The most common ranking functions — and the most misunderstood. They behave differently when there are ties:

SELECT name, dept, salary,
       RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

LAG and LEAD — Period Comparisons

LAG gets the value from a previous row. LEAD gets the value from a future row. Both are critical for month-over-month or year-over-year comparisons.

-- Month-over-month revenue change
SELECT month, revenue,
       LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
             / LAG(revenue, 1) OVER (ORDER BY month), 1) AS pct_change
FROM monthly_revenue;

NTILE — Percentile Buckets

NTILE divides rows into N equal buckets. Perfect for quartile analysis, top/bottom 10%, or customer segmentation.

-- Divide customers into 4 spending quartiles
SELECT customer_id, total_spend,
       NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customers;
-- Quartile 1 = top 25% spenders

The Classic Interview Question

Here's one of the most common window function interview questions:

"Write a query to find the second highest salary in each department."

WITH ranked AS (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rnk = 2;

DENSE_RANK is used here (not RANK) so that if two people share the top salary, the second distinct salary is still ranked 2.

Practice These Until They're Automatic

The analysts who get hired aren't necessarily the smartest — they're the most practiced. Use LeetCode Database problems (filter by window functions), Mode Analytics SQL Tutorial, and our SQL Cheat Sheet to drill these patterns until they feel natural.