Defining Problem Statements

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.