SQL Window Functions Explained

SQL becomes particularly interesting when you stop asking only, “What is the total?” and start asking questions like:

• What is each salesperson's total while still seeing every sale?
• How does this sale compare with the previous one?
• What is this customer's rank within their group?
• How much have we sold so far?

These questions are difficult to answer neatly with ordinary aggregation. This is where SQL window functions come in.

The idea behind a window function

Consider a simple "sales" table:

salesperson	sale_date	amount
Alice	2026-01-01	500
Alice	2026-01-03	800
Alice	2026-01-05	300
Bob	2026-01-01	700
Bob	2026-01-04	400

Suppose we want the total sales for each salesperson.

With "GROUP BY":

SELECT
    salesperson,
    SUM(amount) AS total_sales
FROM sales
GROUP BY salesperson;

Output:

salesperson	total_sales
Alice	1,600
Bob	1,100

We get one row per salesperson. That is useful, but something has been lost: the individual transactions.

A window function allows us to calculate across related rows without collapsing those rows. Consider this:

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY salesperson
    ) AS total_sales
FROM sales;

Output:

salesperson	sale_date	amount	total_sales
Alice	2026-01-01	500	1,600
Alice	2026-01-03	800	1,600
Alice	2026-01-05	300	1,600
Bob	2026-01-01	700	1,100
Bob	2026-01-04	400	1,100

Now every transaction remains visible, but each row also knows the salesperson's total. That is the fundamental idea. GROUP BY reduces rows. Window functions calculate across rows while keeping them.

What does "PARTITION BY" mean?

"PARTITION BY" defines the group of rows over which the calculation operates.

In:

SUM(amount) OVER (
    PARTITION BY salesperson
)

Alice's sales form one window and Bob's sales form another.

It is similar to saying:

“Perform this calculation separately for each salesperson, but do not combine their records into one row.”

You can think of "PARTITION BY" as “divide the data into groups for the calculation.”

What does "ORDER BY" do?

Now suppose the manager wants to know how much each salesperson has sold up to each transaction.

SELECT
    salesperson,
    sale_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY salesperson
        ORDER BY sale_date
    ) AS running_total
FROM sales;

For Alice, the results would conceptually become:

sale_date	amount	running_total
Jan 1	500	500
Jan 3	800	1,300
Jan 5	300	1,600

Here, "ORDER BY" matters because the calculation depends on sequence. Without an ordering, “running total” has no meaningful direction.

Looking at the previous row with "LAG()"

Sometimes we do not need a total. We want to compare the current value with an earlier value. For example, how much did Alice's latest sale change from her previous sale?

SELECT
    salesperson,
    sale_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY salesperson
        ORDER BY sale_date
    ) AS previous_sale
FROM sales;

Output:

salesperson	sale_date	amount	previous_sale
Alice	2026-01-01	500	NULL
Alice	2026-01-03	800	500
Alice	2026-01-05	300	800
Bob	2026-01-01	700	NULL
Bob	2026-01-04	400	700

The first sale for each salesperson has no previous sale, so "LAG()" returns "NULL". This is very useful for questions involving change over time.

You can similarly use "LEAD()" when you want to look forward rather than backward.

Ranking rows

Window functions are also useful for ranking.

SELECT
    salesperson,
    sale_date,
    amount,
    RANK() OVER (
        PARTITION BY salesperson
        ORDER BY amount DESC
    ) AS sale_rank
FROM sales;

Output:

salesperson	sale_date	amount	sale_rank
Alice	2026-01-03	800	1
Alice	2026-01-01	500	2
Alice	2026-01-05	300	3
Bob	2026-01-01	700	1
Bob	2026-01-04	400	2

This ranks each salesperson's transactions from largest to smallest.

There are several ranking functions, including "RANK()", "DENSE_RANK()", and "ROW_NUMBER()". They differ mainly in how they handle ties.

You do not need to memorize all of them immediately. The important idea is that ranking can happen within a window without removing the underlying rows.

Why this matters beyond SQL

Window functions are not just a reporting trick.

For data analysts and machine learning practitioners, they can be useful when creating features such as:

• previous transaction amount
• number of previous transactions
• cumulative customer spending
• transaction rank
• change from previous observation

For example, "LAG()" can help create a feature representing a customer's previous purchase amount.

But there is an important warning: time-dependent features can cause data leakage if future information accidentally enters the training data.

The fact that SQL can calculate something does not mean it is appropriate for a machine learning model.

The mental model

When you see:

FUNCTION(...) OVER (...)

ask three questions:

1. What function am I applying?
   "SUM", "AVG", "LAG", "RANK", etc.

2. What rows belong together?
   That's usually "PARTITION BY".

3. Does the order of those rows matter?
   If it does, use "ORDER BY".

That's most of the mental model you need to start.

You do not have to master every window function to understand why they are powerful.

The key is simple:

A window function lets SQL look across related rows without losing sight of the current row.

Once that idea clicks, functions such as "SUM() OVER()", "LAG()", "RANK()", and "ROW_NUMBER()" stop looking like mysterious SQL syntax and start looking like different ways of asking questions about a row's context.