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_typeis either0or1
Visualization
Tap to expand
Understanding the Visualization
1
Input
Orders table with mixed order types
2
Analysis
Identify customers with type 0 orders
3
Filter
Remove type 1 orders for customers with type 0
Key Takeaway
🎯 Key Insight: Use window functions to analyze customer order patterns before applying conditional filters
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code