{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Performing Aggregations\n", "\n", "Let us understand how to aggregate the data." ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* We can perform global aggregations as well as aggregations by key.\n", "* Global Aggregations\n", " * Get total number of orders.\n", " * Get revenue for a given order id.\n", " * Get number of records with order_status either COMPLETED or CLOSED.\n", "* Aggregations by key - using `GROUP BY`\n", " * Get number of orders by date or status.\n", " * Get revenue for each order_id.\n", " * Get daily product revenue (using order date and product id as keys).\n", "* We can also use `HAVING` clause to apply filtering on top of aggregated data.\n", " * Get daily product revenue where revenue is greater than $500 (using order date and product id as keys).\n", "* Rules while using `GROUP BY`.\n", " * We can have the columns which are specified as part of `GROUP BY` in `SELECT` clause.\n", " * On top of those, we can have derived columns using aggregate functions.\n", " * We cannot have any other columns that are not used as part of `GROUP BY` or derived column using non aggregate functions.\n", " * We will not be able to use aggregate functions or aliases used in the select clause as part of the where clause.\n", " * If we want to filter based on aggregated results, then we can leverage `HAVING` on top of `GROUP BY` (specifying `WHERE` is not an option)\n", "* Typical query execution - FROM -> WHERE -> GROUP BY -> SELECT" ] }, { "cell_type": "code", "execution_count": 73, "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": 74, "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": 75, "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
68883
" ], "text/plain": [ "[(68883,)]" ] }, "execution_count": 75, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(order_id) FROM orders" ] }, { "cell_type": "code", "execution_count": 76, "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
364
" ], "text/plain": [ "[(364,)]" ] }, "execution_count": 76, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%sql SELECT count(DISTINCT order_date) FROM orders" ] }, { "cell_type": "code", "execution_count": 77, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "3 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", "
order_item_idorder_item_order_idorder_item_product_idorder_item_quantityorder_item_subtotalorder_item_product_price
2210731199.99199.99
325025250.050.0
424031129.99129.99
" ], "text/plain": [ "[(2, 2, 1073, 1, 199.99, 199.99),\n", " (3, 2, 502, 5, 250.0, 50.0),\n", " (4, 2, 403, 1, 129.99, 129.99)]" ] }, "execution_count": 77, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT *\n", "FROM order_items \n", "WHERE order_item_order_id = 2" ] }, { "cell_type": "code", "execution_count": 78, "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", "
order_revenue
579.98
" ], "text/plain": [ "[(Decimal('579.98'),)]" ] }, "execution_count": 78, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT round(sum(order_item_subtotal::numeric), 2) AS order_revenue\n", "FROM order_items \n", "WHERE order_item_order_id = 2" ] }, { "cell_type": "code", "execution_count": 79, "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
30455
" ], "text/plain": [ "[(30455,)]" ] }, "execution_count": 79, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) \n", "FROM orders\n", "WHERE order_status IN ('COMPLETE', 'CLOSED')" ] }, { "cell_type": "code", "execution_count": 80, "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", "
order_datecount
2013-07-25 00:00:00143
2013-07-26 00:00:00269
2013-07-27 00:00:00202
2013-07-28 00:00:00187
2013-07-29 00:00:00253
2013-07-30 00:00:00227
2013-07-31 00:00:00252
2013-08-01 00:00:00246
2013-08-02 00:00:00224
2013-08-03 00:00:00183
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 143),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 269),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 202),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 187),\n", " (datetime.datetime(2013, 7, 29, 0, 0), 253),\n", " (datetime.datetime(2013, 7, 30, 0, 0), 227),\n", " (datetime.datetime(2013, 7, 31, 0, 0), 252),\n", " (datetime.datetime(2013, 8, 1, 0, 0), 246),\n", " (datetime.datetime(2013, 8, 2, 0, 0), 224),\n", " (datetime.datetime(2013, 8, 3, 0, 0), 183)]" ] }, "execution_count": 80, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT order_date,\n", " count(1)\n", "FROM orders\n", "GROUP BY order_date\n", "ORDER BY order_date\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 81, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "9 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", "
order_statusstatus_count
CANCELED1428
CLOSED7556
COMPLETE22899
ON_HOLD3798
PAYMENT_REVIEW729
PENDING7610
PENDING_PAYMENT15030
PROCESSING8275
SUSPECTED_FRAUD1558
" ], "text/plain": [ "[('CANCELED', 1428),\n", " ('CLOSED', 7556),\n", " ('COMPLETE', 22899),\n", " ('ON_HOLD', 3798),\n", " ('PAYMENT_REVIEW', 729),\n", " ('PENDING', 7610),\n", " ('PENDING_PAYMENT', 15030),\n", " ('PROCESSING', 8275),\n", " ('SUSPECTED_FRAUD', 1558)]" ] }, "execution_count": 81, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT order_status,\n", " count(1) AS status_count\n", "FROM orders\n", "GROUP BY order_status\n", "ORDER BY order_status\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 82, "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", "
order_item_order_idorder_revenue
44127179.97
26264334.96000000000004
37876699.97
55864600.94
31789129.99
56903479.97
406941129.75
48663969.9200000000001
472161219.89
379221029.9
" ], "text/plain": [ "[(44127, 179.97),\n", " (26264, 334.96000000000004),\n", " (37876, 699.97),\n", " (55864, 600.94),\n", " (31789, 129.99),\n", " (56903, 479.97),\n", " (40694, 1129.75),\n", " (48663, 969.9200000000001),\n", " (47216, 1219.89),\n", " (37922, 1029.9)]" ] }, "execution_count": 82, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT order_item_order_id,\n", " sum(order_item_subtotal) AS order_revenue\n", "FROM order_items\n", "GROUP BY order_item_order_id \n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{error}\n", "This query using `round` will fail as `sum(order_item_subtotal)` will not return the data accepted by `round`. We have to convert the data type of `sum(order_item_subtotal)` to `numeric`.\n", "```" ] }, { "cell_type": "code", "execution_count": 83, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.UndefinedFunction) function round(double precision, integer) does not exist\n", "LINE 1: SELECT order_item_order_id, round(sum(order_item_subtotal), ...\n", " ^\n", "HINT: No function matches the given name and argument types. You might need to add explicit type casts.\n", "\n", "[SQL: SELECT order_item_order_id, round(sum(order_item_subtotal), 2) AS order_revenue\n", "FROM order_items\n", "GROUP BY order_item_order_id \n", "LIMIT 10]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "SELECT order_item_order_id,\n", " round(sum(order_item_subtotal), 2) AS order_revenue\n", "FROM order_items\n", "GROUP BY order_item_order_id \n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 84, "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", "
order_item_order_idorder_revenue
44127179.97
26264334.96
37876699.97
55864600.94
31789129.99
56903479.97
406941129.75
48663969.92
472161219.89
379221029.90
" ], "text/plain": [ "[(44127, Decimal('179.97')),\n", " (26264, Decimal('334.96')),\n", " (37876, Decimal('699.97')),\n", " (55864, Decimal('600.94')),\n", " (31789, Decimal('129.99')),\n", " (56903, Decimal('479.97')),\n", " (40694, Decimal('1129.75')),\n", " (48663, Decimal('969.92')),\n", " (47216, Decimal('1219.89')),\n", " (37922, Decimal('1029.90'))]" ] }, "execution_count": 84, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT order_item_order_id,\n", " round(sum(order_item_subtotal)::numeric, 2) AS order_revenue\n", "FROM order_items\n", "GROUP BY order_item_order_id \n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 85, "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", "
order_dateorder_item_product_idrevenue
2013-07-25 00:00:0024319.96
2013-07-25 00:00:009374.97
2013-07-25 00:00:00134100.00
2013-07-25 00:00:001915099.49
2013-07-25 00:00:00226599.99
2013-07-25 00:00:003653359.44
2013-07-25 00:00:004031949.85
2013-07-25 00:00:005021650.00
2013-07-25 00:00:00572119.97
2013-07-25 00:00:00625199.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 24, Decimal('319.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99'))]" ] }, "execution_count": 85, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "We cannot use the aliases in select clause in `WHERE`. In this case **revenue** cannot be used in `WHERE` clause.\n", "```" ] }, { "cell_type": "code", "execution_count": 86, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.UndefinedColumn) column \"revenue\" does not exist\n", "LINE 5: AND revenue >= 500\n", " ^\n", "\n", "[SQL: SELECT o.order_date, oi.order_item_product_id, round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " AND revenue >= 500\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id\n", "LIMIT 10]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " AND revenue >= 500\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "We cannot use aggregate functions in `WHERE` clause.\n", "```" ] }, { "cell_type": "code", "execution_count": 87, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.GroupingError) aggregate functions are not allowed in WHERE\n", "LINE 5: AND round(sum(oi.order_item_subtotal::numeric), 2) >= 50...\n", " ^\n", "\n", "[SQL: SELECT o.order_date, oi.order_item_product_id, round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " AND round(sum(oi.order_item_subtotal::numeric), 2) >= 500\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id\n", "LIMIT 10]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " AND round(sum(oi.order_item_subtotal::numeric), 2) >= 500\n", "GROUP BY o.order_date,\n", " oi.order_item_product_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 88, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "25 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", " \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_idrevenue
2013-07-25 00:00:0010045599.72
2013-07-25 00:00:001915099.49
2013-07-25 00:00:009574499.70
2013-07-25 00:00:003653359.44
2013-07-25 00:00:0010732999.85
2013-07-25 00:00:0010142798.88
2013-07-25 00:00:004031949.85
2013-07-25 00:00:005021650.00
2013-07-25 00:00:006271079.73
2013-07-25 00:00:00226599.99
2013-07-26 00:00:00100410799.46
2013-07-26 00:00:003657978.67
2013-07-26 00:00:009576899.54
2013-07-26 00:00:001916799.32
2013-07-26 00:00:0010144798.08
2013-07-26 00:00:005024250.00
2013-07-26 00:00:0010733999.80
2013-07-26 00:00:004033249.75
2013-07-26 00:00:006273039.24
2013-07-27 00:00:0010049599.52
2013-07-27 00:00:001915999.40
2013-07-27 00:00:009575699.62
2013-07-27 00:00:0010735399.73
2013-07-27 00:00:003655099.15
2013-07-27 00:00:005025050.00
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1014, Decimal('4798.08')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 502, Decimal('4250.00')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1073, Decimal('3999.80')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 403, Decimal('3249.75')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 627, Decimal('3039.24')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1004, Decimal('9599.52')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 191, Decimal('5999.40')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 957, Decimal('5699.62')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1073, Decimal('5399.73')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 365, Decimal('5099.15')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 502, Decimal('5050.00'))]" ] }, "execution_count": 88, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT o.order_date,\n", " oi.order_item_product_id,\n", " round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", "FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", "WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", "GROUP BY o.order_date, \n", " oi.order_item_product_id\n", "HAVING round(sum(oi.order_item_subtotal::numeric), 2) >= 500\n", "ORDER BY o.order_date, revenue DESC\n", "LIMIT 25" ] }, { "cell_type": "code", "execution_count": 89, "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": 89, "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", " round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", " FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " GROUP BY o.order_date, \n", " oi.order_item_product_id\n", ") q" ] }, { "cell_type": "code", "execution_count": 90, "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
3339
" ], "text/plain": [ "[(3339,)]" ] }, "execution_count": 90, "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", " round(sum(oi.order_item_subtotal::numeric), 2) AS revenue\n", " FROM orders o JOIN order_items oi\n", " ON o.order_id = oi.order_item_order_id\n", " WHERE o.order_status IN ('COMPLETE', 'CLOSED')\n", " GROUP BY o.order_date, \n", " oi.order_item_product_id\n", " HAVING round(sum(oi.order_item_subtotal::numeric), 2) >= 500\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 }