A window function is a SQL function that computes a value for each row based on a related set of rows — the “window” — without collapsing those rows the way GROUP BY does. Running totals, rankings, and “compare this row to the previous one” are all window function territory: you keep every row of detail and add a computed column alongside.
OVER and PARTITION BY
What turns an ordinary function into a window function is the OVER clause. Inside it, PARTITION BY splits the rows into groups (the window restarts for each group) and ORDER BY defines the sequence within each group. Say you want each customer’s orders alongside a running total of what they’ve spent:
SELECT
customer_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS running_total
FROM orders;
Every order stays in the result. The SUM just gets computed over a moving window: all of that customer’s orders up to and including the current one. Swap in ROW_NUMBER() and you get “which order is this for the customer” — a standard trick for finding each customer’s first purchase. LAG() and LEAD() reach backward and forward a row, which is how month-over-month comparisons usually get written.
Window functions vs. GROUP BY
The two answer different questions, and mixing them up is a common source of confusing results. An aggregation with GROUP BY returns one row per group — total revenue per region gives you one row per region. A window function returns every input row, with the group-level number attached to each. That’s what makes “each order’s share of its region’s revenue” a one-step query: divide the row’s amount by SUM(amount) OVER (PARTITION BY region).
One practical wrinkle: window functions are evaluated after WHERE and GROUP BY, so you can’t filter on their results directly in the same query. The usual fix is to wrap the query in a CTE or subquery and filter in the outer step — “rank the products, then keep the top three per category” is the classic shape.
Window functions in Metabase
Window functions are supported by essentially every database Metabase connects to, and the native SQL editor is where most people write them. A common pattern is to build the windowed query in SQL, save it as a question or a model, and let others slice it further in the query builder without touching the SQL. The query builder itself also offers cumulative aggregations like cumulative sum and cumulative count, which cover the most common running-total cases without any SQL at all.
Related terms
Further reading
Put it to work
- SQL cheat sheet — Cheat sheet
- Best practices for writing SQL queries — Tutorial
- Data warehouse dashboard — Dashboard
