Identify and Aggregate Duplicate Transaction Records

MediumSQL · PostgreSQL

Problem

Write a SQL query to identify duplicate records in a large dataset across multiple tables.

Use the supplied transaction, account, and channel data. Treat transactions with the same account, date, amount, merchant, and channel as duplicates. Exclude transactions whose account or channel cannot be matched.

Output

One row per duplicated business key
Columns: account_id, transaction_date, amount, merchant_name, channel_name, duplicate_count, and transaction_ids
Include only keys occurring more than once
Order by account_id, transaction_date, amount, merchant_name, and channel_name

Output details

Sort all listed ordering columns in ascending order, placing NULL transaction_date values last.
Aggregate transaction_ids in ascending numeric order, separated by ', '.
Treat NULL values in business-key columns as belonging to the same duplicate group.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_reference	VARCHAR(20)	Masked account reference
customer_segment	VARCHAR(30)	Customer segment associated with the account

transaction_channels

Column	Type	Description
channel_idPK	INT	Unique transaction channel identifier
channel_name	VARCHAR(30)	Channel through which the transaction was initiated

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
channel_id	INT	Transaction channel identifier
transaction_date	DATE	Business date of the transaction
amount	NUMERIC(12,2)	Transaction amount
merchant_name	VARCHAR(80)	Merchant or transaction description

Tablestransactionsaccountstransaction_channels