SQL Duplicate Transactions Aggregation | Dataford Interview Questions

Write a SQL query to identify duplicate transactions within a specific timeframe and aggregate their total value.

Use the timeframe from 2025-01-01 00:00:00+00 inclusive through 2025-02-01 00:00:00+00 exclusive. Treat transactions with the same account, merchant reference, timestamp, and amount as duplicates. Rows without a matching account number must be ignored.

Output

One row per duplicate transaction signature.
Return account_number, merchant_reference, transaction_timestamp, amount, duplicate_count, and total_value.
Sort by transaction timestamp ascending, then account number and merchant reference ascending. Rows with a NULL account number are excluded.

Schema

accounts

Column	Type	Description
account_idPK	INT	Internal account identifier
account_number	VARCHAR(20)	Customer-facing bank account number
account_type	VARCHAR(20)	Type of bank account

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
merchant_reference	VARCHAR(30)	Reference supplied by the merchant
transaction_timestamp	TIMESTAMPTZ	Timestamp when the transaction was recorded
amount	NUMERIC(12,2)	Transaction amount in the account currency
status	VARCHAR(20)	Processing status

Your question is SQL Duplicate Transactions Aggregation. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem