00:00:00

Description

Editorial

Dual-Month Customer Spend Qualification

EASY15 pts

A commerce analytics team is preparing a premium-customer campaign for mid-2020.

The warehouse contains:

customers(customer_id, name, country)
product(product_id, description, price)
orders(order_id, customer_id, product_id, order_date, quantity)

Business rule: A customer qualifies only if they spent at least 100 in June 2020 and also at least 100 in July 2020.

Calculation details:

Spend contribution of each order row is quantity * product.price.
Orders from other months or years must not affect qualification.
A customer can have multiple orders in a month; monthly spend is the sum of all those orders.

Task: Return every qualifying customer.

Output requirements:

Return exactly two columns: customer_id, customer_name.
Result order is not required.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

customers:
| customer_id | name    | country |
|-------------|---------|---------|
| 1           | Aria    | USA     |
| 2           | Mateo   | Peru    |
| 3           | Sana    | Egypt   |

product:
| product_id | description     | price |
|------------|-----------------|-------|
| 10         | Wireless Mouse  | 80    |
| 20         | Notebook        | 20    |
| 30         | Desk Lamp       | 50    |

orders:
| order_id | customer_id | product_id | order_date  | quantity |
|----------|-------------|------------|-------------|----------|
| 101      | 1           | 10         | 2020-06-09  | 1        |
| 102      | 1           | 20         | 2020-06-15  | 1        |
| 103      | 1           | 30         | 2020-07-08  | 2        |
| 104      | 2           | 10         | 2020-06-12  | 2        |
| 105      | 2           | 20         | 2020-07-04  | 2        |
| 106      | 3           | 20         | 2020-06-21  | 5        |
| 107      | 3           | 30         | 2020-05-30  | 3        |

Output

[
  {"customer_id":1,"customer_name":"Aria"}
]

Explanation

Aria spends 100 in June and 100 in July, so she qualifies. Mateo misses the July threshold, and Sana has no qualifying July spend.

Example

Input

customers:
| customer_id | name   | country |
|-------------|--------|---------|
| 10          | Nia    | USA     |
| 11          | Omar   | UK      |

product:
| product_id | description  | price |
|------------|--------------|-------|
| 1          | Cable        | 25    |
| 2          | Adapter      | 40    |

orders:
| order_id | customer_id | product_id | order_date  | quantity |
|----------|-------------|------------|-------------|----------|
| 1        | 10          | 1          | 2020-06-01  | 4        |
| 2        | 10          | 2          | 2020-07-31  | 1        |
| 3        | 10          | 1          | 2020-07-10  | 3        |
| 4        | 11          | 2          | 2020-06-05  | 2        |
| 5        | 11          | 1          | 2020-07-05  | 3        |

Output

[
  {"customer_id":10,"customer_name":"Nia"}
]

Explanation

Nia reaches exactly 100 in June (425) and 115 in July (40 + 325). Omar reaches 80 in July and does not qualify.

Example

Input

customers:
| customer_id | name   | country |
|-------------|--------|---------|
| 21          | Priya  | IN      |
| 22          | Lucas  | BR      |

product:
| product_id | description | price |
|------------|-------------|-------|
| 7          | Item-A      | 60    |
| 8          | Item-B      | 45    |

orders:
| order_id | customer_id | product_id | order_date  | quantity |
|----------|-------------|------------|-------------|----------|
| 10       | 21          | 7          | 2019-06-10  | 2        |
| 11       | 21          | 8          | 2019-07-10  | 3        |
| 12       | 21          | 8          | 2020-06-10  | 2        |
| 13       | 21          | 7          | 2020-07-10  | 1        |
| 14       | 22          | 7          | 2020-06-12  | 2        |
| 15       | 22          | 8          | 2020-07-21  | 3        |

Output

[
  {"customer_id":22,"customer_name":"Lucas"}
]

Explanation

Orders from 2019 do not count. Priya has only 90 in June 2020 and fails. Lucas clears both month thresholds.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in customers <= 100000
1 <= number of rows in product <= 100000
0 <= number of rows in orders <= 300000
customers.customer_id is unique
product.product_id is unique
orders.order_id is unique
Each orders.customer_id references an existing customer
Each orders.product_id references an existing product
1 <= product.price <= 100000
1 <= orders.quantity <= 1000
order_date is a valid date in YYYY-MM-DD format
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: customer_id, customer_name

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"order_id":101,"customer_id":1,"product_id":10,"order_date":"2020-06-09","quantity":1},{"order_id":102,"customer_id":1,"product_id":20,"order_date":"2020-06-15","quantity":1},{"order_id":103,"customer_id":1,"product_id":30,"order_date":"2020-07-08","quantity":2},{"order_id":104,"customer_id":2,"product_id":10,"order_date":"2020-06-12","quantity":2},{"order_id":105,"customer_id":2,"product_id":20,"order_date":"2020-07-04","quantity":2},{"order_id":106,"customer_id":3,"product_id":20,"order_date":"2020-06-21","quantity":5},{"order_id":107,"customer_id":3,"product_id":30,"order_date":"2020-05-30","quantity":3}]

product =

[{"product_id":10,"description":"Wireless Mouse","price":80},{"product_id":20,"description":"Notebook","price":20},{"product_id":30,"description":"Desk Lamp","price":50}]

customers =

[{"customer_id":1,"name":"Aria","country":"US"},{"customer_id":2,"name":"Mateo","country":"PE"},{"customer_id":3,"name":"Sana","country":"EG"}]

Loading problem...

101

00:00:00

Description

Editorial

Dual-Month Customer Spend Qualification

EASY15 pts

A commerce analytics team is preparing a premium-customer campaign for mid-2020.

The warehouse contains:

customers(customer_id, name, country)
product(product_id, description, price)
orders(order_id, customer_id, product_id, order_date, quantity)

Business rule: A customer qualifies only if they spent at least 100 in June 2020 and also at least 100 in July 2020.

Calculation details:

Spend contribution of each order row is quantity * product.price.
Orders from other months or years must not affect qualification.
A customer can have multiple orders in a month; monthly spend is the sum of all those orders.

Task: Return every qualifying customer.

Output requirements:

Return exactly two columns: customer_id, customer_name.
Result order is not required.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

customers:
| customer_id | name    | country |
|-------------|---------|---------|
| 1           | Aria    | USA     |
| 2           | Mateo   | Peru    |
| 3           | Sana    | Egypt   |

product:
| product_id | description     | price |
|------------|-----------------|-------|
| 10         | Wireless Mouse  | 80    |
| 20         | Notebook        | 20    |
| 30         | Desk Lamp       | 50    |

orders:
| order_id | customer_id | product_id | order_date  | quantity |
|----------|-------------|------------|-------------|----------|
| 101      | 1           | 10         | 2020-06-09  | 1        |
| 102      | 1           | 20         | 2020-06-15  | 1        |
| 103      | 1           | 30         | 2020-07-08  | 2        |
| 104      | 2           | 10         | 2020-06-12  | 2        |
| 105      | 2           | 20         | 2020-07-04  | 2        |
| 106      | 3           | 20         | 2020-06-21  | 5        |
| 107      | 3           | 30         | 2020-05-30  | 3        |

Output

[
  {"customer_id":1,"customer_name":"Aria"}
]

Explanation

Aria spends 100 in June and 100 in July, so she qualifies. Mateo misses the July threshold, and Sana has no qualifying July spend.

Example

Input

customers:
| customer_id | name   | country |
|-------------|--------|---------|
| 10          | Nia    | USA     |
| 11          | Omar   | UK      |

product:
| product_id | description  | price |
|------------|--------------|-------|
| 1          | Cable        | 25    |
| 2          | Adapter      | 40    |

orders:
| order_id | customer_id | product_id | order_date  | quantity |
|----------|-------------|------------|-------------|----------|
| 1        | 10          | 1          | 2020-06-01  | 4        |
| 2        | 10          | 2          | 2020-07-31  | 1        |
| 3        | 10          | 1          | 2020-07-10  | 3        |
| 4        | 11          | 2          | 2020-06-05  | 2        |
| 5        | 11          | 1          | 2020-07-05  | 3        |

Output

[
  {"customer_id":10,"customer_name":"Nia"}
]

Explanation

Nia reaches exactly 100 in June (425) and 115 in July (40 + 325). Omar reaches 80 in July and does not qualify.

Example

Input

customers:
| customer_id | name   | country |
|-------------|--------|---------|
| 21          | Priya  | IN      |
| 22          | Lucas  | BR      |

product:
| product_id | description | price |
|------------|-------------|-------|
| 7          | Item-A      | 60    |
| 8          | Item-B      | 45    |

orders:
| order_id | customer_id | product_id | order_date  | quantity |
|----------|-------------|------------|-------------|----------|
| 10       | 21          | 7          | 2019-06-10  | 2        |
| 11       | 21          | 8          | 2019-07-10  | 3        |
| 12       | 21          | 8          | 2020-06-10  | 2        |
| 13       | 21          | 7          | 2020-07-10  | 1        |
| 14       | 22          | 7          | 2020-06-12  | 2        |
| 15       | 22          | 8          | 2020-07-21  | 3        |

Output

[
  {"customer_id":22,"customer_name":"Lucas"}
]

Explanation

Orders from 2019 do not count. Priya has only 90 in June 2020 and fails. Lucas clears both month thresholds.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in customers <= 100000
1 <= number of rows in product <= 100000
0 <= number of rows in orders <= 300000
customers.customer_id is unique
product.product_id is unique
orders.order_id is unique
Each orders.customer_id references an existing customer
Each orders.product_id references an existing product
1 <= product.price <= 100000
1 <= orders.quantity <= 1000
order_date is a valid date in YYYY-MM-DD format
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: customer_id, customer_name

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

product =

[{"product_id":10,"description":"Wireless Mouse","price":80},{"product_id":20,"description":"Notebook","price":20},{"product_id":30,"description":"Desk Lamp","price":50}]

customers =

[{"customer_id":1,"name":"Aria","country":"US"},{"customer_id":2,"name":"Mateo","country":"PE"},{"customer_id":3,"name":"Sana","country":"EG"}]

Dual-Month Customer Spend Qualification

Hints

Dual-Month Customer Spend Qualification

Hints