00:00:00

Description

Editorial

February 2020 Bulk Product Order Report

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

[{"product_id":1,"order_date":"2020-02-05","unit":60},{"product_id":1,"order_date":"2020-02-17","unit":70},{"product_id":2,"order_date":"2020-02-10","unit":80},{"product_id":3,"order_date":"2020-02-24","unit":5},{"product_id":4,"order_date":"2020-02-25","unit":50},{"product_id":4,"order_date":"2020-02-27","unit":50},{"product_id":4,"order_date":"2020-03-01","unit":50}]

products =

[{"product_id":1,"product_name":"Data Structures Guide","product_category":"Books"},{"product_id":2,"product_name":"String Theory Notes","product_category":"Books"},{"product_id":3,"product_name":"Falcon Laptop","product_category":"Electronics"},{"product_id":4,"product_name":"Nimbus Tee","product_category":"Apparel"}]

Loading problem...

101

00:00:00

Description

Editorial

February 2020 Bulk Product Order Report

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders =

products =

February 2020 Bulk Product Order Report

Hints

February 2020 Bulk Product Order Report

Hints