Skip to content
Python Central Hub

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;
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;
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.

If this helped you, consider buying me a coffee ☕

Buy me a coffee

Was this page helpful?

Let us know how we did