What do PARTITION BY and ORDER BY do inside OVER()?
SHORT ANSWER
PARTITION BY splits rows into independent groups, and ORDER BY sorts rows within each group. For aggregates, adding ORDER BY also turns the result into a running calculation.
Explain it simply
PARTITION BY is like separating exam papers into piles by section. ORDER BY then sorts the papers inside each pile by marks. The window function works pile by pile and never looks across piles.
Example
Table sales(city, day, amount): Pune has 100 on day 1 and 50 on day 2; Delhi has 70 on day 1.
sql
SELECT city, day, amount,
SUM(amount) OVER (PARTITION BY city) AS city_total,
SUM(amount) OVER (PARTITION BY city ORDER BY day) AS running
FROM sales;| city | day | amount | city_total | running |
|---|---|---|---|---|
| Delhi | 1 | 70 | 70 | 70 |
| Pune | 1 | 100 | 150 | 100 |
| Pune | 2 | 50 | 150 | 150 |
- Without ORDER BY, SUM covers the whole partition, so every Pune row shows 150.
- With ORDER BY, the default frame runs from the first row to the current row, giving a running total.
- With no PARTITION BY, the whole result is one partition.
What to say in the interview
"PARTITION BY defines the groups, ORDER BY defines the sequence within each group. Ranking functions need ORDER BY. For aggregates, ORDER BY makes them cumulative."
Common mistakes
- Forgetting that ORDER BY inside OVER changes an aggregate from a total to a running total.