Deduplicate Payments Keeping the Best Record

What you will practice

Payments are duplicates only when merchant_id, external_reference and amount match. Keep one best record per duplicate group using status priority confirmed > pending > failed, then source reliability bank > gateway > import, then processed_at DESC, then payment_id DESC. NULL external references are independent and must not collapse. Return the retained payment rows. Return columns: payment_id, merchant_id, external_reference, amount, status, source, processed_at.

Difficulty

Expert

SQL concept

Window Functions