{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Solution – Daily Product Revenue\n", "\n", "Let us review the Final Solution for our problem statement **daily_product_revenue**." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* Prepare tables\n", " * Create tables\n", " * Load the data into tables\n", "* 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.\n", " * order_date\n", " * order_item_product_id\n", " * product_name\n", " * product_revenue\n", "* As we have fields from multiple tables, we need to perform join after which we have to filter for COMPLETE or CLOSED orders.\n", "* 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." ] }, { "cell_type": "code", "execution_count": 107, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 108, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 109, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idproduct_nameproduct_revenue
2013-07-25 00:00:0024Elevation Training Mask 2.0319.96
2013-07-25 00:00:0093Under Armour Men's Tech II T-Shirt74.97
2013-07-25 00:00:00134Nike Women's Legend V-Neck T-Shirt100.00
2013-07-25 00:00:00191Nike Men's Free 5.0+ Running Shoe5099.49
2013-07-25 00:00:00226Bowflex SelectTech 1090 Dumbbells599.99
2013-07-25 00:00:00365Perfect Fitness Perfect Rip Deck3359.44
2013-07-25 00:00:00403Nike Men's CJ Elite 2 TD Football Cleat1949.85
2013-07-25 00:00:00502Nike Men's Dri-FIT Victory Golf Polo1650.00
2013-07-25 00:00:00572TYR Boys' Team Digi Jammer119.97
2013-07-25 00:00:00625Nike Men's Kobe IX Elite Low Basketball Shoe199.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 24, 'Elevation Training Mask 2.0', Decimal('319.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, \"Under Armour Men's Tech II T-Shirt\", Decimal('74.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, \"Nike Women's Legend V-Neck T-Shirt\", Decimal('100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, \"Nike Men's Free 5.0+ Running Shoe\", Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, 'Bowflex SelectTech 1090 Dumbbells', Decimal('599.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, 'Perfect Fitness Perfect Rip Deck', Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, \"Nike Men's CJ Elite 2 TD Football Cleat\", Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, \"Nike Men's Dri-FIT Victory Golf Polo\", Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, \"TYR Boys' Team Digi Jammer\", Decimal('119.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, \"Nike Men's Kobe IX Elite Low Basketball Shoe\", Decimal('199.99'))]" ] }, "execution_count": 109, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " p.product_name,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS product_revenue\n", "FROM orders o \n", " JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " JOIN products p\n", " ON p.product_id = oi.order_item_product_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id,\n", " p.product_name\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 110, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idproduct_nameproduct_revenue
2013-07-25 00:00:001004Field & Stream Sportsman 16 Gun Fire Safe5599.72
2013-07-25 00:00:00191Nike Men's Free 5.0+ Running Shoe5099.49
2013-07-25 00:00:00957Diamondback Women's Serene Classic Comfort Bi4499.70
2013-07-25 00:00:00365Perfect Fitness Perfect Rip Deck3359.44
2013-07-25 00:00:001073Pelican Sunstream 100 Kayak2999.85
2013-07-25 00:00:001014O'Brien Men's Neoprene Life Vest2798.88
2013-07-25 00:00:00403Nike Men's CJ Elite 2 TD Football Cleat1949.85
2013-07-25 00:00:00502Nike Men's Dri-FIT Victory Golf Polo1650.00
2013-07-25 00:00:00627Under Armour Girls' Toddler Spine Surge Runni1079.73
2013-07-25 00:00:00226Bowflex SelectTech 1090 Dumbbells599.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, 'Field & Stream Sportsman 16 Gun Fire Safe', Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, \"Nike Men's Free 5.0+ Running Shoe\", Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, \"Diamondback Women's Serene Classic Comfort Bi\", Decimal('4499.70')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, 'Perfect Fitness Perfect Rip Deck', Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, 'Pelican Sunstream 100 Kayak', Decimal('2999.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, \"O'Brien Men's Neoprene Life Vest\", Decimal('2798.88')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, \"Nike Men's CJ Elite 2 TD Football Cleat\", Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, \"Nike Men's Dri-FIT Victory Golf Polo\", Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, \"Under Armour Girls' Toddler Spine Surge Runni\", Decimal('1079.73')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, 'Bowflex SelectTech 1090 Dumbbells', Decimal('599.99'))]" ] }, "execution_count": 110, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " p.product_name,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS product_revenue\n", "FROM orders o \n", " JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " JOIN products p\n", " ON p.product_id = oi.order_item_product_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id,\n", " p.product_name\n", "ORDER BY o.order_date,\n", " product_revenue DESC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 111, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
count
9120
" ], "text/plain": [ "[(9120,)]" ] }, "execution_count": 111, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM (\n", " SELECT o.order_date,\n", " oi.order_item_product_id,\n", " p.product_name,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS product_revenue\n", " FROM orders o \n", " JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " JOIN products p\n", " ON p.product_id = oi.order_item_product_id\n", " WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " GROUP BY o.order_date,\n", " oi.order_item_product_id,\n", " p.product_name\n", ") q" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }