SQL Window Functions: The 5 Patterns Every Data Engineer Uses Weekly

Window functions separate engineers who write SQL from engineers who think in SQL. These five patterns cover the vast majority of warehouse work: CDC dedup, metrics, leaderboards, and sessionization.

Try them live: every pattern below runs in the SQL Playground — SQLite in the browser, orders, customers, and events preloaded. No signup.

flowchart TD
  rows[Input rows] --> part[PARTITION BY key]
  part --> ord[ORDER BY time]
  ord --> fn["row_number / rank / sum / lag"]
  fn --> keep[CTE filter: rn = 1 or top-N]

How a window is different from GROUP BY

GROUP BY collapses rows. A window keeps every row and adds a column computed over a peer group.

-- one row per country
select country, sum(amount) from orders group by country;

-- every order, plus that country's total next to it
select
  order_id,
  country,
  amount,
  sum(amount) over (partition by country) as country_revenue
from orders;

That second shape is how you compare a row to its group without a self-join.

The frame you did not know you were using

sum(x) over (order by day) is not “the whole table.” In most engines the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running total.

last_value(x) over (order by day) therefore returns the current row’s value unless you open the frame:

last_value(status) over (
  partition by customer_id
  order by ordered_at
  rows between unbounded preceding and unbounded following
) as latest_status

If a result looks “one row off,” check the frame before you rewrite the business logic.

flowchart LR
  start[UNBOUNDED PRECEDING] --> cur[CURRENT ROW]
  cur --> end[UNBOUNDED FOLLOWING]

Default running frames stop at the current row. last_value usually needs the right edge open.

1. Deduplication (the interview classic you will actually use)

Keep the latest version of each record — daily bread of CDC:

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

row_number gives a unique rank. rank() keeps ties (two rows with rn-equivalent 1). For dedup you almost always want row_number.

Never put row_number() in WHERE. Windows are evaluated after WHERE. Wrap in a CTE or subquery. This is the bug that shows up in every junior PR.

Playground version: example chip Dedup latest order.

2. Running totals

select
    order_date,
    revenue,
    sum(revenue) over (
        order by order_date
        rows between unbounded preceding and current row
    ) as revenue_to_date
from daily_revenue;

Be explicit about ROWS vs RANGE if order_date can tie. Two orders on the same day can surprise you with RANGE.

Playground: Running revenue on orders.

3. Period-over-period with LAG

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

lag(col, n) reaches back n rows; lead looks forward. You cannot reference the alias prev_month in the same select in most dialects — hence the CTE. nullif saves you from divide-by-zero on the first month.

Same shape for a 7-day lookback without a self-join:

select
  day,
  revenue,
  avg(revenue) over (
    order by day
    rows between 6 preceding and current row
  ) as revenue_7d
from daily_revenue;

4. Top-N per group

Top 3 products per country without a correlated subquery:

with ranked as (
    select *,
        row_number() over (
            partition by country
            order by revenue desc
        ) as rank_in_country
    from product_revenue
)
select * from ranked where rank_in_country <= 3;

Use rank() if ties should all appear (two products with the same revenue both “#1”). Use row_number() if you need a hard cap of N rows.

Playground: Top 3 per country.

5. Sessionization with gaps

Group events into sessions separated by 30+ minutes of inactivity:

with flagged as (
    select *,
        case when occurred_at - lag(occurred_at) over (
            partition by user_id order by occurred_at
        ) > interval '30 minutes'
        then 1 else 0 end as new_session
    from events
),
sessions as (
    select *,
        sum(new_session) over (
            partition by user_id
            order by occurred_at
        ) as session_number
    from flagged
)
select user_id, session_number,
       min(occurred_at) as session_start,
       count(*) as events
from sessions
group by 1, 2;

Flag session starts with lag, then turn flags into IDs with a running sum. That flag-then-accumulate pattern also solves “group consecutive identical statuses” and “islands and gaps.”

flowchart LR
  ev[Events in time] --> gap{Gap over 30 min?}
  gap -->|yes| new[New session]
  gap -->|no| same[Same session]
  new --> id[Running sum of flags]
  same --> id

A session is a gap rule, not an hour bucket. Flooring the clock merges strangers.

Playground: Sessionize (30 min gap) on the events table.

Islands and gaps (missing dates in a daily series) is the same flag idea, inverted:

with days as (
  select d::date as dt
  from generate_series(
    date '2026-08-01',
    date '2026-08-31',
    interval '1 day'
  ) as g(d)
),
marked as (
  select
    d.dt,
    f.revenue,
    case when f.revenue is null then 1 else 0 end as is_gap
  from days d
  left join daily_revenue f on f.day = d.dt
)
select dt, revenue, is_gap
from marked
where is_gap = 1;

generate_series is Postgres / DuckDB. In Snowflake use a generator or a date spine model. The point is the spine: windows cannot invent missing rows.

QUALIFY (when your warehouse has it)

Snowflake and some others let you skip the CTE:

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

Same plan, less nesting. Snowflake and BigQuery support QUALIFY; the playground is SQLite, so use a CTE there. Do not mix CTE and QUALIFY styles in one model without a reason.

Performance

Every distinct PARTITION BY / ORDER BY pair can require its own sort. Reuse the same window spec:

sum(amount) over w as running,
avg(amount) over w as moving_avg
from orders
window w as (
  partition by country
  order by ordered_at
  rows between 6 preceding and current row
);

On big tables, align partition columns with clustering / sort keys. A window on user_id against a table clustered only by event_date will scan more than you want.

ROWS vs RANGE

ROWS counts physical rows. RANGE counts value peers. On a timestamp with ties, RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW can pull more rows than you meant. On a unique order_id sequence, they look the same until the first duplicate clock.

Default in most dialects when you write ORDER BY and omit the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is why SUM “just works” as a running total — and why LAST_VALUE looks broken.

Prefer ROWS when the grain is “the last N events.” Prefer RANGE when the grain is “the last N days of clock time,” and only after you have looked at ties.

-- last 7 *rows* (events), not 7 calendar days
avg(amount) over (
  partition by customer_id
  order by ordered_at
  rows between 6 preceding and current row
) as avg_last_7_orders

Lead as a next-event join

LEAD is a self-join you do not have to write:

select
  user_id,
  occurred_at as this_event,
  lead(occurred_at) over (
    partition by user_id
    order by occurred_at
  ) as next_event,
  datediff(
    'minute',
    occurred_at,
    lead(occurred_at) over (
      partition by user_id
      order by occurred_at
    )
  ) as minutes_to_next
from events;

A histogram of minutes_to_next is how you pick the 30-minute session gap instead of inventing it in a meeting.

Pitfalls

• Filtering window results in WHERE — illegal or wrong. Use a CTE or QUALIFY.
• rank vs row_number on dedup — ties duplicate “primary” keys.
• last_value without an unbounded frame.
• Dividing by lag without nullif.
• Sessionizing in the application loop instead of SQL, then wondering why the warehouse sits idle.

What to memorize

Five shapes: dedup, running total, lag/lead, top-N, sessionize. If a ticket does not fit those, you probably want a join or a GROUP BY, not a new window invention.

Open the playground, run the chips, then change a partition. That is faster than rereading this page.

FAQ

Why did WHERE rn = 1 fail? Window aliases are not visible in WHERE on the same SELECT. Use a CTE or QUALIFY where the dialect has it.

When do I want RANK instead of ROW_NUMBER? When ties should share a place (leaderboard). For a hard cap of N rows or a unique latest row, use ROW_NUMBER.

Why is LAST_VALUE equal to the current row? The default frame ends at the current row. Open it with ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Can I sessionize by flooring the timestamp to the hour? That splits real sessions and merges strangers who share an hour. Use a lag gap, then a running sum.

Does every extra window make the query slower? Distinct PARTITION BY / ORDER BY pairs can each sort. Reuse one named WINDOW clause when the spec is the same.