sql result repair agent

The query was failing due to a many-to-many fan-out where order_items multiplied the orders grain before aggregation. I have moved the item-level aggregation into a CTE to preserve the order grain.

SQL Repair Report

Relational Contract

• Intended grain: One row per customer_id.
• Row preservation: All customers must be present, even those with zero orders.
• Join cardinality: 1:Many (Customer to Orders), 1:Many (Orders to Items).
• Aggregation: Sum of item_price grouped by customer_id.

Defect Isolation

The original query joined orders and order_items directly in the main block. Because a single order has multiple items, the order.total (if used) or the join itself was duplicating the parent record before the final group by, leading to inflated sums.

Minimal Repair

• Old: FROM customers c LEFT JOIN orders o ON ... LEFT JOIN order_items i ON ...

New: Wrap order_items in a subquery or CTE that aggregates to the order_id grain first, then join to customers.

Verification Plan

Residual Risk

Data-dependent risk: If an order exists in orders but has no entries in order_items, the current inner join in the CTE will exclude that order's value.

• NULL semantics: Assumes item_price is NOT NULL; otherwise, SUM behavior might vary.

Final SQL (PostgreSQL)

WITH daily_order_totals AS (
    SELECT 
        order_id, 
        customer_id,
        SUM(price) AS order_total
    FROM order_items
    GROUP BY 1, 2
)
SELECT 
    c.customer_name,
    COALESCE(SUM(dot.order_total), 0) AS total_spent
FROM customers c
LEFT JOIN daily_order_totals dot ON c.customer_id = dot.customer_id
GROUP BY c.customer_id, c.customer_name;

Next steps

1. Run the Verification Plan table queries against your dev environment.
2. Confirm if customers with empty orders should show as $0.00 or NULL.
3. Check the EXPLAIN plan to ensure the CTE is being materialized efficiently.