SQL Window Functions: The Part That Took Me a While to Understand

Window functions were one of those SQL topics that looked much more complicated than they actually were.

What finally helped me understand them was realising that they let me calculate something across related rows without removing the individual rows.

That is the big difference from GROUP BY.

What are window functions?

A window function performs a calculation across a set of related rows while still keeping each row in the result.

For example, suppose I have sales data:

Amina | Nairobi | 50000

Brian | Nairobi | 35000

Faith | Mombasa | 45000

I can calculate rankings while still keeping each salesperson in the result.

ORDER BY

ORDER BY tells the window function how the rows should be arranged before the calculation is performed.

For example, I can rank salespeople from highest sales to lowest sales:

The result could look like:

Amina | 50000 | 1

Faith | 45000 | 2

Brian | 35000 | 3

The important part here is:

ORDER BY amount DESC

It tells SQL to start with the highest sales.

PARTITION BY

PARTITION BY is used when I want the calculation to happen separately for different groups.

Suppose the salespeople work in different branches.

I can rank them within each branch:

Now someone can be ranked first in Nairobi and someone else can also be ranked first in Mombasa.

The ranking starts again for each branch.

That's what PARTITION BY does.

ROW_NUMBER()

ROW_NUMBER() gives every row a unique number based on the ordering.

For example, I might want to number hotel bookings from the most expensive to the cheapest:

The result could be:

101 | Amina | 150000 | 1

107 | Brian | 120000 | 2

103 | Faith | 90000 | 3

Even if two bookings have the same amount, each row still gets a different number.

This is useful when I need to identify the first, second, third, and so on.

RANK()

RANK() is useful when I want to rank values but allow ties.

Suppose two salespeople both made KSh 100,000:

The result might be:

Amina | 150000 | 1

Brian | 100000 | 2

Faith | 100000 | 2

David | 70000 | 4

Notice that David is ranked 4th, not 3rd, because two people shared position 2.

DENSE_RANK()

DENSE_RANK() is similar to RANK(), but it doesn't skip the next ranking number after a tie.

Using the same example:

Amina | 150000 | 1

Brian | 100000 | 2

Faith | 100000 | 2

David | 70000 | 3

The difference between RANK() and DENSE_RANK() becomes important when analysing tied results.

A practical example with PARTITION BY

Imagine I work for a hotel with several branches.

I want to find the highest-value booking in each branch.

I can use:

Now I can easily identify the number 1 in each branch.

That means I have the highest-value booking for every branch without running a separate query for each branch.

Why are window functions useful?

This is where I think window functions become really powerful.

I can use them to:

Find the top salesperson in each branch.

Rank students within each class.

Find the highest-value hotel booking per branch.

Number transactions in order.

Compare a row with other rows without losing the original data.

For example, if I want the top three salespeople in every branch, I can first rank them:

Then I can filter the result to ranks 1, 2, and 3.

Window functions vs GROUP BY

This was probably the biggest thing I had to understand.

GROUP BY combines rows into groups.

A window function keeps the individual rows and performs a calculation across them.

For example:

This gives me one row per branch.

But:

keeps every salesperson while also showing the total sales for their branch.

That difference is what makes window functions so useful.

Final thoughts

Window functions looked intimidating at first because of all the extra syntax.

But I now think about them like this:

PARTITION BY → Which group am I working within?

ORDER BY → How should the rows be arranged?

ROW_NUMBER() → Give each row a number.

RANK() → Rank the rows and allow ties.

DENSE_RANK() → Rank the rows without gaps after ties.

Once I started thinking about the business question first, window functions became much easier to understand.