🪟 SQL Window Functions

Master advanced analytics with powerful window functions

What Are Window Functions?

Window functions perform calculations across a set of rows that are somehow related to the current row, but unlike aggregate functions, they don't collapse rows. Each row keeps its identity while accessing data from other rows in its "window."

❌ Aggregate Functions (GROUP BY)

SELECT department, AVG(salary) FROM employees GROUP BY department;
department avg
Sales5000
Engineering6000

⚠️ Rows are collapsed - you lose individual employee data

✅ Window Functions (OVER)

SELECT name, department, salary, AVG(salary) OVER ( PARTITION BY department ) AS dept_avg FROM employees;
name department salary dept_avg
AliceSales50005000
BobSales50005000
CharlieEngineering60006000

✓ All rows preserved - you keep individual employee data!

📊 Common Window Functions

ROW_NUMBER()

Assigns a unique sequential number to each row

ROW_NUMBER() OVER (ORDER BY salary DESC)

RANK()

Ranks rows with gaps after ties

RANK() OVER (ORDER BY score DESC)

DENSE_RANK()

Ranks rows without gaps

DENSE_RANK() OVER (ORDER BY score DESC)

LAG()

Access previous row's value

LAG(salary, 1) OVER (ORDER BY date)

LEAD()

Access next row's value

LEAD(price, 1) OVER (ORDER BY date)

SUM() OVER

Running total or windowed sum

SUM(amount) OVER (ORDER BY date)

AVG() OVER

Moving average or windowed average

AVG(sales) OVER (ORDER BY month)

NTILE(n)

Divide rows into n equal groups

NTILE(4) OVER (ORDER BY revenue)

🔲 PARTITION BY - Dividing Data into Groups

PARTITION BY divides rows into partitions (groups) to which the window function is applied separately.

SELECT name, department, salary, ROW_NUMBER() OVER ( PARTITION BY department ORDER BY salary DESC ) AS rank_in_dept, AVG(salary) OVER ( PARTITION BY department ) AS dept_avg FROM employees;
name department salary rank_in_dept dept_avg
Charlie Sales 5500 1 5000
Alice Sales 5000 2 5000
Eve Sales 4500 3 5000
Frank Engineering 6500 1 6100
Bob Engineering 6000 2 6100
Grace Engineering 5800 3 6100
Diana Marketing 4800 1 4800

💡 Key Point

Notice how ROW_NUMBER() restarts at 1 for each department! The PARTITION BY clause creates separate "windows" for Sales, Engineering, and Marketing. Each partition is ranked independently.

🏆 ROW_NUMBER vs RANK vs DENSE_RANK

Understanding the difference between ranking functions:

SELECT name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank FROM students;
name score ROW_NUMBER RANK DENSE_RANK
Alice 95 1 1 1
Bob 90 2 2 2
Charlie 90 3 2 2
Diana 85 4 4 3
Eve 80 5 5 4
Frank 80 6 5 4
Grace 75 7 7 5

ROW_NUMBER()

Always unique (1, 2, 3, 4, 5, 6, 7)

Even with ties, each row gets a unique number

RANK()

Same rank for ties, skips next (1, 2, 2, 4, 5, 5, 7)

Gaps appear after ties

DENSE_RANK()

Same rank for ties, no gaps (1, 2, 2, 3, 4, 4, 5)

No gaps in sequence

📈 Running Totals and Moving Averages

Window functions with ORDER BY create cumulative calculations:

SELECT date, sales, SUM(sales) OVER ( ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total, AVG(sales) OVER ( ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM daily_sales;
date sales running_total moving_avg_3
2024-01-01 100 100 100.0
2024-01-02 150 250 125.0
2024-01-03 200 450 150.0
2024-01-04 180 630 176.7
2024-01-05 220 850 200.0

💡 Understanding Window Frames

Running Total: UNBOUNDED PRECEDING AND CURRENT ROW means "from the start of the partition to the current row"

Moving Average: 2 PRECEDING AND CURRENT ROW means "current row plus 2 rows before it" (3-day average)

⬅️➡️ LAG and LEAD - Access Adjacent Rows

LAG() and LEAD() let you access values from previous or next rows:

SELECT month, revenue, LAG(revenue, 1) OVER ( ORDER BY month ) AS prev_month_revenue, LEAD(revenue, 1) OVER ( ORDER BY month ) AS next_month_revenue, revenue - LAG(revenue, 1) OVER ( ORDER BY month ) AS month_over_month_change FROM monthly_revenue;
month revenue prev_month_revenue next_month_revenue change
2024-01 10000 NULL 12000 NULL
2024-02 12000 10000 11500 +2000
2024-03 11500 12000 13000 -500
2024-04 13000 11500 14500 +1500
2024-05 14500 13000 NULL +1500

💡 Common Use Cases

  • LAG: Calculate month-over-month changes, compare with previous values
  • LEAD: Look ahead to forecast trends, compare with future values
  • The second parameter (1, 2, etc.) specifies how many rows to skip
  • Returns NULL when there's no previous/next row

🪟 Window Frame Specification

Control exactly which rows are included in your window:

ROWS BETWEEN

Physical offset based on row position

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Includes: previous row, current row, next row

RANGE BETWEEN

Logical offset based on value ranges

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

All rows from start to current

UNBOUNDED PRECEDING

Start of the partition

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Entire partition

CURRENT ROW

The current row being processed

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Current row to end

Visual Example: 3-Row Window

Row 1
Row 2 ← PRECEDING
Row 3 ← CURRENT
Row 4 ← FOLLOWING
Row 5

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

🎯 Real-World Examples

Top N per Group

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY sales DESC ) AS rn FROM products ) sub WHERE rn <= 3;

Get top 3 products per category

Percentile Calculation

SELECT employee_id, salary, NTILE(100) OVER ( ORDER BY salary ) AS percentile FROM employees;

Assign percentile ranks to salaries

Gap Detection

SELECT date, value, LAG(date) OVER ( ORDER BY date ) AS prev_date, date - LAG(date) OVER ( ORDER BY date ) AS days_gap FROM time_series;

Find gaps in time series data

Running Average

SELECT date, temperature, AVG(temperature) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS week_avg FROM weather;

Calculate 7-day moving average

⚠️ Common Pitfalls and Tips

❌ Mistake 1: Forgetting ORDER BY

Without ORDER BY in your window function, the order is unpredictable! Always specify ORDER BY for ranking and cumulative functions.

❌ Mistake 2: Confusing ROWS vs RANGE

ROWS counts physical rows. RANGE includes all rows with the same value. Use ROWS for most cases unless you specifically need value-based windows.

✅ Best Practice: Name Your Windows

SELECT name, ROW_NUMBER() OVER w AS rn, RANK() OVER w AS rnk FROM employees WINDOW w AS ( PARTITION BY dept ORDER BY salary DESC );

Reuse window definitions to keep your query DRY (Don't Repeat Yourself)

🚀 Ready to Practice?

Head over to our SQL editor and try these window functions yourself!

Launch SQL Editor →