Drop Type 1 Orders for Customers With Type 0 Orders - Problem

Given a table Orders with order information, write a solution to report 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 orders of the customer

Return the result table in any order.

Table Schema

Orders
Column Name Type Description
order_id PK int Unique identifier for each order
customer_id int ID of the customer who placed the order
order_type int Type of order (0 or 1)
Primary Key: order_id
Note: Each row represents an order with its type (0 or 1)

Input & Output

Example 1 — Customer with Both Order Types
Input Table:
order_id customer_id order_type
1 1 0
2 1 1
3 2 1
4 3 0
Output:
order_id customer_id order_type
1 1 0
3 2 1
4 3 0
💡 Note:

Customer 1 has both type 0 (order 1) and type 1 (order 2) orders. Since customer 1 has type 0, we exclude their type 1 order. Customer 2 only has type 1, so we keep it. Customer 3 only has type 0, so we keep it.
Example 2 — All Type 1 Orders Only
Input Table:
order_id customer_id order_type
1 1 1
2 2 1
3 3 1
Output:
order_id customer_id order_type
1 1 1
2 2 1
3 3 1
💡 Note:

All customers only have type 1 orders and no type 0 orders, so we keep all orders since none of the customers have type 0 orders.
Example 3 — Multiple Type 0 Orders
Input Table:
order_id customer_id order_type
1 1 0
2 1 0
3 1 1
4 2 0
Output:
order_id customer_id order_type
1 1 0
2 1 0
4 2 0
💡 Note:

Customer 1 has multiple type 0 orders (orders 1, 2) and one type 1 order (order 3). Since customer 1 has type 0 orders, we exclude their type 1 order but keep all type 0 orders.

Constraints

  • 1 ≤ order_id ≤ 1000
  • 1 ≤ customer_id ≤ 1000
  • order_type is either 0 or 1

Visualization

Tap to expand
Drop Type 1 Orders for Customers With Type 0 Orders INPUT: Orders Table id cust_id type qty 1 1 0 10 2 1 1 20 3 2 1 15 4 1 1 5 5 3 1 25 Customer has type 0 Customer has NO type 0 Customer Groups: Cust 1 Cust 2 Cust 3 ALGORITHM STEPS 1 Find Type 0 Customers Subquery: SELECT cust_id WHERE type = 0 2 Filter Logic IF type=0: KEEP order IF type=1: Check customer 3 Apply Condition type=1 AND cust NOT IN (type 0 customers) --> KEEP 4 Drop Type 1 Orders type=1 AND cust IN (type 0 customers) --> DROP SELECT * FROM Orders WHERE type = 0 OR (type = 1 AND cust_id NOT IN (...)); -- Subquery finds type 0 custs FINAL RESULT id cust_id type qty 1 1 0 10 OK 3 2 1 15 OK 5 3 1 25 OK DROPPED Orders: 2 1 1 20 DROP 4 1 1 5 DROP Summary: Customer 1: Keep type 0 Customer 1: Drop type 1s Customers 2,3: Keep all Key Insight: Use a subquery to identify customers with type 0 orders, then use NOT IN to conditionally filter type 1 orders. The WHERE clause combines: (type = 0) OR (type = 1 AND customer_id NOT IN subquery). This ensures type 0 orders are always kept, while type 1 orders are only kept for customers without any type 0 orders. TutorialsPoint - Drop Type 1 Orders for Customers With Type 0 Orders | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
28.5K Views
Medium Frequency
~12 min Avg. Time
847 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen