P88SQLIntermediateDesign14 min
Design a SQL Deduplication Step Before Revenue Aggregation
Deduplicate versioned transactions before aggregating revenue.
#design#sql#deduplication
Scenario
An event table contains multiple versions of each transaction_id after status updates. Summing completed rows directly inflates monthly revenue.
Problem statement
Design a query that selects the latest valid record per transaction before revenue aggregation.
Your Task
- Define the logical transaction key.
- Use a window function to choose a canonical record.
- Filter and aggregate only after deduplication.
Table
transaction_events(transaction_id, event_updated_at, status, amount, event_date)
Related Concepts
SQL window functionsData grainRevenue metrics
Ready to count this problem?
Mark it complete when you have reasoned through the solution in your own words.