What's the difference between PARTITION BY and GROUP BY?
Compare PARTITION BY with GROUP BY and calculate what share of its branch's year each month is.
Free course. Your Maestro, an AI, .
GROUP BY sucursal and PARTITION BY sucursal start the same way: they split the rows into groups by branch. The difference is in what they give back.
GROUP BYsummarizes: one row per group. The result's Glossary term changes: before, there was one row per branch and month; after, one per branch.PARTITION BYannotates: every row stays and gains a value computed over its group. The granularity doesn't change.
That group is called a partition. A row is never in two partitions at once, and each partition is computed on its own, as if it were a separate table. In the diagram, follow one Norte row from start to finish.
35 rows from ventas_mensuales
one per branch and month
Split by branch
Centro 12, Norte 12, Sur 11
SUM(ventas) over its partition
each row looks only at its own branch
35 rows with total_sucursal
no row disappears
Now look at the 35 rows plotted. Each dot is one month of one branch, right where it always was. Each color's horizontal line is AVG(ventas) OVER (PARTITION BY sucursal): the average of its partition, repeated on every row of that branch. No dot had to disappear for the line to exist.
If you've used Vega-Lite or Altair, that line comes from joinaggregate, which is exactly this: a per-group aggregate attached to every row. In SQL you write it with OVER.
With the group's value on the same row, you can compare each row with its group without leaving the query. ventas - AVG(ventas) OVER (PARTITION BY sucursal) tells you how far each month landed from its branch's average. In a run in Maestro's sandbox, Centro's December came out 31,825 pesos above its rounded average (181,175).
Since each row keeps its detail and carries its group's total, you can do math with both on the same row. For example, what share of its branch's year each month is:
ROUND(100.0 * ventas / SUM(ventas) OVER (PARTITION BY sucursal), 1)The 100.0 (with a decimal point) makes the division return decimals. ROUND(..., 1) keeps one decimal place.
Try it
Write the query that returns sucursal, mes, ventas and pct_del_año for all 35 rows. Before you run it, answer the question below: committing to a number first makes the result mean something. If your percentages don't add up, tell your Maestro what you expected.
Check yourself: Before you run it: Sur is missing June, so it has 11 rows. What will Sur's 11 percentages in pct_del_año add up to?
Return sucursal, mes, ventas and pctdelaño: the share of its branch's yearly sales that each month represents, rounded to one decimal place with ROUND(..., 1). Sort by sucursal, mes. You're done when you see 35 rows and Centro's December says 9.8.
Your turn
Return sucursal, mes, ventas and pct_del_año: the share of its branch's yearly sales that each month represents, rounded to one decimal place with ROUND(..., 1). Sort by sucursal, mes. You're done when you see 35 rows and Centro's December says 9.8.
Tables: ventas_mensuales (sucursal, mes, ventas)
ventas_mensuales · 35 rows
| Column | Type |
|---|---|
| sucursal Nombre de la sucursal: Centro, Norte o Sur. | VARCHAR |
| mes Primer día del mes que resume la fila. | DATE |
| ventas Ventas del mes, en pesos. | BIGINT |
First rows
| sucursal | mes | ventas |
|---|---|---|
| Centro | 2025-01-01 | 165100 |
| Centro | 2025-02-01 | 155700 |
| Centro | 2025-03-01 | 178700 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Each branch's percentages add up to 100 (or close, because of rounding), because each one was computed over its own partition. With OVER (), each month would be a share of all three branches together.
A rule of thumb: if you want fewer rows, it's GROUP BY; if you want the same rows with more information, it's a window.
Before you use this at work
You can partition by more than one column. PARTITION BY sucursal, date_trunc('quarter', mes) makes one partition per branch and quarter, so each month is compared with its quarter instead of its year.
ROUND(100.0 * ventas / SUM(ventas) OVER (
PARTITION BY sucursal, date_trunc('quarter', mes)
), 1) AS pct_del_trimestreIn the sandbox, Centro's December came out at 36.0% of its fourth quarter, versus 9.8% of its year.
NULLs don't count, but the row stays. SUM, AVG and COUNT(column) ignore NULLs inside a window too, just like with GROUP BY. The row with the NULL stays in the result and gets its partition's total; its own percentage comes out NULL, because NULL divided by anything is NULL. Here's what the sandbox returned for four rows, one of them empty:
| k | x | SUM(x) OVER () | COUNT(x) OVER () | COUNT(*) OVER () |
|---|---|---|---|---|
| a | 3 | 7 | 3 | 4 |
| b | NULL | 7 | 3 | 4 |
| c | 1 | 7 | 3 | 4 |
| d | 3 | 7 | 3 | 4 |
Look at the last two columns: COUNT(x) counts values and COUNT(*) counts rows. If you compute an average "by hand" as SUM / COUNT(*), the empty row drags the average down. If you're not sure which COUNT fits your case, ask your Maestro.
In interviews it shows up as "show each sale with its branch's average and the difference from that average" or "what percentage of its category does each product represent?" Either way, the clue is that they want every row plus a fact about its group. That combination is a window with PARTITION BY.
A quick test on your own data: take a table of yours with a group column and compute each row's percentage within its group, as in the exercise. Then add those percentages up with a GROUP BY on that same column. If any group doesn't come to 100 (or close, because of rounding), your PARTITION BY isn't the group you thought it was.