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

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 |

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 |

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 |