Window Functions

Partition Rank

Ranking Inside Groups

PARTITION BY restarts a window function for each group.

Program

Play the query to rank sellers within each region after a selectable amount filter.

partition_rank.sql 
CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);
INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);
WITH params(min_amount) AS (VALUES ()) SELECT region, seller, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank FROM sales WHERE amount >= (SELECT min_amount FROM params) ORDER BY region, region_rank;

CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);
INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);
WITH params(min_amount) AS (VALUES ()) SELECT region, seller, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank FROM sales WHERE amount >= (SELECT min_amount FROM params) ORDER BY region, region_rank;

CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);
INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);
WITH params(min_amount) AS (VALUES ()) SELECT region, seller, amount, RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank FROM sales WHERE amount >= (SELECT min_amount FROM params) ORDER BY region, region_rank;
PARTITION BY `PARTITION BY region` restarts ranking for each region.
RANK `RANK()` gives equal values the same rank when ties exist.
grouped window Partitioned windows combine row detail with grouped calculation.