00:00:00

Description

Editorial

Retain Orders with Customer Priority Rule

MEDIUM20 pts

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

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

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

Input

Orders:
[]

Output

[]

Explanation

No input rows means no output rows.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Orders =

[{"order_id":12,"customer_id":2,"order_type":1},{"order_id":1,"customer_id":1,"order_type":0},{"order_id":2,"customer_id":1,"order_type":0},{"order_id":22,"customer_id":3,"order_type":0},{"order_id":32,"customer_id":4,"order_type":1},{"order_id":21,"customer_id":3,"order_type":1},{"order_id":11,"customer_id":2,"order_type":0},{"order_id":31,"customer_id":4,"order_type":1}]

Loading problem...

101

00:00:00

Description

Editorial

Retain Orders with Customer Priority Rule

MEDIUM20 pts

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

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

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

Input

Orders:
[]

Output

[]

Explanation

No input rows means no output rows.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Orders =

Retain Orders with Customer Priority Rule

Hints

Retain Orders with Customer Priority Rule

Hints