February 2020 Bulk Product Order Report

Easy

An analytics warehouse stores catalog data and transactional order events. Table: products product_id: unique product identifier product_name: display name of the product product_category: category label of the product Table: orders product_id: product identifier linked to products order_date: calendar date of the order event unit: number of units purchased in that row Task: Build a report of products whose total ordered units in February 2020 reached at least 100. Business rules: Count only rows where order_date is within February 2020. Aggregate unit values by product. Keep only products with aggregated February units >= 100. Output requirements: Return columns in this exact order: product_name unit Output rows can be in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

products:
| product_id | product_name          | product_category |
|------------|-----------------------|------------------|
| 1          | Data Structures Guide | Books            |
| 2          | String Theory Notes   | Books            |
| 3          | Falcon Laptop         | Electronics      |
| 4          | Nimbus Tee            | Apparel          |

orders:
| product_id | order_date  | unit |
|------------|-------------|------|
| 1          | 2020-02-05  | 60   |
| 1          | 2020-02-17  | 70   |
| 2          | 2020-02-10  | 80   |
| 3          | 2020-02-24  | 5    |
| 4          | 2020-02-25  | 50   |
| 4          | 2020-02-27  | 50   |
| 4          | 2020-03-01  | 50   |

Output

[
  {"product_name":"Data Structures Guide","unit":130},
  {"product_name":"Nimbus Tee","unit":100}
]

Explanation

Product 1 has 130 February units and product 4 has 100 February units. Product 2 has only 80 and product 3 has only 5.

Example 2

Input

products:
| product_id | product_name      | product_category |
|------------|-------------------|------------------|
| 10         | Solar Charger     | Accessories      |
| 11         | Noise Earbuds     | Electronics      |

orders:
| product_id | order_date  | unit |
|------------|-------------|------|
| 10         | 2020-01-31  | 90   |
| 10         | 2020-02-01  | 10   |
| 11         | 2020-02-29  | 100  |

Output

[
  {"product_name":"Noise Earbuds","unit":100}
]

Explanation

January rows are excluded. Product 10 has only 10 in February, while product 11 reaches the threshold exactly with 100.

Example 3

Input

products:
| product_id | product_name     | product_category |
|------------|------------------|------------------|
| 21         | Mesh Router      | Networking       |
| 22         | Smart Thermostat | Home             |

orders:
| product_id | order_date  | unit |
|------------|-------------|------|
| 21         | 2020-03-02  | 100  |
| 22         | 2020-02-10  | 40   |
| 22         | 2020-02-20  | 50   |

Output

[]

Explanation

No product reaches 100 units in February 2020.

Constraints

1 <= number of rows in products <= 500000
0 <= number of rows in orders <= 500000
product_id is unique in products
orders rows may contain duplicates and each row must be counted
1 <= unit <= 1000000
order_date is a valid date in YYYY-MM-DD format
orders.product_id references products.product_id
Return exactly two columns: product_name, unit
Filter month window to February 2020 only
Keep only aggregated February unit totals >= 100
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: product_name, unit

products: | product_id | product_name | product_category | |------------|-----------------------|------------------| | 1 | Data Structures Guide | Books | | 2 | String Theory Notes | Books | | 3 | Falcon Laptop | Electronics | | 4 | Nimbus Tee | Apparel | orders: | product_id | order_date | unit | |------------|-------------|------| | 1 | 2020-02-05 | 60 | | 1 | 2020-02-17 | 70 | | 2 | 2020-02-10 | 80 | | 3 | 2020-02-24 | 5 | | 4 | 2020-02-25 | 50 | | 4 | 2020-02-27 | 50 | | 4 | 2020-03-01 | 50 |

products: | product_id | product_name | product_category | |------------|-------------------|------------------| | 10 | Solar Charger | Accessories | | 11 | Noise Earbuds | Electronics | orders: | product_id | order_date | unit | |------------|-------------|------| | 10 | 2020-01-31 | 90 | | 10 | 2020-02-01 | 10 | | 11 | 2020-02-29 | 100 |

products: | product_id | product_name | product_category | |------------|------------------|------------------| | 21 | Mesh Router | Networking | | 22 | Smart Thermostat | Home | orders: | product_id | order_date | unit | |------------|-------------|------| | 21 | 2020-03-02 | 100 | | 22 | 2020-02-10 | 40 | | 22 | 2020-02-20 | 50 |