Cheatsheet
SQL Window Functions Cheatsheet
OVER, PARTITION BY, ranking, LAG/LEAD, and running totals.
The shape
Anatomy of a window
function(args) over (
partition by group_col -- optional: restart per group
order by sort_col -- optional: needed for rank/offset/running
rows between … -- optional: the frame
)Every row stays in the result. The function adds a column instead of collapsing rows like GROUP BY.
Ranking
| Function | Ties | Sequence |
|---|---|---|
row_number() | Always unique | 1 2 3 4 |
rank() | Share, then skip | 1 2 2 4 |
dense_rank() | Share, no gap | 1 2 2 3 |
ntile(n) | n equal buckets | 1 1 2 2 |
Top price per category
row_number() over (partition by category_id order by price desc)Offsets: look at other rows
| Function | Returns |
|---|---|
lag(col) | Value from the previous row. |
lead(col) | Value from the next row. |
first_value(col) | First value in the window. |
last_value(col) | Last value in the window. |
Change vs. the previous day
with daily_sales as (
select o.order_date,
sum(oi.quantity * oi.unit_price) as daily_total
from orders as o
join order_items as oi on oi.order_id = o.order_id
group by o.order_date
)
select order_date,
daily_total,
daily_total - lag(daily_total) over (order by order_date) as change
from daily_sales
order by order_datelag(col, 2, 0) = look back 2 rows, default to 0 at the edge.
Running totals & shares
Running total and percent of group
select product_name,
price,
sum(price) over (order by price
rows between unbounded preceding
and current row) as running_total,
round(100.0 * price
/ sum(price) over (partition by category_id), 1) as pct_of_category
from productsAn ordered sum() over (...) already accumulates. An emptyover () totals everything.
Filtering a window result
WHERE cannot see a window: wrap it
with ranked as (
select *, row_number() over (partition by category_id
order by price desc) as rn
from products
)
select * from ranked where rn = 1Watch out
WHERE runs before window functions, so you can't filter on rn directly. Use a CTE/subquery, or DuckDB'sQUALIFY. And row_number() needs anORDER BY inside OVER to be deterministic.