Window Functions: OVER, PARTITION BY and Ranking

Compute per-row analytics across a set of related rows - without collapsing them.

Big idea: A window function runs a calculation over a window - a set of rows related to the current row - and returns one value per row, keeping every original row visible. This is the key difference from GROUP BY, which folds many rows into one. Window functions are the foundation of analytical SQL: rankings, per-group top-N, running totals, and moving averages all start here.

By the end you will be able to:

explain how the window model differs from GROUP BY;
read the anatomy of an OVER(...) clause;
slice data into groups with PARTITION BY;
choose correctly between ROW_NUMBER, RANK and DENSE_RANK;
bucket rows with NTILE(n);
solve the top-N-per-group problem cleanly.

0. The example schema

Every query below runs against one small table of sales. Each row is a single deal closed by a salesperson in a region.

CREATE TABLE sales (
    id           INT PRIMARY KEY,
    region       VARCHAR(20),
    salesperson  VARCHAR(30),
    amount       DECIMAL(10,2)
);

INSERT INTO sales (id, region, salesperson, amount) VALUES
    (1, 'North', 'Alice', 5000),
    (2, 'North', 'Bob',   5000),
    (3, 'North', 'Carol', 3000),
    (4, 'South', 'Dan',   7000),
    (5, 'South', 'Eve',   4000),
    (6, 'South', 'Frank', 4000),
    (7, 'West',  'Grace', 6000),
    (8, 'West',  'Heidi', 2000);

Note the deliberate ties: Alice and Bob both closed 5000; Eve and Frank both closed 4000. Ties are exactly where the three ranking functions behave differently.

1. The window model vs GROUP BY

You already know GROUP BY: it collapses each group into a single summary row. If you ask for the total per region, three rows come back - one per region - and the individual salespeople disappear.

SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;

region	region_total
North	13000
South	15000
West	8000

A window function computes the same SUM but keeps every row. The magic word is OVER: it says "do this aggregate over a window, not over the whole GROUP".

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

region	salesperson	amount	region_total
North	Alice	5000	13000
North	Bob	5000	13000
North	Carol	3000	13000
South	Dan	7000	15000
South	Eve	4000	15000
South	Frank	4000	15000
West	Grace	6000	8000
West	Heidi	2000	8000

Same eight rows as the table, now each carrying its region total alongside. That single capability - "aggregate, but keep the detail" - is why analysts reach for window functions constantly. Compute amount / region_total and you have each person's share of their region, impossible in one pass with plain GROUP BY.

Window Functions: OVER, PARTITION BY and Ranking