Understanding SQL Window Functions

Introduction

This week I’ve been learning a range of SQL concepts. Some clicked quickly, others took a bit more effort to fully grasp.

Joins, for example, were relatively intuitive. Once I understood how tables connect, it became easier to reason about how data flows between them. Window functions, on the other hand, were a different story.

I found them harder to wrap my head around. Not because they are overly complex, but because they require a shift in how you think about data — from grouping and reducing rows to enriching each row with additional context.

That challenge is what led me to write this article. To keep things grounded, I’ll use a simple student exam results dataset throughout the article.

---

What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row, without collapsing the dataset.

Unlike GROUP BY, which reduces multiple rows into a single summary row, window functions retain every row and add additional insights alongside the existing data.

---

1. Assigning Order with ROW_NUMBER()

Requirement

Assign a unique rank to each exam result from highest to lowest marks.

SELECT 
    result_id, 
    student_id, 
    marks,
    ROW_NUMBER() OVER (ORDER BY marks DESC) AS row_num
FROM exam_results;

How it works

• The data is sorted by marks in descending order
• SQL assigns numbers sequentially (1, 2, 3…)

Key behaviour

• Every row gets a unique number
• Ties are ignored
• Ordering strictly follows the defined rule

Example

marks	row_num
95	1
90	2
90	3
85	4

Line everyone up based on a rule, then count them one by one

---

2. Handling Ties: RANK() vs DENSE_RANK()

SELECT 
    marks,
    RANK() OVER (ORDER BY marks DESC) AS rank_marks,
    DENSE_RANK() OVER (ORDER BY marks DESC) AS dense_rank
FROM exam_results;

Example

marks	rank	dense_rank
95	1	1
90	2	2
90	2	2
85	4	3

---

Rank Behaviour

• Equal values share the same rank
• Gaps appear after ties

Dense_Rank Behaviour

• Equal values share the same rank
• No gaps in ranking

RANK counts positions, DENSE_RANK counts distinct values

---

3. Grouping into Buckets with NTILE()

How it works

• Rows are sorted by marks
• Data is divided into equal groups as much as possible

Key behaviour

• Groups may not be equal
• Extra rows go to top groups first
• Ties are ignored

SELECT 
    result_id,
    student_id,
    marks,
    NTILE(3) OVER (ORDER BY marks DESC) AS performance_band
FROM exam_results;

Example

Group	Rows
1	4
2	3
3	3

Divide the list into equal buckets, then fill from the top in case of any remainders

---

4. Adding Context with AVG() OVER()

Requirement

Show each exam result alongside the student’s average mark.

Query

SELECT 
    student_id,
    marks,
    ROUND(AVG(marks) OVER (PARTITION BY student_id), 2) AS student_avg
FROM exam_results;

How it works

• Data is grouped per student using PARTITION BY
• Average is calculated per group
• Result is attached to each row

Key behaviour

• Rows are not collapsed
• Averages repeat per student
• Original detail is preserved

Example

student_id	marks	student_avg
1	80	85.00
1	90	85.00
2	70	70.00

Why not GROUP BY?

SELECT student_id, AVG(marks)
FROM exam_results
GROUP BY student_id;

• Returns one row per student
• Loses exam-level detail

Calculate once per group, attach results everywhere

---

Summary

Function	What it answers
ROW_NUMBER()	Exact order of rows
RANK()	Position with gaps
DENSE_RANK()	Grouping without gaps
NTILE()	Bucket distribution
AVG() OVER()	Context per row

Conclusions

Window functions are not just about computation — they change how you think about data.

Instead of reducing rows, they allow you to:

• Add meaning
• Compare within context
• Build richer analysis directly in SQL

Once that clicks, everything else becomes much easier, or does it?