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.
We have following fields in order_items.
We have following fields in products
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.
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.