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.

min_amount
partition_rank.sql
Replay: real traced execution (multi-file project)
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 (25)) 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 (20)) 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 (30)) 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;

  1. sales ← 0 rows

    1CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);
    values this step0 rowssales

  2. sales ← 4 rows

    1CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);3WITH params(min_amount) AS (VALUES (25)) 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;
    values this step4 rowssales

  3. result ← 3 rows

    2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);3WITH params(min_amount) AS (VALUES (25)) 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;
    values this step3 rowsresult

  1. sales ← 0 rows

    1CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);
    values this step0 rowssales

  2. sales ← 4 rows

    1CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);3WITH params(min_amount) AS (VALUES (20)) 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;
    values this step4 rowssales

  3. result ← 4 rows

    2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);3WITH params(min_amount) AS (VALUES (20)) 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;
    values this step4 rowsresult

  1. sales ← 0 rows

    1CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);
    values this step0 rowssales

  2. sales ← 4 rows

    1CREATE TABLE sales (region TEXT, seller TEXT, amount INTEGER);2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);3WITH params(min_amount) AS (VALUES (30)) 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;
    values this step4 rowssales

  3. result ← 2 rows

    2INSERT INTO sales VALUES ('east', 'Ada', 30), ('east', 'Lin', 20), ('west', 'Mia', 25), ('west', 'Noor', 35);3WITH params(min_amount) AS (VALUES (30)) 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;
    values this step2 rowsresult

Follow the Groups

  1. The default min_amount is 25.
  2. The filter keeps east Ada 30, west Noor 35, and west Mia 25.
  3. Ranking restarts inside each region.
  4. Ada is rank 1 in east; Noor is rank 1 in west.
  5. Mia is the next kept west row, so Mia gets rank 2. | region | seller | amount | rank in region | | --- | --- | --- | --- | | east | Ada | 30 | 1 | | west | Noor | 35 | 1 | | west | Mia | 25 | 2 |
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.

Exercise: partition_rank.sql

Reproduce the default ranks, then use the pinned min_amount variants to predict why 20 also keeps east Lin as rank 2 and 30 leaves only Ada and Noor.