Inactive Sellers in 2020

Easy

A commerce analytics team needs a clean roster of sellers who had no completed sales during calendar year 2020. Table: customer customer_id: unique customer identifier customer_name: customer display name Table: orders order_id: unique order identifier sale_date: date the order was recorded order_cost: amount for the order customer_id: customer linked to the order seller_id: seller linked to the order Table: seller seller_id: unique seller identifier seller_name: seller display name Task: Return every seller who has zero orders in the 2020 calendar year. Business rules: If a seller has at least one order with sale_date in 2020, that seller must be excluded. Orders outside 2020 do not exclude a seller. Data quality note: some orders may have NULL seller_id. Such rows do not map to any seller and should not exclude anyone. Output requirements: Return exactly one column named seller_name. Sort by seller_name in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customer:
| customer_id | customer_name |
|-------------|---------------|
| 101         | Ava           |
| 102         | Ben           |

orders:
| order_id | sale_date  | order_cost | customer_id | seller_id |
|----------|------------|------------|-------------|-----------|
| 1        | 2020-02-14 | 1200       | 101         | 1         |
| 2        | 2019-07-19 | 700        | 102         | 2         |
| 3        | 2020-11-05 | 1600       | 102         | 2         |
| 4        | 2021-01-07 | 900        | 101         | 3         |

seller:
| seller_id | seller_name |
|-----------|-------------|
| 1         | Iris        |
| 2         | Mason       |
| 3         | Noor        |

Output

[
  {"seller_name":"Noor"}
]

Explanation

Iris and Mason each have at least one order in 2020. Noor only appears in 2021, so Noor is the only inactive seller for 2020.

Example 2

Input

customer:
| customer_id | customer_name |
|-------------|---------------|
| 11          | Kai           |
| 12          | Lina          |

orders:
| order_id | sale_date  | order_cost | customer_id | seller_id |
|----------|------------|------------|-------------|-----------|
| 101      | 2018-06-01 | 500        | 11          | 10        |
| 102      | 2019-12-31 | 650        | 12          | 11        |
| 103      | 2021-03-20 | 800        | 11          | 12        |

seller:
| seller_id | seller_name |
|-----------|-------------|
| 10        | Aria        |
| 11        | Devon       |
| 12        | Blake       |

Output

[
  {"seller_name":"Aria"},
  {"seller_name":"Blake"},
  {"seller_name":"Devon"}
]

Explanation

There are no 2020 orders at all, so every seller is returned and sorted alphabetically.

Example 3

Input

customer:
| customer_id | customer_name |
|-------------|---------------|
| 201         | Nina          |
| 202         | Omar          |

orders:
| order_id | sale_date  | order_cost | customer_id | seller_id |
|----------|------------|------------|-------------|-----------|
| 501      | 2020-03-01 | 900        | 201         | 21        |
| 502      | 2020-06-04 | 300        | 202         | null      |
| 503      | 2020-07-09 | 450        | 201         | 9999      |
| 504      | 2019-08-10 | 700        | 202         | 22        |
| 505      | 2021-01-02 | 880        | 201         | 23        |

seller:
| seller_id | seller_name |
|-----------|-------------|
| 21        | Owen        |
| 22        | Zed         |
| 23        | Zed         |

Output

[
  {"seller_name":"Zed"},
  {"seller_name":"Zed"}
]

Explanation

Seller 21 is excluded due to a 2020 sale. The null and unknown seller_id rows do not map to known sellers. Sellers 22 and 23 both remain and share the same display name.

Constraints

1 <= number of rows in customer <= 300000
1 <= number of rows in seller <= 300000
0 <= number of rows in orders <= 500000
customer.customer_id is unique
seller.seller_id is unique
orders.order_id is unique
sale_date values are valid calendar dates
0 <= order_cost <= 1000000000
seller_name values are not guaranteed to be unique
In noisy feeds, orders.seller_id may be null and should not map to a seller
Return exactly one column: seller_name
Sort output by seller_name ascending
Use SQLite 3.27.2 behavior for SQL submissions
For Pandas submissions, return a DataFrame with column seller_name

Loading problem...

Preparing your editor. You can read the problem while it loads.

Inactive Sellers in 2020

Easy

A commerce analytics team needs a clean roster of sellers who had no completed sales during calendar year 2020. Table: customer customer_id: unique customer identifier customer_name: customer display name Table: orders order_id: unique order identifier sale_date: date the order was recorded order_cost: amount for the order customer_id: customer linked to the order seller_id: seller linked to the order Table: seller seller_id: unique seller identifier seller_name: seller display name Task: Return every seller who has zero orders in the 2020 calendar year. Business rules: If a seller has at least one order with sale_date in 2020, that seller must be excluded. Orders outside 2020 do not exclude a seller. Data quality note: some orders may have NULL seller_id. Such rows do not map to any seller and should not exclude anyone. Output requirements: Return exactly one column named seller_name. Sort by seller_name in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

customer:
| customer_id | customer_name |
|-------------|---------------|
| 101         | Ava           |
| 102         | Ben           |

orders:
| order_id | sale_date  | order_cost | customer_id | seller_id |
|----------|------------|------------|-------------|-----------|
| 1        | 2020-02-14 | 1200       | 101         | 1         |
| 2        | 2019-07-19 | 700        | 102         | 2         |
| 3        | 2020-11-05 | 1600       | 102         | 2         |
| 4        | 2021-01-07 | 900        | 101         | 3         |

seller:
| seller_id | seller_name |
|-----------|-------------|
| 1         | Iris        |
| 2         | Mason       |
| 3         | Noor        |

Output

[
  {"seller_name":"Noor"}
]

Explanation

Iris and Mason each have at least one order in 2020. Noor only appears in 2021, so Noor is the only inactive seller for 2020.

Example 2

Input

customer:
| customer_id | customer_name |
|-------------|---------------|
| 11          | Kai           |
| 12          | Lina          |

orders:
| order_id | sale_date  | order_cost | customer_id | seller_id |
|----------|------------|------------|-------------|-----------|
| 101      | 2018-06-01 | 500        | 11          | 10        |
| 102      | 2019-12-31 | 650        | 12          | 11        |
| 103      | 2021-03-20 | 800        | 11          | 12        |

seller:
| seller_id | seller_name |
|-----------|-------------|
| 10        | Aria        |
| 11        | Devon       |
| 12        | Blake       |

Output

[
  {"seller_name":"Aria"},
  {"seller_name":"Blake"},
  {"seller_name":"Devon"}
]

Explanation

There are no 2020 orders at all, so every seller is returned and sorted alphabetically.

Example 3

Input

customer:
| customer_id | customer_name |
|-------------|---------------|
| 201         | Nina          |
| 202         | Omar          |

orders:
| order_id | sale_date  | order_cost | customer_id | seller_id |
|----------|------------|------------|-------------|-----------|
| 501      | 2020-03-01 | 900        | 201         | 21        |
| 502      | 2020-06-04 | 300        | 202         | null      |
| 503      | 2020-07-09 | 450        | 201         | 9999      |
| 504      | 2019-08-10 | 700        | 202         | 22        |
| 505      | 2021-01-02 | 880        | 201         | 23        |

seller:
| seller_id | seller_name |
|-----------|-------------|
| 21        | Owen        |
| 22        | Zed         |
| 23        | Zed         |

Output

[
  {"seller_name":"Zed"},
  {"seller_name":"Zed"}
]

Explanation

Seller 21 is excluded due to a 2020 sale. The null and unknown seller_id rows do not map to known sellers. Sellers 22 and 23 both remain and share the same display name.

Constraints

1 <= number of rows in customer <= 300000
1 <= number of rows in seller <= 300000
0 <= number of rows in orders <= 500000
customer.customer_id is unique
seller.seller_id is unique
orders.order_id is unique
sale_date values are valid calendar dates
0 <= order_cost <= 1000000000
seller_name values are not guaranteed to be unique
In noisy feeds, orders.seller_id may be null and should not map to a seller
Return exactly one column: seller_name
Sort output by seller_name ascending
Use SQLite 3.27.2 behavior for SQL submissions
For Pandas submissions, return a DataFrame with column seller_name