Topic 52 of 52 · Expert
Reconcile latest versioned payments before aggregation
For each payment ID keep the highest version, then sum amount per customer.
MySQL 8-compatible SELECT syntax; sample queries also execute in SQLite 3. Window functions require MySQL 8+.
Problem statement
For each payment ID keep the highest version, then sum amount per customer.
A replayed event log contains old versions and exact replay duplicates; sums must not double-count.
Input: payments(payment_id,version,customer_id,amount); identical key/version rows have identical attributes.
Output: customer_id, total ordered by customer.
- Conflicting payloads at the same version must be rejected upstream.
- Payment versions are positive integers.
- Customer ID may change between versions; latest version wins.
Sample 1
Input: p1:v1 c1=10,v2 c1=15; p2:v1 c1=5 Output: c1=20
Only latest p1 plus p2 count.
Sample 2
Input: p1:v2 c1=15 repeated twice Output: c1=15
A replay is counted once.
Brute-force solution
Join each record to a correlated maximum version, then deduplicate identical rows before summing.
O(n log n) for version sorting.
CREATE TABLE payments(payment_id INTEGER, version INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES(1,1,1,10),(1,2,1,15),(1,2,1,15),(2,1,1,5),(3,1,2,7);
WITH r AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY payment_id ORDER BY version DESC) rn FROM payments) SELECT customer_id,SUM(amount) total FROM r WHERE rn=1 GROUP BY customer_id ORDER BY customer_id;Optimized solution
ROW_NUMBER selects one latest row per payment; equal-version payload identity makes replay selection harmless under the stated contract.
- Validate same-version conflicts in ingestion.
- Rank each payment by descending version.
- Keep one row per payment.
- Group surviving amounts by latest customer.
CREATE TABLE payments(payment_id INTEGER, version INTEGER, customer_id INTEGER, amount INTEGER);
INSERT INTO payments VALUES(1,1,1,10),(1,2,1,15),(1,2,1,15),(2,1,1,5),(3,1,2,7);
WITH r AS(SELECT *,ROW_NUMBER() OVER(PARTITION BY payment_id ORDER BY version DESC) rn FROM payments) SELECT customer_id,SUM(amount) total FROM r WHERE rn=1 GROUP BY customer_id ORDER BY customer_id;Complexity analysis
Time: O(n log n) for version sorting.
Auxiliary space: O(n) window workspace.
Test cases
- Exact replays.
- Out-of-order versions.
- Conflicting same-version payloads require validation.
Expected: (1,20),(2,7).Common mistakes
- Summing the event log before deduplication.
- Selecting latest per customer rather than payment.
Key takeaways
- Separate event grain from entity grain.
- Declare conflict policy.
- Reconcile replayed input deterministically.
Follow-up challenges
- How would deletion events be represented?
- How do you audit same-version conflicts before publication?