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

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

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

Running total

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;

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 BYGROUP 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”).

  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"]

🧪 Try It Yourself

Exercise 1 – ROW_NUMBER per partition

Exercise 2 – Running total with SUM() OVER

Exercise 3 – RANK vs ROW_NUMBER on ties

Next

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