Skip to content

2084. Drop Type 1 Orders for Customers With Type 0 Orders πŸ”’

Description

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.

Solutions

Solution 1

Thinking

Customers who have a type-0 order must drop all type-1 rows; everyone else keeps theirs. Compute the set of customers with a type-0 order, then keep a row if it is type 0 or its customer is outside that set.

A CTE plus NOT EXISTS encodes the filter.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# Write your MySQL query statement below
WITH
    T AS (
        SELECT DISTINCT customer_id
        FROM Orders
        WHERE order_type = 0
    )
SELECT *
FROM Orders AS o
WHERE order_type = 0 OR NOT EXISTS (SELECT 1 FROM T AS t WHERE t.customer_id = o.customer_id);

Solution 2

Thinking

Solution 1 uses a subquery. Self-join different types of the same customer: a type-1 row that matches a type-0 should drop. Keep rows with no mixed-type partner, or whose partner is type 1 (so the row itself is type 0), then distinct.

Same result via LEFT JOIN.

1
2
3
4
5
6
7
8
SELECT DISTINCT
    a.order_id,
    a.customer_id,
    a.order_type
FROM
    Orders AS a
    LEFT JOIN Orders AS b ON a.customer_id = b.customer_id AND a.order_type != b.order_type
WHERE b.order_type IS NULL OR b.order_type = 1;

Comments