Skip to main content
Talentd

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

MediumPremiumFree on FleetCodeDatabase5 min read
Practice this problem

Problem Statement

Table: Orders

+-------------+------+
| Column Name | Type |
+-------------+------+
| order_id    | int  | 
| customer_id | int  |
| order_type  | int  | 
+-------------+------+
order_id is the column with unique values for this table.
Each row of this table indicates the ID of an order, the ID of the customer who ordered it, and the order type.
The orders could be of type 0 or type 1.

 

Write a solution to report all the orders based on the following criteria:

  • If a customer has at least one order of type 0, do not report any order of type 1 from that customer.
  • Otherwise, report all the orders of the customer.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input:
Orders table:
+----------+-------------+------------+
| order_id | customer_id | order_type |
+----------+-------------+------------+
| 1        | 1           | 0          |
| 2        | 1           | 0          |
| 11       | 2           | 0          |
| 12       | 2           | 1          |
| 21       | 3           | 1          |
| 22       | 3           | 0          |
| 31       | 4           | 1          |
| 32       | 4           | 1          |
+----------+-------------+------------+
Output:
+----------+-------------+------------+
| order_id | customer_id | order_type |
+----------+-------------+------------+
| 31       | 4           | 1          |
| 32       | 4           | 1          |
| 1        | 1           | 0          |
| 2        | 1           | 0          |
| 11       | 2           | 0          |
| 22       | 3           | 0          |
+----------+-------------+------------+
Explanation:
Customer 1 has two orders of type 0. We return both of them.
Customer 2 has one order of type 0 and one order of type 1. We only return the order of type 0.
Customer 3 has one order of type 0 and one order of type 1. We only return the order of type 0.
Customer 4 has two orders of type 1. We return both of them.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
NOT IN SubqueryO(n)O(n)Clean and readable when excluding rows based on a list of matching customer IDs
NOT EXISTS Anti-JoinO(n)O(1)Preferred when correlated checks are needed and indexes exist on customer_id
LEFT JOIN FilteringO(n)O(n)Useful when transforming the exclusion logic into an explicit join for readability

Video Solution

LeetCode Medium 2084 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 10,284 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is Drop Type 1 Orders for Customers With Type 0 Orders easy or hard?
The problem is generally considered Medium difficulty because it requires understanding relational filtering logic. The SQL itself is short, but recognizing that type 1 orders must be removed when a type 0 order exists for the same customer requires careful reasoning.
Drop Type 1 Orders for Customers With Type 0 Orders Python/Java solution
This is a database problem rather than an algorithmic coding problem in Python or Java. The expected answer is a SQL query that filters rows using subqueries, anti-joins, or joins in systems like MySQL, PostgreSQL, or SQL Server.
How to solve Drop Type 1 Orders for Customers With Type 0 Orders in O(n)?
Select all rows where order_type = 0, since those must always remain. For type 1 rows, keep them only if the customer_id does not appear among customers who have a type 0 order. This can be implemented using a NOT IN subquery or a NOT EXISTS anti-join, both of which run in linear scan time with proper indexing.
What is the best approach for Drop Type 1 Orders for Customers With Type 0 Orders?
The most common solution uses a SQL subquery with NOT IN or NOT EXISTS. First identify customers who have at least one type 0 order, then filter out type 1 orders belonging to those customers. This approach scans the table once and performs an exclusion check, giving roughly O(n) time complexity in MySQL.
Is Drop Type 1 Orders for Customers With Type 0 Orders asked at Google/Amazon/Meta?
This problem represents a common SQL filtering pattern used in real database interviews at companies like Amazon, Meta, and Google. The main skill tested is writing subqueries or anti-joins to remove rows based on related rows from the same entity.
What data structure is used in Drop Type 1 Orders for Customers With Type 0 Orders?
The problem is solved using SQL query constructs rather than traditional in-memory data structures. Internally, the database may treat the subquery result as a temporary set or hash structure when evaluating NOT IN or NOT EXISTS conditions.
What is the time complexity of Drop Type 1 Orders for Customers With Type 0 Orders?
The typical SQL solution runs in O(n) time where n is the number of rows in the Orders table. The database scans the table and checks membership against a subquery result or correlated condition. Space complexity ranges from O(1) to O(n) depending on whether the engine materializes the subquery.

Ready to solve this problem?

Practice Drop Type 1 Orders for Customers With Type 0 Orders with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary