Duplicate Bank Transactions with Reversal Handling

What you will practice

A reversal points to original_transaction_id and economically cancels that original when account_id matches and reversal amount is its exact negative. Invalid/unmatched reversals are exceptions. Among non-reversed originals, duplicates share account_id, merchant_reference, amount and posted_at; keep the lowest transaction_id and flag later records. Return transaction_id and issue for valid reversals, reversed originals, duplicate copies and invalid reversals; legitimate similar transactions with different timestamps remain effective. Return columns: transaction_id, issue.

Difficulty

Expert

SQL concept

CTE