In this article, we will learn how to calculate daily product revenue using retail tables. We will be combining data from the orders, order_items, and products tables to derive revenue based on the order_item_subtotal.
Watch the video for a visual explanation of the concepts.
Key Concepts Explanation
Joining Tables
To get daily product revenue, we need to join the orders, order_items, and products tables based on the relevant keys such as order_id, order_item_order_id, and order_item_product_id.
SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_item_order_id
JOIN products p ON oi.order_item_product_id = p.product_id;
Grouping Data
We will group the data by order_date, product_id, and product_name to calculate revenue using the order_item_subtotal.
SELECT order_date, product_id, product_name, SUM(order_item_subtotal) AS revenue
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_item_order_id
JOIN products p ON oi.order_item_product_id = p.product_id
GROUP BY order_date, product_id, product_name;
Hands-On Tasks
- Join the orders, order_items, and products tables using SQL.
- Group the data by order_date, product_id, and product_name to calculate the revenue for each product daily.
Conclusion
In this article, we explored how to calculate daily product revenue using SQL queries on retail tables. By joining and grouping the data, we were able to derive revenue for each product on a daily basis. Practice these concepts to master the calculation of product revenue in your data analysis tasks.