SQL Sample to remove duplicate events by event_id and collector_tstamp on PostgreSQL

delete_duplicate_snowplow_events_postgres.sql

DELETE
FROM atomic.events
WHERE ctid IN (SELECT ctid
               FROM (SELECT ctid, ROW_NUMBER() OVER (PARTITION BY event_id, collector_tstamp ) AS rn
                     FROM atomic.events) t
               WHERE rn > 1);

DELETE
FROM atomic.io_snowplow_foundation_content_1
WHERE ctid IN (SELECT ctid
               FROM (SELECT ctid, ROW_NUMBER() OVER (PARTITION BY root_id, root_tstamp ) AS rn
                     FROM atomic.io_snowplow_foundation_content_1) t
               WHERE rn > 1);

DELETE
FROM atomic.com_snowplowanalytics_snowplow_timing_1
WHERE ctid IN (SELECT ctid
               FROM (SELECT ctid, ROW_NUMBER() OVER (PARTITION BY root_id, root_tstamp ) AS rn
                     FROM atomic.com_snowplowanalytics_snowplow_timing_1) t
               WHERE rn > 1);