Windows I — ranking & offsets

ROW_NUMBER/RANK/DENSE_RANK on ties, PARTITION vs GROUP, top-N per group, LAG/LEAD.

• ★ROW_NUMBER vs RANK vs DENSE_RANK

  On ties, ROW_NUMBER breaks them arbitrarily, RANK gaps, DENSE_RANK doesn't — only one matches the business sentence.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE entries(name VARCHAR, score INT); INSERT INTO entries VALUES ('Ana',90),('Ben',90),('Cy',80);

  The trap

  SELECT name, ROW_NUMBER() OVER (ORDER BY score DESC, name) AS place FROM entries ORDER BY place;

  Ana	1
  Ben	2
  Cy	3

  The fix

  SELECT name, RANK() OVER (ORDER BY score DESC) AS place FROM entries ORDER BY name;

  Ana	1
  Ben	1
  Cy	3

  Ana and Ben tied at 90 but the standings put Ben in second — ROW_NUMBER invented an order the scores don't have.

• PARTITION BY keeps rows; GROUP BY collapses

  A window PARTITION returns every row with the aggregate attached; GROUP BY returns one row per group — choosing the wrong one.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE repairs(shop VARCHAR, mins INT); INSERT INTO repairs VALUES ('east',30),('east',50),('west',40);

  The trap

  SELECT shop, AVG(mins) AS avg_mins FROM repairs GROUP BY shop ORDER BY shop;

  east	40
  west	40

  The fix

  SELECT shop, mins, mins - AVG(mins) OVER (PARTITION BY shop) AS vs_avg FROM repairs ORDER BY shop, mins;

  east	30	-10
  east	50	10
  west	40	0

  GROUP BY collapsed the repairs to one row per shop, so the per-repair detail was gone — a window PARTITION keeps every repair with its shop's average attached.

• Top-N per group

  A window function can't be filtered in WHERE — wrap it in a subquery or use QUALIFY to keep rn <= N.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE sales(branch VARCHAR, drink VARCHAR, units INT); INSERT INTO sales VALUES ('north','kale',30),('north','beet',10),('south','mango',22),('south','lime',8);

  The trap

  SELECT branch, drink FROM sales WHERE ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units DESC) = 1 ORDER BY branch;

  0 rows

  The fix

  SELECT branch, drink FROM sales QUALIFY ROW_NUMBER() OVER (PARTITION BY branch ORDER BY units DESC) = 1 ORDER BY branch;

  north	kale
  south	mango

  Filtering ROW_NUMBER() in WHERE raised a binder error — the window value doesn't exist yet when WHERE runs, so the engine refused rather than guessed; QUALIFY (or a subquery) is the compute-then-filter fix.

• LAG / LEAD

  LAG/LEAD default to NULL at the partition edge; offset >1 and IGNORE NULLS are the controls (dialect note).

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE oil(night INT, litres INT); INSERT INTO oil VALUES (1,100),(2,90),(3,75);

  The trap

  SELECT night, litres - LAG(litres) OVER (ORDER BY night) AS used FROM oil ORDER BY night;

  1	NULL
  2	-10
  3	-15

  The fix

  SELECT night, litres - LAG(litres, 1, litres) OVER (ORDER BY night) AS used FROM oil ORDER BY night;

  1	0
  2	-10
  3	-15

  Night one's consumption came out NULL — LAG defaults to NULL at the partition edge (no predecessor), and here that NULL then poisons the arithmetic; the third argument LAG(litres, 1, litres) states the baseline instead.

• Window + DISTINCT interaction

  DISTINCT applies after window functions compute — surprising duplicates or collapses result.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE p(color VARCHAR); INSERT INTO p VALUES ('teal'),('teal');

  The trap

  SELECT DISTINCT color, ROW_NUMBER() OVER () AS rn FROM p ORDER BY rn;

  teal	1
  teal	2

  The fix

  SELECT color, ROW_NUMBER() OVER () AS rn FROM (SELECT DISTINCT color FROM p) ORDER BY rn;

  teal

  1

  Adding ROW_NUMBER kept both identical 'teal' rows — windows compute before DISTINCT, so the row number made the duplicates unique and DISTINCT found nothing to collapse; staging the dedup first fixes it.

• NTILE with uneven buckets

  NTILE(n) deals rows into n buckets as evenly as it can — when counts do not divide, the leading buckets get the extra rows, so labels sit on unequal populations.

  Lesson →
  See it breakshowhide

  Setup

  CREATE TABLE castings(id INT, w INT); INSERT INTO castings VALUES (1,10),(2,20),(3,30),(4,30),(5,50),(6,60),(7,70);

  The trap

  SELECT w, NTILE(3) OVER (ORDER BY w, id) AS bucket FROM castings ORDER BY id;

  10	1
  20	1
  30	1
  30	2
  50	2
  60	3
  70	3

  The fix

  SELECT w, CASE WHEN w < 40 THEN 1 WHEN w < 70 THEN 2 ELSE 3 END AS bucket FROM castings ORDER BY id;

  10	1
  20	1
  30	1
  30	1
  50	2
  60	2
  70	3

  NTILE put the two equal 30-weight castings in different buckets — it deals rows by position, not value, and over 7 rows the buckets are 3·2·2; a value-range CASE keeps equal weights together.