Retain Orders with Customer Priority Rule

Medium

An order-governance team is applying a retention policy before downstream analytics. The policy gives priority to baseline orders (type 0) over promotional orders (type 1) for each customer. Table: Orders order_id: unique order identifier customer_id: customer identifier order_type: order classification (0 or 1) Retention rule: If a customer has at least one order with order_type = 0, then all rows for that customer with order_type = 1 must be excluded. If a customer has no order_type = 0 rows, keep all of that customer's rows. Task: Return the retained rows after applying the policy. Output requirements: Return exactly these columns in the result: order_id, customer_id, order_type Sort by order_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Orders:
| order_id | customer_id | order_type |
|----------|-------------|------------|
| 1        | 1           | 0          |
| 2        | 1           | 1          |
| 3        | 2           | 1          |
| 4        | 2           | 1          |
| 5        | 3           | 0          |
| 6        | 3           | 1          |

Output

[
  {"order_id":1,"customer_id":1,"order_type":0},
  {"order_id":3,"customer_id":2,"order_type":1},
  {"order_id":4,"customer_id":2,"order_type":1},
  {"order_id":5,"customer_id":3,"order_type":0}
]

Explanation

Customers 1 and 3 each have at least one type-0 order, so their type-1 rows are removed. Customer 2 has only type-1 orders, so both rows are retained.

Example 2

Input

Orders:
| order_id | customer_id | order_type |
|----------|-------------|------------|
| 10       | 7           | 1          |
| 11       | 7           | 1          |
| 12       | 8           | 1          |

Output

[
  {"order_id":10,"customer_id":7,"order_type":1},
  {"order_id":11,"customer_id":7,"order_type":1},
  {"order_id":12,"customer_id":8,"order_type":1}
]

Explanation

No customer has a type-0 row, so all rows remain.

Example 3

Input

Orders:
[]

Output

[]

Explanation

No input rows means no output rows.

Constraints

0 <= number of rows in Orders <= 1800
1 <= order_id <= 2000000000
1 <= customer_id <= 1000000000
order_type is either 0 or 1
order_id values are unique
If a customer has any row with order_type = 0, all that customer's order_type = 1 rows must be excluded
Return exactly these columns: order_id, customer_id, order_type
Sort final output by order_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly three columns: order_id, customer_id, order_type

Orders: | order_id | customer_id | order_type | |----------|-------------|------------| | 1 | 1 | 0 | | 2 | 1 | 1 | | 3 | 2 | 1 | | 4 | 2 | 1 | | 5 | 3 | 0 | | 6 | 3 | 1 |

Orders: | order_id | customer_id | order_type | |----------|-------------|------------| | 10 | 7 | 1 | | 11 | 7 | 1 | | 12 | 8 | 1 |