Your question is SQL Duplicate Detection at Scale. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
ingested_at, with record_id as a tie-breaker.original and later copies as duplicate.NULL values.| Column | Type | Description |
|---|---|---|
| record_idPK | BIGINT | Unique ingested record identifier |
| customer_id | VARCHAR(20) | SAS customer identifier |
| event_date | DATE | Date associated with the event |
| event_type | VARCHAR(40) | Event category |
| event_amount | NUMERIC(12,2) | Amount associated with the event |
| ingested_at | TIMESTAMP | Timestamp when the record entered the platform |
| source_system | VARCHAR(30) | Originating ingestion channel |