The "Mental Model" for Window Functions

In partnership with

Most analysts hit the same wall around month six.

You're asked to calculate something that sounds simple: "Show me each customer's order alongside their running total" or "Compare this month's revenue to last month's."

You write a GROUP BY query. It runs. And then you realize: you've collapsed all your rows into summaries. The individual orders are gone. The transaction-level detail your manager asked for? Destroyed.

This is the moment you discover that GROUP BY has a ceiling.

And this is where Window Functions enter the picture.

The Mental Model That Makes It Click

Before I show you syntax, I need to change how you think about data.

GROUP BY works like this:

Imagine a classroom of students. The teacher asks everyone to leave the room. She calculates the class average, writes "Average Grade: B+" on a single piece of paper, and returns alone.

The individual students? Gone. You have one summary row. That's GROUP BY.

Window Functions work differently:

The students stay in their seats. The teacher walks down each row with a clipboard. For Student #3, she calculates "Your grade compared to the two students sitting next to you." For Student #7, she calculates "Your cumulative GPA from the start of the year."

Every student remains visible. Every row stays intact. But now each row carries context about the rows around it.

This is the core principle: Window Functions let you calculate across related rows without collapsing them.

Once you internalize this, the syntax becomes obvious.

Before reading on. A quick shoutout to our sponsor who keeps this newsletter free. It helps us keep the lights on if you check them out. (Continue Reading)

Learn how to make every AI investment count.

Successful AI transformation starts with deeply understanding your organization’s most critical use cases. We recommend this practical guide from You.com that walks through a proven framework to identify, prioritize, and document high-value AI opportunities.

In this AI Use Case Discovery Guide, you’ll learn how to:

Map internal workflows and customer journeys to pinpoint where AI can drive measurable ROI
Ask the right questions when it comes to AI use cases
Align cross-functional teams and stakeholders for a unified, scalable approach

Get the Guide.

Breaking Down the Syntax

Every window function follows this pattern:

FUNCTION() OVER (
    PARTITION BY column_name
    ORDER BY column_name
)

PARTITION BY is the reset button.

It answers: "Where should the calculation restart?"

If you write PARTITION BY customer_id, the running total resets for each customer. Customer A gets their own running total. Customer B gets their own. They don't mix.

Without PARTITION BY, the calculation runs across all rows in your result set.

ORDER BY defines the sequence.

It answers: "In what order should the function process rows?"

For running totals, you need chronological order. For rankings, you need descending order by the metric you're ranking.

Here's a running total example:

SELECT 
    customer_id,
    order_date,
    order_amount,
    SUM(order_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) as running_total
FROM orders;
```

Each customer's orders stay visible. But now every row shows how much that customer has spent *up to that point*.

---

### The Ranking Functions: An Interview Trap

Interviewers love asking about `ROW_NUMBER`, `RANK`, and `DENSE_RANK`. They look similar. The differences are subtle. And most candidates get at least one wrong.

Here's a concrete scenario:

Three salespeople. Their Q4 numbers:
- Alice: $500
- Bob: $500  
- Carol: $400

You need to rank them. Which function do you use?

**ROW_NUMBER()** forces uniqueness. Even with a tie, it assigns different numbers.
```
Alice: 1
Bob: 2
Carol: 3
```

The order between Alice and Bob is arbitrary. ROW_NUMBER just picks one.

**RANK()** handles ties but skips the next number. Think of Olympic medals.
```
Alice: 1
Bob: 1
Carol: 3
```

Two gold medals. No silver. Carol gets bronze.

**DENSE_RANK()** handles ties without skipping.
```
Alice: 1
Bob: 1
Carol: 2

Two people tied for first. The next person is second, not third.

When to use each:

Need exactly one row per group (like "most recent order")? → ROW_NUMBER
Ranking for display where gaps are acceptable? → RANK
Ranking where you need consecutive numbers? → DENSE_RANK

If you can explain this distinction clearly in an interview, you've demonstrated senior-level SQL thinking.

Why Companies Actually Care

Window functions aren't academic exercises. They solve problems that managers ask for constantly:

"Show me month-over-month growth."

That's LAG(). You compare the current row to the previous row without a self-join.

"What's our cumulative revenue by quarter?"

That's SUM() OVER (ORDER BY ...). Running totals with one line of code.

"Give me the top 3 products in each category."

That's ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC), filtered to WHERE rn <= 3.

Every one of these queries is painful with GROUP BY. With window functions, they're straightforward.

The Resource

A single newsletter can't make you proficient. You need to write queries, break things, and debug.

I built a 23-page Window Functions Mastery Guide that walks through every major function with real e-commerce data. It includes:

The full "Easy → Medium → Hard" progression
A decision framework for choosing the right function
Common mistakes (like the LAST_VALUE trap—most people get this wrong)
Practice challenges with solutions
A raw dataset you can load into any SQL environment

📥 Download the PDF guide and practice dataset free at realanalystjobs.com/resources

This is the material I wish existed when I was learning. No fluff. Just the patterns you'll use in production.

See you next week.
—RAJ (Real Analyst Jobs)

P.S. Added new 25+ fresh remote jobs: realanalystjobs.com
Added new free projects : https://realanalystjobs.com/projects
Document your 2026 Journey: https://realanalystjobs.com/journey
Talk to me : https://realanalystjobs.com/raj