00:00:00

Description

Editorial

Inactive Sellers in 2020

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":4,"sale_date":"2021-01-07","order_cost":900,"customer_id":101,"seller_id":3},{"order_id":2,"sale_date":"2019-07-19","order_cost":700,"customer_id":102,"seller_id":2},{"order_id":3,"sale_date":"2020-11-05","order_cost":1600,"customer_id":102,"seller_id":2},{"order_id":1,"sale_date":"2020-02-14","order_cost":1200,"customer_id":101,"seller_id":1}]

seller =

[{"seller_id":1,"seller_name":"Iris"},{"seller_id":2,"seller_name":"Mason"},{"seller_id":3,"seller_name":"Noor"}]

customer =

[{"customer_id":101,"customer_name":"Ava"},{"customer_id":102,"customer_name":"Ben"}]

Loading problem...

101

00:00:00

Description

Editorial

Inactive Sellers in 2020

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

seller =

[{"seller_id":1,"seller_name":"Iris"},{"seller_id":2,"seller_name":"Mason"},{"seller_id":3,"seller_name":"Noor"}]

customer =

[{"customer_id":101,"customer_name":"Ava"},{"customer_id":102,"customer_name":"Ben"}]

Inactive Sellers in 2020

Hints

Inactive Sellers in 2020

Hints