Define Problem Statement – Daily Product Revenue

Let us try to get daily product revenue using retail tables.

• daily is derived from orders.order_date.

• product has to be derived from products.product_name.

• revenue has to be derived from order_items.order_item_subtotal.

• We need to join all the 3 tables, then group by order_date, product_id as well as product_name to get revenue using order_item_subtotal.

• Get Daily Product Revenue using products, orders and order_items data set.

• We have following fields in orders.

  • order_id

  • order_date

  • order_customer_id

  • order_status

• We have following fields in order_items.

  • order_item_id

  • order_item_order_id

  • order_item_product_id

  • order_item_quantity

  • order_item_subtotal

  • order_item_product_price

• We have following fields in products

  • product_id

  • product_category_id

  • product_name

  • product_description

  • product_price

  • product_image

• We have one to many relationship between orders and order_items.

• orders.order_id is primary key and order_items.order_item_order_id is foreign key to orders.order_id.

• We have one to many relationship between products and order_items.

• products.product_id is primary key and order_items.order_item_product_id is foreign key to products.product_id

• By the end of this module we will explore all standard transformations and get daily product revenue using following fields.

  • orders.order_date

  • order_items.order_item_product_id

  • products.product_name

  • order_items.order_item_subtotal (aggregated using date and product_id).

• We will consider only COMPLETE or CLOSED orders.

• As there can be more than one product names with different ids, we have to include product_id as part of the key using which we will group the data.