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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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