Partition by SQL: Window Functions

SQL is powerful. But sometimes, just filtering and grouping data isn’t enough. There’s a secret weapon in SQL’s toolbox — Window Functions. And they get super useful when you mix them with PARTITION BY.

TLDR:

Window Functions let you do calculations across rows related to the current row. Adding PARTITION BY breaks the data into smaller chunks — like grouping, but still keeping all rows. It’s amazing for ranking, running totals, and comparing rows. Once you get the hang of it, your SQL powers will level up fast!

What’s a Window Function?

A Window Function does some math on a bunch of rows — but without collapsing them into one result like GROUP BY does. You still get all the rows, but now each row can “see” other rows around it. That’s pretty wild.

Okay, But What About PARTITION BY?

PARTITION BY is like a cousin of GROUP BY. It splits your data into little sections, or partitions. Then the window function runs inside each of those.

Think of it like this: You’re running a raffle in a school, and each classroom gets its own prize winner. That’s what partitioning does — it keeps things fair by grouping the right rows together before doing the math.

Without PARTITION BY, the window function treats all rows the same. Adding it adds structure and meaning.

Time for an Example

Here’s a basic SQL table that we will use. It’s called sales:

id  |  salesperson  |  region  | amount
----+--------------+---------+--------
1   | Alice         | West    | 500
2   | Bob           | West    | 700
3   | Carl          | East    | 300
4   | Dana          | East    | 400
5   | Erin          | West    | 200

Goal:

Let’s calculate the total sales per region but still keep all rows. GROUP BY can’t do this — it would smush rows together.

With a Window Function:

SELECT
  salesperson,
  region,
  amount,
  SUM(amount) OVER(PARTITION BY region) AS region_total
FROM sales;

Now we get:

salesperson | region | amount | region_total
------------+--------+--------+--------------
Alice       | West   | 500    | 1400
Bob         | West   | 700    | 1400
Erin        | West   | 200    | 1400
Carl        | East   | 300    | 700
Dana        | East   | 400    | 700

Pretty, right? Each person sees their region’s total — thanks to PARTITION BY. 💪

Other Handy Window Functions

Let’s take a look at some common ones:

• ROW_NUMBER() — Gives each row a unique number per partition
• RANK() — Similar to ROW_NUMBER, but tied rows get the same rank
• DENSE_RANK() — Like RANK(), but no rank gaps
• SUM(), AVG(), MAX(), MIN() — All work with partitions
• LAG(), LEAD() — Let you peek at earlier or later rows

Rank Employees by Sales — Per Region

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

salesperson | region | amount | region_rank
------------+--------+--------+-------------
Bob         | West   | 700    | 1
Alice       | West   | 500    | 2
Erin        | West   | 200    | 3
Dana        | East   | 400    | 1
Carl        | East   | 300    | 2

Each region now has its own ranking chart!

Why Not Just Use GROUP BY?

Great question. The key difference is this:

• GROUP BY merges rows into one summary
• PARTITION BY keeps all rows, but adds context

So use GROUP BY when you want to shrink your table. Use PARTITION BY when you want to enrich your rows with info about their “data neighborhood”.

Peeking with LAG and LEAD

Want to compare someone’s sales to the person before or after them? Easy:

SELECT
  salesperson,
  region,
  amount,
  LAG(amount, 1) OVER(PARTITION BY region ORDER BY amount DESC) AS prev_sales
FROM sales;

The LAG() function brings in the amount from the previous row (within the same region). Great for finding improvements or spotting drops.

And You Can Chain Them!

SELECT
  salesperson,
  region,
  amount,
  SUM(amount) OVER(PARTITION BY region) AS region_total,
  RANK() OVER(PARTITION BY region ORDER BY amount DESC) AS region_rank,
  LAG(amount, 1) OVER(PARTITION BY region ORDER BY amount DESC) AS prev_sales
FROM sales;

Now you’ve got advanced analytics going on — all in one query. 🚀

Use Cases That’ll Blow Your Mind

• Find the top salesperson in each region? Easy. RANK().
• Calculate running totals, resets per month? Yup. SUM() + PARTITION BY month.
• Compare performance this week vs last? Use LAG().
• Want to know who improved the most? Compare amounts with LAG() or LEAD().

One More Bonus Trick

Want to calculate someone’s percent of total sales in their region?

SELECT
  salesperson,
  region,
  amount,
  amount * 100.0 / SUM(amount) OVER(PARTITION BY region) AS percent_of_region
FROM sales;

salesperson | region | amount | percent_of_region
------------+--------+--------+-------------------
Alice       | West   | 500    | 35.71
Bob         | West   | 700    | 50.00
Erin        | West   | 200    | 14.29
Carl        | East   | 300    | 42.86
Dana        | East   | 400    | 57.14

Percentages per region — done in one line! Magic. ✨

In Summary

Window Functions with PARTITION BY are amazing. They let you:

• Keep all your data rows
• But add smart, calculated columns
• Give each row context from its “peers”
• Impress your coworkers 💼

Once you get it, it’s hard to imagine life before it. And trust us — your reports will never be the same.

Quick Reminders Before You Go:

• GROUP BY = compress rows
• PARTITION BY = organize data, not remove it
• Always pair with OVER() when using a window function
• Ordering within each partition helps a lot

Now get out