Part 2 of 5 · 1 min read
OVER and PARTITION BY
Computing a per-group value while keeping every individual row.
Think of a leaderboard that lists every single player, each row also showing that player's rank. Nobody is removed from the list the way GROUP BY would remove all but one row per group.
In plain words: OVER turns an aggregate or ranking function into a window function, computed across a defined window of rows rather than collapsing the whole result into one row per group. PARTITION BY divides that window into groups, similar to GROUP BY, but every original row still appears in the output.
A worked example. Showing each order alongside the total number of orders that same customer has placed, without collapsing the order list down to one row per customer.
The key lines.
SELECT order_id, customer_id,
COUNT(*) OVER (PARTITION BY customer_id) AS customer_order_count
FROM orders;
Window function, PARTITION BY
A window function computes a value across a set of related rows without collapsing them into one row, using the OVER clause. PARTITION BY, inside OVER, divides rows into groups the window function computes separately within.
Example: COUNT(*) OVER (PARTITION BY customer_id) shows every order row, each annotated with that customer's total order count.