Duplicate Transactions SQL | Dataford Interview Questions

Syfe's QA team needs to identify potentially duplicated entries in the user ledger before reconciliation. A transaction is considered duplicated when the same user has multiple ledger rows with the same transaction type, amount, currency, and timestamp. The transaction ID and processing status are not part of the duplicate definition.

Write a PostgreSQL query that returns every row belonging to a duplicate group and includes the user's email when available.

Requirements

Use aggregation with GROUP BY and HAVING to identify duplicate transaction groups.
Return the transaction ID, user ID, email, transaction attributes, and the number of rows in its duplicate group.
Include ledger rows whose user record is unavailable, showing a NULL email.
Order results by user_id, occurred_at, and transaction_id.

Schema

ledger_transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique ledger row identifier
user_id	INTEGER	Syfe user identifier
transaction_type	VARCHAR(30)	Ledger event type
amount	NUMERIC(12,2)	Transaction amount
currency	VARCHAR(3)	ISO currency code
occurred_at	TIMESTAMP	Time of the ledger event
processing_status	VARCHAR(20)	Processing state of the ledger row
external_reference	VARCHAR(50)	Reference from the upstream payment system

users

Column	Type	Description
user_idPK	INTEGER	Syfe user identifier
email	VARCHAR(120)	User email address

Your question is Duplicate Transactions SQL. 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