Deduplicate Transactions Across Channels

MediumSQL · PostgreSQL

Problem

Wayfair receives transaction records from multiple fulfillment channels, including Wayfair.com, the Wayfair app, and CastleGate. The same transaction may appear more than once when records are passed through multiple channels.

Write a PostgreSQL query that returns one canonical record for each non-null transaction reference, keeping the earliest transaction by timestamp. Include the number of records found for each transaction reference so duplicate groups can be identified.

Requirements

Join transactions to their fulfillment channel names.
Exclude records with a null transaction reference or no matching fulfillment channel.
Use a window function to rank records by transaction_timestamp, using transaction_id as a deterministic tie-breaker.
Return only the earliest record for each transaction reference, ordered chronologically.

Schema

payment_transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction record identifier
transaction_reference	VARCHAR(30)	Business reference shared by duplicate records
order_id	INT	Wayfair order identifier
amount	DECIMAL(10,2)	Transaction amount
transaction_timestamp	TIMESTAMP	Time the channel record was received
channel_id	INT	References fulfillment_channels.channel_id

fulfillment_channels

Column	Type	Description
channel_idPK	INT	Fulfillment channel identifier
channel_name	VARCHAR(50)	Name of the fulfillment channel

Tablespayment_transactionsfulfillment_channels