Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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.

Solution

Method 1 – SQL Anti-Join (NOT EXISTS)

Intuition

To exclude type 1 orders for customers who have at least one type 0 order, we can use an anti-join: for each order, if it is type 1, check that there is no type 0 order for the same customer. Otherwise, always include type 0 orders.

Approach

  1. Select all orders of type 0.
  2. Select all orders of type 1 where the customer does not have any type 0 order (using NOT EXISTS or NOT IN).
  3. Union the two result sets.
  4. Return the result in any order.

Code

1
2
3
4
5
6
7

SELECT * FROM Orders WHERE order_type = 0
UNION ALL
SELECT * FROM Orders o1
WHERE order_type = 1
  AND NOT EXISTS (
    SELECT 1 FROM Orders o2 WHERE o2.customer_id = o1.customer_id AND o2.order_type = 0
  );
1
2
3
4
5
6
7

SELECT * FROM Orders WHERE order_type = 0
UNION ALL
SELECT * FROM Orders o1
WHERE order_type = 1
  AND NOT EXISTS (
    SELECT 1 FROM Orders o2 WHERE o2.customer_id = o1.customer_id AND o2.order_type = 0
  );
1
2
3
4
5
6

import pandas as pd

def drop_type1_orders_for_customers_with_type0_orders(orders: pd.DataFrame) -> pd.DataFrame:
    type0_customers = set(orders.loc[orders['order_type'] == 0, 'customer_id'])
    mask = (orders['order_type'] == 0) | (~orders['customer_id'].isin(type0_customers))
    return orders[mask]

Complexity

  • ⏰ Time complexity: O(n), where n is the number of orders.
  • 🧺 Space complexity: O(n), for the output table.