🔴 Advanced · Topic 5 of 7 · Most Tested
SQL Window Functions — Complete Guide
The most tested SQL topic in senior data analyst interviews. Master RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, SUM OVER and running totals with real company examples.
Core Syntax
The OVER() Clause — How Window Functions Work
Every window function uses OVER() to define the “window” — the set of rows it calculates across. PARTITION BY divides data into groups. ORDER BY defines the order within each group.
Key difference from GROUP BY: Window functions add a new column to each existing row — they do NOT reduce the number of rows. GROUP BY collapses rows. Window functions keep all rows and add calculations alongside them.
SQL — OVER() Syntax
FUNCTION() OVER (
PARTITION BY column — divide into groups (optional)
ORDER BY column — sort within each group
ROWS BETWEEN … — define frame (optional)
)
— Example: rank employees by salary within each department
RANK() OVER (PARTITION BY department ORDER BY salary DESC)
Ranking Functions
RANK, DENSE_RANK, ROW_NUMBER
| Function | Handles Ties | Example output |
|---|---|---|
ROW_NUMBER() | No ties — always unique | 1, 2, 3, 4, 5 |
RANK() | Same rank for ties, skips next | 1, 1, 3, 4, 5 |
DENSE_RANK() | Same rank for ties, no skip | 1, 1, 2, 3, 4 |
SQL — Ranking Window Functions
SELECT
emp_name, department, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank_,
DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dense_rank_,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
— Top 1 per group (use ROW_NUMBER for exactly 1)
WITH r AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) rn
FROM employees
)
SELECT * FROM r WHERE rn = 1;
Interview rule: Use ROW_NUMBER when you need EXACTLY 1 row per group (strict top-1). Use RANK/DENSE_RANK when ties should both be included. This distinction is frequently tested.
Time-Series Functions
LAG & LEAD — Compare with Previous/Next Rows
SQL — LAG & LEAD
SELECT
month,
revenue,
LAG(revenue, 1) OVER(ORDER BY month) AS prev_month_revenue,
LEAD(revenue, 1) OVER(ORDER BY month) AS next_month_target,
ROUND(
(revenue – LAG(revenue,1) OVER(ORDER BY month))
* 100.0
/ LAG(revenue,1) OVER(ORDER BY month),
2
) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;
LAG and LEAD syntax: LAG(column, offset, default). Offset is how many rows back (default 1). Default is the value when there’s no previous row — use 0 for revenue calculations to avoid NULL issues.
Running Calculations
Running Totals & Moving Averages
SQL — Running Totals & Moving Average
SELECT
order_date,
daily_revenue,
SUM(daily_revenue) OVER(ORDER BY order_date) AS cumulative_revenue,
AVG(daily_revenue) OVER(
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7d_avg
FROM daily_sales;
ROWS BETWEEN frame: Defines the window boundaries. ‘6 PRECEDING AND CURRENT ROW’ means the current row + 6 rows before it — a 7-day window. This is the standard rolling/moving average pattern.
Interview Questions
Practice Questions — Window Functions
Medium
Rank all employees by salary within each department. Show department, name, salary and rank.
AmazonPaytm
+
Use RANK() with PARTITION BY department — a foundational window function pattern every analyst must know.
SELECT
department, emp_name, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) salary_rank
FROM employees
ORDER BY department, salary_rank;
department, emp_name, salary,
RANK() OVER(PARTITION BY department ORDER BY salary DESC) salary_rank
FROM employees
ORDER BY department, salary_rank;
Hard
Calculate month-over-month revenue growth % for each month in 2024.
SwiggyZomatoPhonePe
+
Use LAG() to get previous month revenue, then calculate percentage change. Handle NULL for the first month.
SELECT month, revenue,
ROUND((revenue – LAG(revenue) OVER(ORDER BY month)) * 100.0
/ LAG(revenue) OVER(ORDER BY month), 2) mom_pct
FROM monthly_revenue WHERE year=2024;
ROUND((revenue – LAG(revenue) OVER(ORDER BY month)) * 100.0
/ LAG(revenue) OVER(ORDER BY month), 2) mom_pct
FROM monthly_revenue WHERE year=2024;
Hard
Find users active on 3+ consecutive days in the last 30 days.
GoogleMetaUber
+
Subtract ROW_NUMBER from date to create a ‘consecutive group’ — rows in the same group have the same grp value. Count each group.
WITH da AS (
SELECT DISTINCT user_id, DATE(login) dt FROM activity
WHERE login >= CURRENT_DATE-30
), grp AS (
SELECT user_id, dt,
dt – ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY dt) g
FROM da
)
SELECT DISTINCT user_id FROM grp
GROUP BY user_id, g HAVING COUNT(*)>=3;
SELECT DISTINCT user_id, DATE(login) dt FROM activity
WHERE login >= CURRENT_DATE-30
), grp AS (
SELECT user_id, dt,
dt – ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY dt) g
FROM da
)
SELECT DISTINCT user_id FROM grp
GROUP BY user_id, g HAVING COUNT(*)>=3;
Window Functions feel hard? We’ll fix that.
Book a free 1-on-1 mock session. Our mentors specialise in window function interview questions at top companies.
Book Free Session