SQL window functions: select one event with a deterministic rule

Last updated: 5 Oct 20265 min read

tutorial

A window function can rank event versions without collapsing the audit trail before the selection rule is applied.

Define which version wins

A support case can have several resolution events because agents edit status or a replay emits corrected records. Decide whether the analysis uses the latest event known at a snapshot, the first valid resolution or the final resolution before cutoff. These are different measures. For a latest-known snapshot, rank events per case by occurrence time, revision and stable event ID, all descending. The final event ID breaks ties and makes reruns deterministic.

Apply the cutoff before ranking

Filter event rows to those known by the analysis snapshot, then compute ROW_NUMBER. Ranking all history first and filtering afterward can discard an older valid event when the globally latest one arrived after the cutoff. This is a point-in-time error. Snapshot discipline requires the source revision and cutoff to be fixed in the review packet.

Retain the losing rows for audit

The ranked table should expose row number and tie-break columns, even if the final metric keeps only rank one. Count cases with multiple candidates and exact timestamp ties. A change in winning row after a backfill should produce a revision record, not an unexplained jump in a published rate. Historical joins are the next boundary when event rows need versioned dimensions.

Separate sequence from aggregate

ROW_NUMBER chooses a record; SUM OVER computes a running measure while keeping every record. Neither is a cure for duplicate business identities. If two source IDs refer to the same case, resolve identity first. A window partition on the wrong key creates plausible but invalid output, especially when a customer has several separate cases.

Probe a tie fixture

For case 401, two events share the same occurrence timestamp but revisions 1 and 2. Revision 2 should win. For case 402, an event arrived after the snapshot; it must be absent from the ranked input. Assert the two selected IDs and the count of excluded late events. Without those assertions, ordering bugs tend to appear only when source traffic is replayed.

Implementation

WITH status_events(case_id, event_id, occurred_minute, revision, known_minute, status) AS (
    VALUES (401, 91, 20, 1, 25, 'open'),
           (401, 92, 20, 2, 27, 'resolved'),
           (402, 93, 17, 1, 21, 'open'),
           (402, 94, 29, 1, 35, 'resolved')
), ranked AS (
    SELECT case_id, event_id, status,
           ROW_NUMBER() OVER (
               PARTITION BY case_id
               ORDER BY occurred_minute DESC, revision DESC, event_id DESC
           ) AS position
    FROM status_events
    WHERE known_minute <= 30
)
SELECT case_id, event_id, status
FROM ranked
WHERE position = 1
ORDER BY case_id;

Performance and operating cost

For N event rows, partition sorting is O(N log N) worst-case time and may spill to disk. An index aligned with case ID and ordering columns can reduce sorting work, though snapshot filters and engine plans matter. Retaining the ranked audit view costs storage but makes revisions explainable.

Common Mistakes

Do not rank future-known events before applying the snapshot cutoff.
Do not order only by a timestamp that can tie.
Do not partition by customer ID when the metric unit is a case.