39. How would you list customers who have no completed orders, while retaining customers with failed or cancelled orders?
EasySQL
Technical
Show model answer
Start from the customer population and exclude customers for whom a completed order exists. A correlated NOT EXISTS query expresses that condition without multiplying customer rows when several completed orders share a customer.
Put the completed-status condition inside the existence check. Verify customers with no orders, only cancelled orders, and both completed and cancelled orders. Those cases distinguish the business rule from simply finding customers absent from all orders.
What interviewers listen for
- Define the completed-order condition
- Use an existence-based exclusion
- Test mixed order statuses