Solution – Daily Product Revenue¶
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 same name and 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 join after which 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.
In [1]:
%load_ext sql
In [2]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [3]:
%%sql
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
10 rows affected.
Out[3]:
| order_date | order_item_product_id | product_name | product_revenue |
|---|---|---|---|
| 2013-07-25 00:00:00 | 24 | Elevation Training Mask 2.0 | 319.96 |
| 2013-07-25 00:00:00 | 93 | Under Armour Men’s Tech II T-Shirt | 74.97 |
| 2013-07-25 00:00:00 | 134 | Nike Women’s Legend V-Neck T-Shirt | 100.00 |
| 2013-07-25 00:00:00 | 191 | Nike Men’s Free 5.0+ Running Shoe | 5099.49 |
| 2013-07-25 00:00:00 | 226 | Bowflex SelectTech 1090 Dumbbells | 599.99 |
| 2013-07-25 00:00:00 | 365 | Perfect Fitness Perfect Rip Deck | 3359.44 |
| 2013-07-25 00:00:00 | 403 | Nike Men’s CJ Elite 2 TD Football Cleat | 1949.85 |
| 2013-07-25 00:00:00 | 502 | Nike Men’s Dri-FIT Victory Golf Polo | 1650.00 |
| 2013-07-25 00:00:00 | 572 | TYR Boys’ Team Digi Jammer | 119.97 |
| 2013-07-25 00:00:00 | 625 | Nike Men’s Kobe IX Elite Low Basketball Shoe | 199.99 |
In [4]:
%%sql
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
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[4]:
| order_date | order_item_product_id | product_name | product_revenue |
|---|---|---|---|
| 2013-07-25 00:00:00 | 1004 | Field & Stream Sportsman 16 Gun Fire Safe | 5599.72 |
| 2013-07-25 00:00:00 | 191 | Nike Men’s Free 5.0+ Running Shoe | 5099.49 |
| 2013-07-25 00:00:00 | 957 | Diamondback Women’s Serene Classic Comfort Bi | 4499.70 |
| 2013-07-25 00:00:00 | 365 | Perfect Fitness Perfect Rip Deck | 3359.44 |
| 2013-07-25 00:00:00 | 1073 | Pelican Sunstream 100 Kayak | 2999.85 |
| 2013-07-25 00:00:00 | 1014 | O’Brien Men’s Neoprene Life Vest | 2798.88 |
| 2013-07-25 00:00:00 | 403 | Nike Men’s CJ Elite 2 TD Football Cleat | 1949.85 |
| 2013-07-25 00:00:00 | 502 | Nike Men’s Dri-FIT Victory Golf Polo | 1650.00 |
| 2013-07-25 00:00:00 | 627 | Under Armour Girls’ Toddler Spine Surge Runni | 1079.73 |
| 2013-07-25 00:00:00 | 226 | Bowflex SelectTech 1090 Dumbbells | 599.99 |
In [5]:
%%sql
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
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[5]:
| count |
|---|
| 9120 |