Approach overview:- Identify duplicates by ordering events per user and marking any event within 1 second of a previous kept event as duplicate.- Delete duplicates in small batches using a CTE (or temp table) so each DELETE touches limited rows (reduces lock contention).- If feasible, do dedupe offline (CTAS + swap) or enforce uniqueness on insert (unique index + upsert) to avoid repeated cleanup.SQL pattern (Postgres-style) — mark duplicates using window functions, then delete in batches:sql
-- 1) First create a helper column rn_group that increments when gap > 1 second
WITH flagged AS (
SELECT
event_id,
user_id,
event_time,
SUM(is_new_group) OVER (PARTITION BY user_id ORDER BY event_time) AS grp
FROM (
SELECT
event_id,
user_id,
event_time,
CASE
WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL THEN 1
WHEN EXTRACT(EPOCH FROM (event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time))) > 1 THEN 1
ELSE 0
END AS is_new_group
FROM staging_events
) s
),
-- 2) pick the "first" row in each group to keep
to_delete AS (
SELECT f.event_id
FROM (
SELECT event_id,
ROW_NUMBER() OVER (PARTITION BY user_id, grp ORDER BY event_time, event_id) AS rn
FROM flagged f
) f2
WHERE rn > 1
-- optional: limit batch size to avoid large deletes
LIMIT 10000
)
DELETE FROM staging_events se
USING to_delete td
WHERE se.event_id = td.event_id;
