SQL Window Functions for Analysts: Latest Record, Event Timing, Running Totals

A SQL window function computes a value across a set of related rows without collapsing them: every row stays in the output and gains a new column, its rank, its previous event, its running total. For analysts, three patterns cover most real work: ROW_NUMBER for the latest record per group, LAG for time between events, and SUM OVER for running totals.

A window function is an aggregate or ranking calculation that runs over a window of rows related to the current row, declared with an OVER clause, without merging those rows the way GROUP BY does. That one property, aggregate context attached to intact detail rows, is exactly what analyst questions need: which is the latest status of each payment, how long did each payment sit between two statuses, what was the cumulative settled amount at each point in the day. If you already query the state to find the truth, window functions are the next tier: they turn questions that otherwise need self-joins and correlated subqueries into one readable query.

The examples below run on a payment status events table, the shape you meet behind any payment state machine: one row per status event, many rows per payment.

payment_id  status  created_at
P-1001      ACCP    2026-07-10 09:00:04
P-1001      ACSP    2026-07-10 09:00:11
P-1001      ACSC    2026-07-10 09:02:47
P-1002      ACCP    2026-07-10 09:01:30
P-1002      RJCT    2026-07-10 09:01:31

What does OVER (PARTITION BY … ORDER BY …) actually mean?

The OVER clause defines the window: which rows the function can see, and in what order. PARTITION BY splits the rows into independent groups, and the function restarts for each group. ORDER BY sequences the rows inside each partition, which ranking and offset functions require. An optional frame clause narrows the window further, to “all rows so far” or “the previous 6 rows.”

Read any window function in three steps. First the function: what is being computed (a row number, a lagged value, a sum). Then the partition: per what (per payment, per account, per day). Then the order: sequenced how (by event time, by amount). ROW_NUMBER() OVER (PARTITION BY payment_id ORDER BY created_at DESC) reads as “number this payment’s events, newest first.” Once you read the clause this way, every window function in a colleague’s query becomes legible.

How do you get the latest record per group?

Number each group’s rows with ROW_NUMBER, newest first, and keep row 1. This is the most used window pattern in analyst work, because operational tables are event-shaped, many rows per entity, while most questions are state-shaped, one current row per entity.

with ranked as (
  select
    payment_id,
    status,
    created_at,
    row_number() over (
      partition by payment_id
      order by created_at desc, event_id desc
    ) as rn
  from payment_status_events
)
select payment_id, status, created_at
from ranked
where rn = 1;

One row per payment, its current status. The event_id tiebreaker matters: two events in the same millisecond are common in fast systems, and without a deterministic tiebreaker ROW_NUMBER picks one arbitrarily, so the same query can return different answers on different runs. That is also the difference between the three ranking functions. ROW_NUMBER forces unique numbers, RANK gives ties the same number and skips (1, 1, 3), DENSE_RANK gives ties the same number without skipping (1, 1, 2). For latest-record queries you want ROW_NUMBER with a real tiebreaker; for leaderboard-style questions, “rank accounts by volume,” RANK or DENSE_RANK states your tie policy explicitly.

The same pattern is a duplicate detector when you flip the filter: where rn > 1 returns every duplicate row, which is how you check a feed for replays before reconciling it against the source.

How do you measure time between events with LAG?

LAG fetches a value from the previous row in the window, which puts an event and its predecessor on the same output row, so the time between them becomes a subtraction. Without it, this question is an awkward self-join on “the row before this one,” which is painful to write and easy to get wrong.

select
  payment_id,
  status,
  created_at,
  lag(status) over (
    partition by payment_id order by created_at
  ) as previous_status,
  created_at - lag(created_at) over (
    partition by payment_id order by created_at
  ) as time_in_previous_status
from payment_status_events;

Each row now shows the status, the status before it, and how long the payment sat there. Filter to status = 'ACSC' and you have settlement duration per payment; aggregate that and you have the distribution your service level reporting needs. LEAD is the mirror image, fetching from the next row, useful for “what happened after.”

The pair also validates sequences. previous_status lets you assert legal transitions directly: a row where status is ACSC and previous_status is RJCT is a rejected payment that somehow settled, an illegal transition in the state machine and exactly the kind of defect a QA analyst can catch with one query across millions of rows.

How do running totals and moving windows work?

An aggregate function with an ORDER BY in its window becomes cumulative: on each row, it aggregates everything up to that row. A frame clause makes it a moving window instead.

select
  created_at,
  amount,
  sum(amount) over (
    partition by settlement_date order by created_at
  ) as cumulative_settled,
  avg(amount) over (
    order by created_at
    rows between 6 preceding and current row
  ) as moving_avg_7
from settled_payments;

cumulative_settled answers “how much had settled by this point in the day,” the shape behind intraday liquidity views and cut-off monitoring. The rows between 6 preceding and current row frame is a 7-row moving average, the standard smoothing for trend checks. The default frame with an ORDER BY is “start of partition to current row,” which is why a plain sum(...) over (order by ...) is cumulative; state the frame explicitly when you mean something else.

This is the clearest illustration of window versus GROUP BY: GROUP BY settlement_date tells you the day’s total, once. The window tells you the total so far on every row, and you still have every payment’s detail next to it.

Where do window functions show up in real analyst work?

Four situations account for most usage. Current state from event tables: the rn = 1 pattern, used every time you need one row per payment, account, or case from an event stream, which in a warehouse is most of the time, since fact tables are event-grained. Duplicate and gap detection: rn > 1 for replays, LAG for sequence gaps, both core to reconciliation and feed validation. Timing and service levels: LAG subtraction for time in status, feeding aging reports and cut-off analysis. Cumulative and trend views: running totals and moving averages for intraday positions and volume trends.

All of this is standard SQL, the syntax above runs unchanged on PostgreSQL, Snowflake, BigQuery, and SQL Server. Window functions are not a dialect trick; they are part of the SQL standard, which makes them the rare skill that transfers intact across every warehouse you will ever touch.

The takeaway

Window functions attach aggregate context to intact detail rows: OVER declares the window, PARTITION BY splits it into groups, ORDER BY sequences it, and a frame clause bounds it. Three patterns cover most analyst work: ROW_NUMBER ... where rn = 1 for the latest record per group, LAG for time and transitions between events, and SUM OVER with an ordered window for running totals. Learn to read the OVER clause in three steps, function, partition, order, and both writing your own and reading everyone else’s becomes routine.