Advanced SQL

Window Functions

Window functions calculate across related rows while retaining each row; partition, ordering, and frame are independent parts of the semantic contract and ties require deliberate treatment.

PARTITION BY divides the input into independent analytic groups.
Window ORDER BY defines analytic sequence and peer groups.
The frame is a subset around the current row.
ROWS counts physical ordered rows; RANGE groups by ordering values.
The ordered default commonly ends at the current row’s last peer.
Null ordering and null inputs need explicit intent.

Partition, order, and frame

Explicit running total and ranking

WITH order_totals AS (
  SELECT o.customer_id, o.order_id, o.placed_at,
         SUM(i.quantity * i.unit_price) AS order_total
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'PAID'
  GROUP BY o.customer_id, o.order_id, o.placed_at
)
SELECT customer_id, order_id, placed_at, order_total,
       SUM(order_total) OVER (
         PARTITION BY customer_id
         ORDER BY placed_at, order_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_amount,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id ORDER BY placed_at, order_id
       ) AS sequence_number
FROM order_totals
ORDER BY customer_id, placed_at, order_id;

Frame behavior when two orders share Jan 2
Frame	At first Jan 2 row	At second Jan 2 row
`ROWS ... CURRENT ROW`	Only first physical row	Both physical rows
`RANGE ... CURRENT ROW`	Both Jan 2 peers	Both Jan 2 peers
Whole partition	Every customer row	Every customer row

With amounts 40 and 60 on the same timestamp, deterministic ROWS plus order_id yields running totals 40 then 100. Ordering only by the timestamp leaves the physical order tied; a peer-aware RANGE total is 100 for both.