Expert
Window Functions
Pro Challenge

Deduplicate Payments Keeping the Best Record

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.

Preparing the interactive SQL workspace...

Challenge overview

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