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
Sorting Data Exercises - Basic SQL Queries