lesson 35 Window functions · advanced

Window functions: ROW_NUMBER

Assign a sequence number to rows within a group.

the idea

Window functions calculate across related rows while keeping each row visible. ROW_NUMBER assigns 1, 2, 3 within a partition using its ORDER BY. Include a tie-breaker for repeatable results.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT
  id,
  customer_id,
  ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY ordered_at, id ) AS order_number
FROM orders;

Watch for: The window ORDER BY controls calculation, not the final display order.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Order a customer's orders. ROW_NUMBER needs a clear order within each partition. Sort one customer's orders by time to see the sequence.
step-1.sql
sql›
```
SELECT id, customer_id, ordered_at
FROM orders
WHERE customer_id = 1
ORDER BY ordered_at, id;
```
2
Number rows inside each partition. PARTITION BY customer_id restarts the counter for each customer. ORDER BY decides which row is 1, 2, 3…
step-2.sql
sql›
```
SELECT
  id,
  customer_id,
  ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY ordered_at, id ) AS order_seq
FROM orders;
```