Dual-Month Customer Spend Qualification

Easy

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 1

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 2

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 (4*25) and 115 in July (40 + 3*25). Omar reaches 80 in July and does not qualify.

Example 3

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.

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

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 |

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 |

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 |