Resolving Duplicate Transactions

Amazon: Find duplicate transaction records, decide which to keep, and report the cleaned revenue total.

Assignment

The retail operations team has exported a month of customer transactions for a revenue reconciliation, and wants a quick analysis before the numbers are compared with the payment system. They suspect the export contains duplicate records.

Deliverables

• The duplicate records in the data identified.
• A clear rule for which records to keep and which to remove, with the reasoning behind it.
• The total revenue after the data is cleaned.
• A short closing report of your findings for the retail operations team, including anything they should double-check before relying on the numbers.

Data Description

amazon_customer_transactions.csv has one row per transaction record:

• transaction_id
• customer_id
• product
• amount
• order_timestamp - when the order was placed
• recorded_at - when the record was written by the system

Practicalities

You can solve this in Python, SQL, or a mix of both — StrataScratch notebooks support both languages in the same notebook.

Assume this data comes directly from the order system and hasn't been cleaned, so review it carefully before relying on the numbers. When something looks off, work out what it is and decide how to handle it; when a definition or a final decision is genuinely ambiguous, choose a reasonable option and explain why. There's often more than one valid way to compute a result, so explain the approach you take and the reasoning behind your choices.

What's assessed: your results, and how clearly you explain what you see in the data and justify the choices you make.