examples · sql · 9 / 13 · works today

9. Window functions

works today

ROW_NUMBER(), RANK(), DENSE_RANK(), LAG() and LEAD() with OVER (PARTITION BY ... ORDER BY ...) all execute server-side through the SQL translator — and explicit ROWS BETWEEN / RANGE frame clauses (running totals, moving windows) execute too.

per-group ranking

Rank each customer's orders by amount - the canonical window use - runs server-side:

SELECT id, customer_id, amount_cents,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY amount_cents DESC
       ) AS rn
  FROM shop.orders

Each row comes back with its rn computed in one pass - no client-side sorting needed.

running totals — ROWS frame

Add a ROWS BETWEEN frame to accumulate a running total per partition — the classic dashboard shape — in one server-side pass:

SELECT id, customer_id, amount_cents,
       SUM(amount_cents) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running
  FROM shop.orders

running carries the cumulative sum as the window slides — customer 1 → 14900, 18800; customer 2 → 7200, 14400, 36400. RANGE frames and N PRECEDING / FOLLOWING bounds work the same way.