Window functions, properly

If you learn one advanced SQL topic, learn this one. Window functions turn a category of problems that would otherwise need self-joins or application code into a single readable clause.

The mental model#

GROUP BY collapses rows. A window function keeps every row and computes something across a related set of rows alongside it.

select
    borough,
    fare_usd,
    avg(fare_usd) over (partition by borough) as borough_avg,
    fare_usd - avg(fare_usd) over (partition by borough) as vs_borough
from ride_hail_trips;

Every trip is still there, now carrying its borough's average next to it. Doing that with GROUP BY requires computing the averages separately and joining back.

The anatomy#

function() over (
    partition by <split into groups>
    order by     <order within each group>
    rows between <frame: which rows count>
)

• partition by — like GROUP BY, but does not collapse. Omit it and the window is the whole result set.
• order by — required for anything positional: rankings, running totals, lag/lead.
• frame — which rows within the partition participate. This is the part people skip, and it has a surprising default.

sum(fare_usd) over (order by pickup_at rows between unbounded preceding and current row)

Say rows explicitly. Always.

The functions worth knowing#

Ranking. row_number() gives 1,2,3 with no ties. rank() gives 1,2,2,4. dense_rank() gives 1,2,2,3. Pick deliberately: "top 3 per group" with rank() can return five rows.

Offsets. lag(x, 1) and lead(x, 1) reach backwards and forwards. This is period-over-period, and it replaces the self-join everybody writes first.

select
    month,
    revenue,
    lag(revenue) over (order by month)                                as prev_month,
    revenue - lag(revenue) over (order by month)                      as change,
    round(100.0 * (revenue / nullif(lag(revenue) over (order by month), 0) - 1), 1) as pct_change
from monthly_revenue;

nullif(x, 0) guards the division. Without it, one zero month kills the query.

Aggregates as windows. sum, avg, count, min, max all work with over. Running totals and moving averages:

avg(load_mw) over (order by hour_at rows between 23 preceding and current row) as ma_24h

Distribution. ntile(4) for quartiles, percent_rank(), cume_dist(). ntile is how you build a decile analysis in one line.

First and last. first_value, last_value, nth_value. last_value has a notorious gotcha — with the default frame it returns the current row, since the frame ends there. You need rows between unbounded preceding and unbounded following.

The patterns you will use constantly#

Deduplicate, keeping the best row per key. The single most useful window pattern in existence.

select * from (
    select *,
           row_number() over (partition by customer_id order by updated_at desc) as rn
    from customer_snapshots
) ranked
where rn = 1;

Top N per group.

select * from (
    select borough, trip_id, fare_usd,
           row_number() over (partition by borough order by fare_usd desc) as rn
    from ride_hail_trips
) t where rn <= 3;

Gaps and islands — grouping consecutive runs. The classic trick: subtract a row_number() from the value, and consecutive runs share a constant.

-- Find stretches of consecutive days a device reported.
select device_id, min(reading_date) as run_start, max(reading_date) as run_end, count(*) as days
from (
    select device_id, reading_date,
           reading_date - (row_number() over (partition by device_id order by reading_date))::int as grp
    from daily_readings
) t
group by device_id, grp
having count(*) > 1;

Sessionisation — start a new session when the gap exceeds a threshold.

select *,
       sum(is_new_session) over (partition by user_id order by event_at rows unbounded preceding) as session_id
from (
    select *,
           case when event_at - lag(event_at) over (partition by user_id order by event_at)
                     > interval '30 minutes'
                then 1 else 0 end as is_new_session
    from events
) t;

That last one is worth studying. A cumulative sum over a 0/1 flag is how you convert "did something change here" into "which group am I in", and it generalises far beyond sessions.

Where they run#

Window functions execute after WHERE and GROUP BY, and before ORDER BY and LIMIT. Two consequences:

• You cannot filter on a window function in WHERE. Wrap it in a subquery or CTE, as every example above does.
• QUALIFY, where your engine supports it (Snowflake, BigQuery, DuckDB), removes that wrapping:

select * from customer_snapshots
qualify row_number() over (partition by customer_id order by updated_at desc) = 1;

Performance#

Each distinct partition by … order by combination generally costs a sort. Five window functions sharing one specification cost one sort; five with different specifications cost five. Reuse the specification where you can — a named WINDOW clause makes that explicit and readable:

select
    trip_id,
    sum(fare_usd)   over w as running_fare,
    row_number()    over w as trip_seq
from ride_hail_trips
window w as (partition by borough order by pickup_at rows unbounded preceding);