Drop Type 1 Orders for Customers With Type 0 Orders - Solution & Explanation

Problem Overview: The Orders table stores order_id, customer_id, and order_type (0 or 1). If a customer has at least one type 0 order, all their type 1 orders must be removed. Customers who only have type 1 orders keep them. The result returns the remaining rows sorted by order_id.

Approach 1: Subquery with NOT IN (O(n) time, O(n) space)

This solution first finds all customers who placed a type 0 order using a subquery. Those customers should never keep their type 1 orders. The main query then returns rows where either the order itself is type 0, or the customer_id does not appear in the subquery result. The key insight is treating the list of type 0 customers as an exclusion set. MySQL evaluates the subquery once and filters rows using a membership check, which effectively behaves like an anti-filter. Time complexity is O(n) for scanning the table, with O(n) space for the intermediate customer list.

Approach 2: NOT EXISTS Anti-Join (O(n) time, O(1) extra space)

An alternative uses a correlated subquery with NOT EXISTS. For each order row, the query checks whether another row exists with the same customer_id and order_type = 0. If such a row exists and the current row is type 1, the row should be removed. This method behaves like an anti-join and avoids materializing a separate list of customers. Databases often optimize this pattern well with indexes on customer_id. Time complexity is still roughly O(n) with proper indexing, and extra space is O(1).

Approach 3: LEFT JOIN Filtering (O(n) time, O(n) space)

You can also build a derived table containing all customers who have type 0 orders, then LEFT JOIN it with the original table. Rows where the join finds a match indicate that the customer has a type 0 order. Filtering keeps all rows with order_type = 0 and only keeps type 1 rows when the join result is NULL. This approach explicitly models the relationship between the base table and the set of restricted customers. Time complexity remains O(n), but the join may require O(n) intermediate storage.

This problem is a classic filtering task in database queries using SQL subqueries and anti-join patterns. The main skill tested is recognizing when rows must be excluded based on another row belonging to the same entity (here, the same customer).

Recommended for interviews: The NOT IN or NOT EXISTS approach is the most common solution. Both clearly express the rule: remove type 1 orders if a type 0 order exists for that customer. Interviewers expect you to identify the exclusion condition quickly and translate it into a subquery-based filter.