Write a query to find the average order value per customer segment, excluding cancelled orders.

Answer

Filter out cancelled orders before aggregating, then average order value grouped by customer segment.

Solution

SELECT c.segment, AVG(o.order_value) AS avg_order_value
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
WHERE o.status <> 'cancelled'
GROUP BY c.segment;

Step by Step

1. Join Orders to Customers to bring in each order's segment.
2. WHERE o.status <> 'cancelled' removes cancelled orders before any aggregation happens.
3. GROUP BY c.segment then computes one average per segment using only the remaining valid orders.

Alternative Method: Conditional Aggregation

SELECT c.segment,
       AVG(CASE WHEN o.status <> 'cancelled' THEN o.order_value END) AS avg_order_value
FROM Orders o
JOIN Customers c ON c.customer_id = o.customer_id
GROUP BY c.segment;

This CASE-based version keeps cancelled rows in the join but excludes them from the average via NULL (since AVG() ignores NULLs automatically) - functionally equivalent here, but useful when you also need other aggregates over the full row set in the same query, avoiding a second pass.

Key Exam Traps

• WHERE before GROUP BY, always: Filtering with WHERE excludes rows before aggregation; filtering with HAVING would apply after grouping, which is the wrong tool here since "excluding cancelled orders" is a row-level condition, not a group-level one.
• NULL handling in AVG(): AVG() silently ignores NULL values rather than treating them as 0 - this is exactly why the CASE-based alternative works correctly, but it's a common point of confusion if someone expects NULLs to drag the average down.
• Refunded orders: The question only mentions "cancelled," but real datasets often also have "refunded" as a separate status that arguably shouldn't count either - worth clarifying the exact status values that qualify as excluded.