SQL Duplicate Detection at Scale

SAS Customer Intelligence 360 receives event data from multiple ingestion channels. Write a PostgreSQL query that identifies repeated event records in the customer_events table, which may contain millions of rows.

Treat rows with the same customer_id, event_date, event_type, and event_amount as one business event. The record_id and ingested_at values identify individual ingested copies.

Requirements

1. Return every row belonging to a duplicate business-event group, including the first-ingested copy.
2. Show the number of records in each duplicate group and rank records by ingested_at, with record_id as a tie-breaker.
3. Label the earliest copy as original and later copies as duplicate.
4. Use a CTE and window functions. The query must retain groups where nullable business-key columns have matching NULL values.