The task and original case
Reconcile repeated transaction events without deleting separate transactions that happen to have the same amount. The work sample is a deduplication policy and exception report. A smaller row count is useful only when you can explain which real identity each retained row represents.
| Case element | Value or requirement |
|---|---|
| Inputs | Raw events T 1=40,T 1=40,T 2=60,T 3=40;T 1 is repeated transport, T 3 is a separate transaction. |
| Build | A deduplication policy, exception table and revenue reconciliation. |
This is an original invented exercise. It is independent practice, with no employer assessment or open-vacancy claim. Download the inputs, attempt your own result, then open the separate answer key.
Data and worked inputs
Build a result another person can follow
The raw stream contains T1 for 40 twice, T2 for 60 and T3 for 40. Treat the second T1 as repeated transport of the same transaction. T3 is a separate identity even though its amount equals T1's. Declare transaction ID as the business key for this invented case.
Group by identity and inspect whether repeated rows agree on amount and any other retained fields. When identical copies agree, keep one deterministic representative. When copies disagree, quarantine that identity for review rather than choosing an arbitrary value. SQLite row-number windows can make a retention order explicit; they do not establish which copy is authoritative without your rule.
Sources[1]
Check the worked result
Raw event value totals 40 + 40 + 60 + 40 = 180. Retaining T1, T2 and T3 yields 40 + 60 + 40 = 140. The forty-unit difference is one repeated T1 event. A distinct-values sum yields only 40 + 60 = 100 and incorrectly removes T3's contribution.
Show raw count four, retained count three, repeated count one, raw total 180 and retained total 140 together. This reconciliation makes the policy inspectable rather than merely presenting a cleaned spreadsheet.
Make the failure visible
Change T1's second value to 45. The conflicting copies should now appear in an exception table. Also test a missing transaction ID: a shared blank ID must not collapse unrelated transactions. Keep raw rows with source position so that a reviewer can trace the choice. Never assume that identical amount and date establish the same business transaction.
Revise it and ask someone to reproduce it
Ask a peer to add T4 for 40 and predict the new retained total of 180. Your policy should preserve the new identity. Then have them introduce a conflicting T1 copy and check the exception behavior. Revise the key definition or ordering note wherever your partner had to guess.
- Raw, retained and repeated counts reconcile.
- Equal amounts with different IDs remain separate.
- Conflicting or missing identities are reviewed before summation.
Describe the work accurately
After completing your own version, you could adapt this project bullet: “Reconciled a practice event stream using transaction identities; retained equal-value transactions and flagged conflicting duplicates.”
Use Projects or Independent practice as the heading. Keep the input files, result and revision together. Describe only your own completed work; the invented numbers are model outputs, not employer results or measured business impact.