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()`	Unique per partition	`1 2 3 4`
`rank()`	Share, then skip	`1 2 2 4`
`dense_rank()`	Share, no gap	`1 2 2 3`
`ntile(n)`	Up to n near-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 current window frame.
`last_value(col)`	Last value in the current window frame.

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_date

lag(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 products

In DuckDB, an ordered aggregate window uses a cumulative default frame, so sum(...) over (order by ...) accumulates through the current ordering value. Ties are peers under the default RANGE frame; use an explicit ROWS frame when row-by-row behavior matters. An empty over () uses the whole partition.

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 = 1

Watch out

WHERE runs logically before window results are available, so filter a window result with a CTE/subquery or DuckDB's QUALIFY. For deterministic ranking, give row_number() an ORDER BY that fully breaks ties. Also remember that first_value() and last_value() operate on the current frame, not automatically the whole partition.

Lesson

Top-N Reports in SQL

Build ranked summary reports.

Reference

SQL Subqueries and EXISTS

Nested SELECTs, IN, EXISTS, and correlation.

Interview

SQL Window Function Interview Questions

Practice ranking, running totals, and top row per group.

Mistake

SQL: Window Running Totals Can Jump on Ties

The default RANGE frame groups tied rows; use ROWS.