SkillsetMaster - AI, Web Development & Data Analytics Courses

What are Window Functions?

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Window functions perform calculations across rows while keeping all rows in the result. Unlike GROUP BY which combines rows, window functions keep every row.

Simple analogy: Like ranking students - each student keeps their row, but you add their rank number.

ROW_NUMBER - Give Each Row a Number

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Assigns a unique number to each row.

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM students;

Result:

| name | score | rank | |-------|-------|------| | John | 95 | 1 | | Mary | 90 | 2 | | Peter | 85 | 3 |

Each student keeps their row + gets a rank number.

RANK - Rank with Gaps

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Like ROW_NUMBER but ties get same rank (creates gaps).

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Result:

| name | score | rank | |-------|-------|------| | John | 95 | 1 | | Mary | 90 | 2 | | Peter | 90 | 2 | | Sarah | 85 | 4 |

Mary and Peter both score 90, so both get rank 2. Next rank is 4 (gap!).

DENSE_RANK - Rank Without Gaps

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Like RANK but no gaps.

SELECT
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Result:

| name | score | rank | |-------|-------|------| | John | 95 | 1 | | Mary | 90 | 2 | | Peter | 90 | 2 | | Sarah | 85 | 3 |

No gaps! After two people at rank 2, next is 3.

Difference Between ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Same scores: 95, 90, 90, 85

| Function | Ranks | |-------------|-------------| | ROW_NUMBER | 1, 2, 3, 4 | | RANK | 1, 2, 2, 4 | | DENSE_RANK | 1, 2, 2, 3 |

LEAD - Look at Next Row

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Get value from the next row.

SELECT
  name,
  score,
  LEAD(score) OVER (ORDER BY score DESC) AS next_score
FROM students;

Result:

| name | score | next_score | |-------|-------|------------| | John | 95 | 90 | | Mary | 90 | 85 | | Peter | 85 | NULL |

Shows the next student's score.

LAG - Look at Previous Row

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Get value from the previous row.

SELECT
  name,
  score,
  LAG(score) OVER (ORDER BY score DESC) AS previous_score
FROM students;

Result:

| name | score | previous_score | |-------|-------|----------------| | John | 95 | NULL | | Mary | 90 | 95 | | Peter | 85 | 90 |

Shows the previous student's score.

PARTITION BY - Rank Within Groups

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Rank students within each grade separately.

SELECT
  name,
  grade,
  score,
  ROW_NUMBER() OVER (PARTITION BY grade ORDER BY score DESC) AS rank_in_grade
FROM students;

Result:

| name | grade | score | rank_in_grade | |-------|-------|-------|---------------| | John | A | 95 | 1 | | Mary | A | 90 | 2 | | Peter | B | 88 | 1 | | Sarah | B | 85 | 2 |

Ranking restarts for each grade!

Real-World Examples

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Example 1: Top 3 Products per Category

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

Gets top 3 best-selling products in each category.

Example 2: Compare with Previous Month

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS previous_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS difference
FROM monthly_sales;

Shows revenue change from previous month.

Example 3: Running Total

SELECT
  order_date,
  total,
  SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders;

Cumulative total of all orders.

Example 4: Employee Salary Rank

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

Rank employees by salary within each department.

Summary

ROW_NUMBER vs RANK vs DENSE_RANK

name	score	ROW_NUMBER	RANK	DENSE_RANK
John	95	1	1	1
Mary	90	2	2	2
Peter	90	3	2	2
Sarah	85	4	4	3

4 rows

LEAD and LAG Example

name	score	LAG (previous)	LEAD (next)
John	95	NULL	90
Mary	90	95	85
Peter	85	90	NULL

3 rows

Window functions = Calculate across rows without grouping

Common functions:

ROW_NUMBER: Unique number per row (1, 2, 3, 4)
RANK: Rank with gaps (1, 2, 2, 4)
DENSE_RANK: Rank without gaps (1, 2, 2, 3)
LEAD: Look at next row
LAG: Look at previous row
PARTITION BY: Create groups within window

Use for:

Rankings and leaderboards
Comparing with previous/next rows
Running totals
Top N per category

Window Functions

What are Window Functions?

ROW_NUMBER - Give Each Row a Number

RANK - Rank with Gaps

DENSE_RANK - Rank Without Gaps

Difference Between ROW_NUMBER, RANK, DENSE_RANK

LEAD - Look at Next Row

LAG - Look at Previous Row

PARTITION BY - Rank Within Groups

Real-World Examples

Example 1: Top 3 Products per Category

Example 2: Compare with Previous Month

Example 3: Running Total

Example 4: Employee Salary Rank

Summary

Finished this topic?