Solution – Daily Product Revenue

Private: Mastering SQL using Postgresql Writing Basic SQL Queries 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.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%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

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%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:***@localhost:5432/itversity_retail_db
10 rows affected.

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

%%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:***@localhost:5432/itversity_retail_db
1 rows affected.

count
9120

Previous Topic

Back to Lesson

Next Topic