Debugging · Advanced · 35 min
A revenue view doubles its totals
Find the table-grain defect before changing SUM.
Task
A reporting view joins orders to order_items and payments. An order worth 1,000 cents has two items and two successful payment records totaling 1,000 cents. The view reports 2,000 cents for that order.
Requirements
- Orders have one row per order. Items have one row per order line. Payments have one row per payment attempt.
- Only successful payments contribute revenue. Keep an order with no successful payment and report zero.
- Explain the multiplication in the supplied query, propose a corrected view, and describe a regression fixture.
- Do not use SUM(DISTINCT amount_cents): two legitimate payments can have equal amounts.
- Return order_id and revenue_cents ordered by order_id. Use SQLite.
Join cardinalitySQL debuggingRegression testing
Loading exercise…