Faith Njenga

Posted on Sep 15

SQL Is Surviving, Franklin: Now Rows Are Competing

#database #sql #programming

If you have survived SELECT, WHERE, JOIN, GROUP BY, subqueries, and CTEs, congratulations.

Franklin has now discovered window functions.

And window functions are where SQL starts doing something that feels a little suspicious.

They can look at other rows without collapsing the rows you already have.

You can have:

An employee's salary and their department's average salary
A student's score and their rank
A month's sales and the running total
Today's sales and yesterday's sales

All in the same result.

Franklin has questions.

"Wait. SQL can look at other rows and still give me my original row?"

Yes, Franklin.

Welcome to window functions.

First, What Problem Are We Actually Solving?

Let's start with a tiny table.

employee	department	salary
Amina	Data	80000
Brian	Data	100000
Carol	HR	70000
David	HR	90000

Now imagine your manager asks:

"Show me every employee, their salary, and the average salary of their department."

You might think:

GROUP BY has entered the chat.

And you'd be right. Sort of.

We could calculate the department averages:

SELECT
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

The result:

department	avg_salary
Data	90000
HR	80000

Great.

Except...

Where did the employees go?

Amina disappeared.

Brian disappeared.

Carol disappeared.

David disappeared.

GROUP BY grouped the rows together to calculate one result per department.

But our question wanted both:

The individual employee
Information about the employee's group

This is where window functions walk in.

Meet `OVER()`: The Part That Makes the Window

Before we start throwing PARTITION BY, ORDER BY, and other SQL vocabulary at Franklin, let's understand the most important piece:

OVER()

OVER() tells SQL:

"Perform this calculation across a set of rows, but keep the individual rows in the result."

For example:

SELECT
    employee,
    salary,
    AVG(salary) OVER () AS average_salary
FROM employees;

The result could look like this:

employee	salary	average_salary
Amina	80000	85000
Brian	100000	85000
Carol	70000	85000
David	90000	85000

Let's break this down.

AVG(salary)

means:

"Calculate the average salary."

Then:

OVER()

means:

"Calculate it across the rows available to this query, but don't combine those rows into one."

That's the big difference.

Without a window function, an aggregate like AVG() can give us one overall result.

With AVG() OVER(), we can show the average alongside every individual row.

Think of OVER() as the doorway into window-function territory.

Once we step inside, we can tell SQL more specifically which rows it should consider.

`PARTITION BY`: Franklin, Pick Your Group

What if we don't want the average salary for everyone?

What if we want the average salary within each department?

That's where PARTITION BY comes in.

SELECT
    employee,
    department,
    salary,
    AVG(salary) OVER (
        PARTITION BY department
    ) AS department_avg
FROM employees;

The result:

employee	department	salary	department_avg
Amina	Data	80000	90000
Brian	Data	100000	90000
Carol	HR	70000	80000
David	HR	90000	80000

Let's break down the important part:

PARTITION BY department

This tells SQL:

"Separate the rows into groups based on department, but don't collapse them."

So SQL can mentally create:

Data

employee	salary
Amina	80000
Brian	100000

employee	salary
Carol	70000
David	90000

Then AVG() works within each group.

This is one of the most important differences to remember:

GROUP BY collapses rows into groups.

PARTITION BY creates groups for the window calculation while keeping the original rows.

Franklin can breathe again.

`ORDER BY`: Now Franklin Needs an Order

PARTITION BY answers:

"Which rows belong together?"

Sometimes we also need to answer:

"In what order should SQL consider these rows?"

That's where ORDER BY inside OVER() comes in.

For example, suppose we want to rank employees by salary.

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

Here:

PARTITION BY department

means:

"Rank employees separately within each department."

And:

ORDER BY salary DESC

means:

"Put the highest salary first."

So OVER() is the container, while PARTITION BY and ORDER BY give SQL more instructions about the rows involved.

Ranking: Apparently, Everyone Needs a Position

Now let's look at three ranking functions.

We'll use this table:

employee	salary
Amina	100000
Brian	100000
Carol	80000
David	70000

`ROW_NUMBER()`: Everyone Gets a Number

SELECT
    employee,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_number
FROM employees;

Result:

employee	salary	row_number
Amina	100000	1
Brian	100000	2
Carol	80000	3
David	70000	4

Notice something important.

Amina and Brian have the same salary, but they still receive different numbers.

ROW_NUMBER() gives every row a unique position.

Think:

"I don't care about ties. Just number every row."

`RANK()`: Ties Are Allowed

Now let's use RANK().

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Amina	100000	1
Brian	100000	1
Carol	80000	3
David	70000	4

Amina and Brian both receive rank 1 because they have the same salary.

But notice what happened next.

There is no rank 2.

The next person gets rank 3.

Think:

"Ties get the same rank, and the next rank can have a gap."

`DENSE_RANK()`: Ties Without the Gap

Now:

SELECT
    employee,
    salary,
    DENSE_RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Result:

employee	salary	salary_rank
Amina	100000	1
Brian	100000	1
Carol	80000	2
David	70000	3

Amina and Brian still tie for first.

But this time, Carol gets rank 2.

No gap.

So Franklin's ranking dictionary is:

Function	What it does
`ROW_NUMBER()`	Gives every row a unique number
`RANK()`	Ties get the same rank, and gaps can appear
`DENSE_RANK()`	Ties get the same rank, but no gaps appear

Three functions.

Three slightly different personalities.

Choose based on what the question actually asks.

`LAG()` and `LEAD()`: Looking Behind and Ahead

Now suppose we have monthly sales:

month	sales
January	100
February	200
March	300
April	400

And someone asks:

"How much did sales change compared with the previous month?"

We could try to join the table to itself.

Or we could use LAG().

SELECT
    month,
    sales,
    LAG(sales) OVER (
        ORDER BY month
    ) AS previous_month
FROM sales;

Result:

month	sales	previous_month
January	100	NULL
February	200	100
March	300	200
April	400	300

LAG() looks backward.

For February, it finds January.

For March, it finds February.

For April, it finds March.

The first row has nothing before it, so SQL gives us NULL.

And LEAD() does the opposite.

LEAD(sales) OVER (
    ORDER BY month
)

LEAD() looks at the row after the current row.

So:

LAG() → "What happened before me?"
LEAD() → "What happens after me?"

Franklin is now looking into the past and the future.

Running Totals: When `SUM()` Gets a Memory

Now someone asks:

"Show me the sales for each month and the total sales accumulated so far."

Our table:

month	sales
January	100
February	200
March	300
April	400
May	500

We can write:

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
    ) AS running_total
FROM sales;

And the result can be:

month	sales	running_total
January	100	100
February	200	300
March	300	600
April	400	1000
May	500	1500

But wait.

Franklin has a very reasonable question:

"Hold on. I only wrote SUM(sales) and ORDER BY month. How did SQL know that February should include January, and March should include January and February?"

Excellent question.

There is something happening behind the scenes.

When an aggregate window function such as SUM() has an ORDER BY but no frame explicitly specified, the database uses a default window frame.

For this simple example, that gives us the running-total behaviour we want.

Conceptually, SQL is looking at:

January
→ January

February
→ January + February

March
→ January + February + March

April
→ January + February + March + April

May
→ January + February + March + April + May

So:

100
100 + 200 = 300
100 + 200 + 300 = 600
100 + 200 + 300 + 400 = 1000
100 + 200 + 300 + 400 + 500 = 1500

Now we can make those boundaries explicit:

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

The result is:

month	sales	running_total
January	100	100
February	200	300
March	300	600
April	400	1000
May	500	1500

This version tells SQL exactly what we mean:

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

In human language:

"Start from the very first row and keep including rows until the row I'm currently calculating."

And now Franklin asks:

"Okay... what exactly are ROWS, BETWEEN, UNBOUNDED PRECEDING, and CURRENT ROW?"

Franklin.

You just opened the window frame.

Meet the Window Frame: Franklin, We Need Boundaries

A window is the set of rows that a window function can work with.

A window frame goes one step further.

It tells SQL:

"From that window, these are the exact rows I want you to use for this calculation."

This is where you might see:

CURRENT ROW
PRECEDING
FOLLOWING
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
ROWS BETWEEN

They sound complicated.

They're really just instructions about where to look.

`CURRENT ROW`: Where Am I?

CURRENT ROW means:

"The row I'm currently calculating."

If SQL is calculating the result for March:

January
February
March     <- CURRENT ROW
April
May

March is the current row.

Simple.

`PRECEDING`: Look Behind Me

PRECEDING means rows before the current row.

For example:

2 PRECEDING

means:

"Go back two rows."

If we're on March:

January   <- 2 PRECEDING
February  <- 1 PRECEDING
March     <- CURRENT ROW
April
May

`FOLLOWING`: Look Ahead

FOLLOWING means rows after the current row.

For example:

2 FOLLOWING

means:

"Look two rows ahead."

If we're on March:

January
February
March     <- CURRENT ROW
April     <- 1 FOLLOWING
May       <- 2 FOLLOWING

Franklin has now learned that SQL can look backward and forward.

`ROWS BETWEEN`: Tell SQL Exactly Where to Look

Suppose we want a 3-month moving average.

For each month, we want:

Current month + the two months before it.

We can write:

SELECT
    month,
    sales,
    AVG(sales) OVER (
        ORDER BY month
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM sales;

Let's translate the important part.

ROWS BETWEEN

means:

"Define the exact rows I want to use."

2 PRECEDING

means:

"Start two rows before me."

CURRENT ROW

means:

"Stop at the row I'm currently calculating."

So for March, the frame is:

January   <- 2 PRECEDING
February  <- 1 PRECEDING
March     <- CURRENT ROW

Those three rows are used to calculate March's moving average.

The result:

month	sales	moving_avg
January	100	100
February	200	150
March	300	200
April	400	300
May	500	400

Notice that January doesn't have two previous rows.

SQL doesn't invent them.

It simply uses the rows that actually exist.

`UNBOUNDED PRECEDING`: Start From the Beginning

Now imagine we want a running total.

Instead of saying:

"Go back two rows."

We want:

"Go all the way back to the first row."

That's:

UNBOUNDED PRECEDING

So we can write:

SELECT
    month,
    sales,
    SUM(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM sales;

In plain English:

"Start at the very first row and keep going until the row I'm currently calculating."

So when we're calculating April:

January   <- start here
February
March
April     <- CURRENT ROW
May

SQL adds January through April.

The result is:

month	sales	running_total
January	100	100
February	200	300
March	300	600
April	400	1000
May	500	1500

That's why the running total keeps growing.

`UNBOUNDED FOLLOWING`: Go All the Way to the End

There is also:

UNBOUNDED FOLLOWING

This means:

"Keep going until the last row."

For example:

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

means:

"Start at my current row and continue all the way to the end."

If we're on March:

January
February
March     <- CURRENT ROW
April
May       <- UNBOUNDED FOLLOWING

The frame is:

March + April + May

You don't need to memorize every possible combination right away.

Start with the vocabulary.

Franklin's Window Frame Dictionary

SQL	Human translation
`CURRENT ROW`	The row I'm on
`1 PRECEDING`	One row before me
`2 PRECEDING`	Two rows before me
`1 FOLLOWING`	One row after me
`2 FOLLOWING`	Two rows after me
`UNBOUNDED PRECEDING`	Start from the beginning
`UNBOUNDED FOLLOWING`	Go all the way to the end

And:

ROWS BETWEEN A AND B

basically means:

"Use the rows from A through B for this calculation."

That's the idea you want to carry with you.

`WHERE` Comes First: Sorry, Window Functions

Here's another rule that actually matters.

Suppose we calculate employee rankings:

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Now Franklin says:

"Cool. Give me only employees ranked number 1."

Naturally, he tries:

SELECT
    employee,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees
WHERE salary_rank = 1;

SQL says:

"Nope."

Why?

Because, in the simplified logical processing order, WHERE is handled before the window function result is available.

A simplified order looks like this:

FROM
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
WINDOW FUNCTIONS
↓
ORDER BY

So when SQL is evaluating WHERE, salary_rank isn't available yet.

The solution is to give SQL another query layer.

WITH ranked_employees AS (
    SELECT
        employee,
        salary,
        RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)

SELECT *
FROM ranked_employees
WHERE salary_rank = 1;

The CTE calculates the rank first.

Then the outer query receives that result.

Now WHERE can filter it.

Franklin survives.

Again.

Can Window Functions Be Nested?

Franklin has gained confidence.

Perhaps too much confidence.

He tries this:

AVG(
    RANK() OVER (
        ORDER BY salary DESC
    )
) OVER ()

No.

Window functions cannot be directly nested inside another window function.

If you need to use the result of one window calculation in another calculation, use another query layer.

For example:

WITH ranked AS (
    SELECT
        employee,
        salary,
        RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)

SELECT
    employee,
    salary,
    salary_rank
FROM ranked;

Think of it this way:

One query layer calculates. Another query layer uses the result.

This is another place where CTEs and subqueries become useful.

Can Window Functions Work With `GROUP BY`?

Yes.

This is another common misconception.

Window functions and GROUP BY can absolutely appear in the same query.

Suppose we first calculate the average salary for each department:

SELECT
    department,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Now we have one row per department.

We can rank those department averages:

SELECT
    department,
    AVG(salary) AS avg_salary,
    RANK() OVER (
        ORDER BY AVG(salary) DESC
    ) AS department_rank
FROM employees
GROUP BY department;

The important idea is:

GROUP BY determines the grouped rows first, and the window function can then work with those resulting rows.

So don't memorize:

"Window functions and GROUP BY don't mix."

That's not true.

Instead, ask:

"What rows exist at the point where my window function is being calculated?"

That question will save you from a lot of SQL confusion.

Franklin's Window Function Survival Guide

Before Franklin walks away pretending he understood everything on the first try, let's recap.

Need to keep individual rows?

Think:

OVER()

Need calculations within groups?

Think:

PARTITION BY

Need the rows considered in a particular order?

Think:

ORDER BY

Need rankings?

Think:

ROW_NUMBER()
RANK()
DENSE_RANK()

Need the previous row?

Think:

LAG()

Need the next row?

Think:

LEAD()

Need a running total?

Think:

SUM() OVER (ORDER BY ...)

Need a moving calculation?

Think:

ROWS BETWEEN ...

Need to look backward?

Think:

PRECEDING

Need to look forward?

Think:

FOLLOWING

Need to start from the first row?

Think:

UNBOUNDED PRECEDING

Need to go to the last row?

Think:

UNBOUNDED FOLLOWING

Need to filter a window-function result?

Don't put it directly in WHERE.

Use a:

CTE or subquery

Need one window function to use another window function's result?

Don't nest them directly.

Use another query layer.

So, What Exactly Did We Survive?

Window functions aren't really about doing "complicated calculations."

They're about answering questions that involve relationships between rows without throwing those rows away.

You can ask:

"Who is the highest-paid employee in each department?"

"How does this month's sales compare with last month's?"

"What's the running total so far?"

"What's the three-month moving average?"

"Where does each student rank in their class?"

"What was the previous transaction?"

"What comes next?"

And suddenly SQL isn't just asking:

"What rows do you want?"

It's asking:

"How should these rows understand each other?"

That's the magic of window functions.

Franklin came in thinking SQL only knew how to look at one row at a time.

He leaves knowing SQL can look across groups, backward, forward, and through carefully defined frames.

He just shouldn't try to put a window function inside another window function.

SQL has boundaries.

Even Franklin needs some.

What's Next?

We've survived:

SELECT
Filtering
GROUP BY
Joins
Subqueries
CTEs
Window functions

And somehow, Franklin is still here.

So apparently, surviving SQL was never the finish line.

It was just the beginning.

First, What Problem Are We Actually Solving?

Meet OVER(): The Part That Makes the Window

PARTITION BY: Franklin, Pick Your Group

ORDER BY: Now Franklin Needs an Order

Ranking: Apparently, Everyone Needs a Position

ROW_NUMBER(): Everyone Gets a Number

RANK(): Ties Are Allowed

DENSE_RANK(): Ties Without the Gap

LAG() and LEAD(): Looking Behind and Ahead

Running Totals: When SUM() Gets a Memory

Meet the Window Frame: Franklin, We Need Boundaries

CURRENT ROW: Where Am I?

PRECEDING: Look Behind Me

FOLLOWING: Look Ahead

ROWS BETWEEN: Tell SQL Exactly Where to Look

UNBOUNDED PRECEDING: Start From the Beginning

UNBOUNDED FOLLOWING: Go All the Way to the End

Franklin's Window Frame Dictionary

WHERE Comes First: Sorry, Window Functions

Can Window Functions Be Nested?

Can Window Functions Work With GROUP BY?

Franklin's Window Function Survival Guide

Need to keep individual rows?

Need calculations within groups?

Need the rows considered in a particular order?

Need rankings?

Need the previous row?

Need the next row?

Need a running total?

Need a moving calculation?

Need to look backward?

Need to look forward?

Need to start from the first row?

Need to go to the last row?

Need to filter a window-function result?

Need one window function to use another window function's result?

So, What Exactly Did We Survive?

What's Next?

Meet `OVER()`: The Part That Makes the Window

`PARTITION BY`: Franklin, Pick Your Group

`ORDER BY`: Now Franklin Needs an Order

`ROW_NUMBER()`: Everyone Gets a Number

`RANK()`: Ties Are Allowed

`DENSE_RANK()`: Ties Without the Gap

`LAG()` and `LEAD()`: Looking Behind and Ahead

Running Totals: When `SUM()` Gets a Memory

`CURRENT ROW`: Where Am I?

`PRECEDING`: Look Behind Me

`FOLLOWING`: Look Ahead

`ROWS BETWEEN`: Tell SQL Exactly Where to Look

`UNBOUNDED PRECEDING`: Start From the Beginning

`UNBOUNDED FOLLOWING`: Go All the Way to the End

`WHERE` Comes First: Sorry, Window Functions

Can Window Functions Work With `GROUP BY`?