Window Functions (OVER, PARTITION BY)

Why window functions

Window functions let you compute metrics across related rows while keeping each row.

Common uses:

• Rank customers by revenue
• Running totals over time
• Moving averages

ROW_NUMBER and RANK

Rank orders by amount per user

SELECT
  user_id,
  order_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders;

Running total

Running revenue by date

SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS running_revenue
FROM (
  SELECT
    DATE(order_ts) AS order_date,
    SUM(amount) AS daily_revenue
  FROM orders
  GROUP BY DATE(order_ts)
) t
ORDER BY order_date;

PARTITION BY

• Splits data into groups
• Window function works within each group

Example: running revenue by country.

Window functions vs GROUP BY

The key difference from GROUP BY: a window function does not collapse rows. Every input row stays in the result, but each row also gets a computed value based on the rows around it (its “window”).

diagram How OVER (PARTITION BY ...) works mermaid

  flowchart LR
  A["All rows"] --> B{"PARTITION BY user_id"}
  B --> C["Window: user 1's rows"]
  B --> D["Window: user 2's rows"]
  C --> E["ROW_NUMBER / SUM ... per row"]
  D --> E
  E --> F["Same row count as input, plus a new column"]

PARTITION BY splits rows into windows; the function computes a value per row using only the rows in its own window.

sketch Partitions computed independently p5.js Pause Restart

Rows are grouped into colored partitions; RANK restarts at 1 inside each partition instead of continuing across the whole table.

Analyst’s tip

PARTITION BY restarts the calculation for every group, the same way GROUP BY does — the difference is that a window function still returns one output row per input row instead of collapsing them. Forget PARTITION BY and your “per-user” rank or running total quietly becomes a global one across the whole table.

🧪 Try It Yourself

Exercise 1 – ROW_NUMBER per partition

pch.exerciseTag pch.exerciseTitle

Exercise 2 – Running total with SUM() OVER

pch.exerciseTag pch.exerciseTitle

Exercise 3 – RANK vs ROW_NUMBER on ties

pch.exerciseTag pch.exerciseTitle

Next

CTEs (WITH) and Subqueries show you how to name and reuse the derived tables you’ve been building inline with subqueries so far.