Detect Duplicate Records in SQL | Dataford Interview Questions

Your question is Detect Duplicate Records in SQL. Start with the requirements and the two tables 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.

Problem

Finacle transaction ingestion can receive the same banking transaction more than once from core banking, ATM, or mobile channels. Write a PostgreSQL query to identify duplicate active records in finacle_transaction_ingest.

Requirements

Treat records as duplicates when customer_id, txn_reference, txn_date, amount, currency, and transaction_type match. A NULL reference must match another NULL reference.
Include only records with processing_status of POSTED or PENDING; exclude reversed records.
Return every duplicate record, its duplicate count, the customer name when available, and sort deterministically by customer, reference, date, and transaction ID.

Schema

finacle_transaction_ingest

Column	Type	Description
transaction_idPK	BIGINT	Unique ingested transaction record ID
customer_id	VARCHAR(20)	Finacle customer identifier
txn_reference	VARCHAR(40)	Source transaction reference
txn_date	DATE	Banking transaction date
amount	NUMERIC(14,2)	Transaction amount
currency	VARCHAR(3)	ISO currency code
transaction_type	VARCHAR(20)	Debit or credit
processing_status	VARCHAR(20)	Processing state
source_system	VARCHAR(20)	Originating channel or system

finacle_customers

Column	Type	Description
customer_idPK	VARCHAR(20)	Finacle customer identifier
customer_name	VARCHAR(120)	Customer display name
segment	VARCHAR(30)	Customer segment