Let us review the Final Solution for our problem statement daily_product_revenue.
-
Prepare tables
- Create tables
- Load the data into tables
-
We need to project the fields which we are interested in. We need to have product_id as well as product_name as there can be products with the same name, which can result in incorrect output.
- order_date
- order_item_product_id
- product_name
- product_revenue
-
As we have fields from multiple tables, we need to perform a join. After that, we have to filter for COMPLETE or CLOSED orders.
-
We have to group the data by order_date and order_item_product_id, then we have to perform aggregation on order_item_subtotal to get product_revenue.
SELECT o.order_date,
oi.order_item_product_id,
p.product_name,
round(sum(oi.order_item_subtotal::numeric), 2) AS product_revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_item_order_id
JOIN products p
ON p.product_id = oi.order_item_product_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date,
oi.order_item_product_id,
p.product_name
LIMIT 10
SELECT o.order_date,
oi.order_item_product_id,
p.product_name,
round(sum(oi.order_item_subtotal::numeric), 2) AS product_revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_item_order_id
JOIN products p
ON p.product_id = oi.order_item_product_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date,
oi.order_item_product_id,
p.product_name
ORDER BY o.order_date,
product_revenue DESC
LIMIT 10
SELECT count(1) FROM (
SELECT o.order_date,
oi.order_item_product_id,
p.product_name,
round(sum(oi.order_item_subtotal::numeric), 2) AS product_revenue
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_item_order_id
JOIN products p
ON p.product_id = oi.order_item_product_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
GROUP BY o.order_date,
oi.order_item_product_id,
p.product_name
) q
Hope this detailed solution helps to understand and execute the necessary actions to calculate the daily product revenue efficiently.