← SQL roadmap

Topic 52 of 52 · Expert

Reconcile latest versioned payments before aggregation

For each payment ID keep the highest version, then sum amount per customer.

Architect / SpecialistSpecialist · 8–10+ yearsIncremental data quality

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.

  1. Validate same-version conflicts in ingestion.
  2. Rank each payment by descending version.
  3. Keep one row per payment.
  4. 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?
Practise SQL with the AI mentor

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.