Keep the first-ingested copy of each event

The ridelog app sends events to a collector that retries on timeout. About 5% of events landed twice. A copy has the same user_id, event_type and event_ts as the original, but its own event_id and a later ingested_at.

Downstream jobs need each event exactly once. Return the rows of events with duplicates removed:

column	type	meaning
event_id	integer	id of the copy you kept
user_id	integer	as in events
event_type	text	as in events
event_ts	timestamp	as in events
ingested_at	timestamp	as in events

Rules:

• Two rows are copies of one event when user_id, event_type and event_ts all match.
• Keep the copy with the earliest ingested_at.
• If two copies were ingested at the same instant, keep the one with the lower event_id.
• Don’t assume the first copy has the lower event_id. Ids come from the collector, not the clock.
• Two different event types at the same timestamp are two events. Keep both.

Row order doesn’t matter.

Tables: events.

Approach

Partition by the columns that define “the same event”, sort each partition by the rule for which copy wins, and keep the first row.

SELECT event_id, user_id, event_type, event_ts, ingested_at
FROM events
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id, event_type, event_ts
  ORDER BY ingested_at, event_id
) = 1;

Why it works

The PARTITION BY says what a duplicate is. The ORDER BY says which copy survives. Splitting those two decisions is the whole pattern, and both are business rules you should write down.

ROW_NUMBER hands out 1, 2, 3 with no ties, so exactly one row per event gets 1. RANK would give two rows the number 1 when ingested_at ties. That’s why the event_id tie-breaker is there: it makes the choice deterministic, so running the job twice keeps the same row.

MIN(event_id) per group is a common shortcut and it’s wrong here. The lowest id is not always the first ingest.

Under the hood

DuckDB hashes rows on (user_id, event_type, event_ts) to form partitions, sorts each partition by (ingested_at, event_id), then numbers rows in one pass. Most partitions hold a single row, so the sorts are tiny. QUALIFY is applied after the window runs, the same as filtering in an outer query.