LeetCode 2084. Drop Type 1 Orders for Customers with Type 0 Orders SQL Solution

Problem

LeetCode SQL Problem

2084. Drop Type 1 Orders for Customers with Type 0 Orders

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

Solution - Self Join, CTE, Subquery

MySQL

Drop Type 1 Orders for Customers with Type 0 Orders

-- Find customers that have at least one order of type 0.
-- Do not return any order of type 1 from these customers
WITH CustomerIds_OnlyReturnTypeZero
AS (
	SELECT DISTINCT (L.customer_id) AS customer_id
	FROM Orders AS L
	INNER JOIN Orders AS R ON L.order_id < R.order_id
		AND L.customer_id = R.customer_id
	WHERE L.order_type - R.order_type <> 0
	)
SELECT *
FROM Orders AS O
WHERE O.order_type = 0 -- Always return type 0 order
	OR (
		-- Only return type 1 order if the customer does not have any type 0 order
		O.order_type = 1
		AND O.customer_id NOT IN (
			SELECT customer_id
			FROM CustomerIds_OnlyReturnTypeZero
			)
		)

TSQL

Drop Type 1 Orders for Customers with Type 0 Orders

-- Find customers that have at least one order of type 0.
-- Do not return any order of type 1 from these customers
WITH CustomerIds_OnlyReturnTypeZero
AS (
	SELECT DISTINCT (L.customer_id) AS customer_id
	FROM Orders AS L
	INNER JOIN Orders AS R ON L.order_id < R.order_id
		AND L.customer_id = R.customer_id
	WHERE L.order_type - R.order_type <> 0
	)
SELECT *
FROM Orders AS O
WHERE O.order_type = 0 -- Always return type 0 order
	OR (
		-- Only return type 1 order if the customer does not have any type 0 order
		O.order_type = 1
		AND O.customer_id NOT IN (
			SELECT customer_id
			FROM CustomerIds_OnlyReturnTypeZero
			)
		)

Query Output

order_id	customer_id	order_type
1	1	0
2	1	0
11	2	0
22	3	0
31	4	1
32	4	1

Solution - Group By, CTE, Subquery

MySQL

Drop Type 1 Orders for Customers with Type 0 Orders

-- Find customers that have at least one order of type 0.
-- Do not return any order of type 1 from these customers
WITH CustomerIds_OnlyReturnTypeZero
AS (
	SELECT DISTINCT (L.customer_id) AS customer_id
	FROM Orders AS L
	INNER JOIN Orders AS R ON L.order_id < R.order_id
		AND L.customer_id = R.customer_id
	WHERE L.order_type - R.order_type <> 0
	)
SELECT *
FROM Orders AS O
WHERE O.order_type = 0 -- Always return type 0 order
	OR (
		-- Only return type 1 order if the customer does not have any type 0 order
		O.order_type = 1
		AND O.customer_id NOT IN (
			SELECT customer_id
			FROM CustomerIds_OnlyReturnTypeZero
			)
		)

TSQL

Drop Type 1 Orders for Customers with Type 0 Orders

-- Find customers that have at least one order of type 0.
-- Do not return any order of type 1 from these customers
WITH CustomerIds_OnlyReturnTypeZero
AS (
	SELECT DISTINCT (L.customer_id) AS customer_id
	FROM Orders AS L
	INNER JOIN Orders AS R ON L.order_id < R.order_id
		AND L.customer_id = R.customer_id
	WHERE L.order_type - R.order_type <> 0
	)
SELECT *
FROM Orders AS O
WHERE O.order_type = 0 -- Always return type 0 order
	OR (
		-- Only return type 1 order if the customer does not have any type 0 order
		O.order_type = 1
		AND O.customer_id NOT IN (
			SELECT customer_id
			FROM CustomerIds_OnlyReturnTypeZero
			)
		)

Query Output

order_id	customer_id	order_type
1	1	0
2	1	0
11	2	0
22	3	0
31	4	1
32	4	1

Solution - Correlated Subquery

MySQL

Drop Type 1 Orders for Customers with Type 0 Orders

SELECT *
FROM Orders AS O
WHERE O.order_type = 0 -- Always return type 0 order
	OR (
		-- Only return type 1 order if the customer does not have any type 0 order
		O.order_type = 1
		AND NOT EXISTS (
			SELECT *
			FROM Orders S
			WHERE S.customer_id = O.customer_id
				AND S.order_type = 0
			)
		)

TSQL

Drop Type 1 Orders for Customers with Type 0 Orders

SELECT *
FROM Orders AS O
WHERE O.order_type = 0 -- Always return type 0 order
	OR (
		-- Only return type 1 order if the customer does not have any type 0 order
		O.order_type = 1
		AND NOT EXISTS (
			SELECT *
			FROM Orders S
			WHERE S.customer_id = O.customer_id
				AND S.order_type = 0
			)
		)

Query Output

order_id	customer_id	order_type
1	1	0
2	1	0
11	2	0
22	3	0
31	4	1
32	4	1