You can assume each line of the file order_products.csv
holds data on one request. The file contains data of the form
order_id,product_id,add_to_cart_order,reordered
2,33120,1,1
2,28985,2,1
2,9327,3,0
2,45918,4,1
3,17668,1,1
3,46667,2,1
3,17461,4,1
3,32665,3,1
4,46842,1,0
where
order_id
: unique identifier of orderproduct_id
: unique identifier of productadd_to_cart_order
: sequence order in which each product was added to shopping cartreordered
: flag indicating if the product has been ordered by this user at some point in the past. The field is1
if the user has ordered it in the past and0
if the user has not. While data engineers should validate their data, for the purposes of this challenge, you can take thereordered
flag at face value and assume it accurately reflects whether the product has been ordered by the user before.
The file products.csv
holds data on every product, and looks something like this:
product_id,product_name,aisle_id,department_id
9327,Garlic Powder,104,13
17461,Air Chilled Organic Boneless Skinless Chicken Breasts,35,12
17668,Unsweetened Chocolate Almond Breeze Almond Milk,91,16
28985,Michigan Organic Kale,83,4
32665,Organic Ezekiel 49 Bread Cinnamon Raisin,112,3
33120,Organic Egg Whites,86,16
45918,Coconut Butter,19,13
46667,Organic Ginger Root,83,4
46842,Plain Pre-Sliced Bagels,93,3
where
product_id
: unique identifier of the productproduct_name
: name of the productaisle_id
: identifier of aisle in which product is locateddepartment_id
: identifier of department
Given the two input files in the input directory, your program should create an output file, report.csv
, in the output directory that, for each department, surfaces the following statistics:
number_of_orders
. How many times was a product requested from this department? (If the same product was ordered multiple times, we count it as multiple requests)
number_of_first_orders
. How many of those requests contain products ordered for the first time?
percentage
. What is the percentage of requests containing products ordered for the first time compared with the total number of requests for products from that department? (e.g., number_of_first_orders
divided by number_of_orders
)
For example, with the input files given above, the correct output file is
department_id,number_of_orders,number_of_first_orders,percentage
3,2,1,0.50
4,2,0,0.00
12,1,0,0.00
13,2,1,0.50
16,2,0,0.00
The output file should adhere to the following rules
- It is listed in ascending order by
department_id
- A
department_id
should be listed only ifnumber_of_orders
is greater than0
percentage
should be rounded to the second decimal