{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Sorting Data\n", "\n", "Let us understand how to sort the data using **SQL**." ] }, { "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": [ "* We typically perform sorting as final step.\n", "* Sorting can be done either by using one field or multiple fields. Sorting by multiple fields is also known as composite sorting.\n", "* We can sort the data either in ascending order or descending order by using column or expression.\n", "* By default, the sorting order is ascending and we can change it to descending by using `DESC`.\n", "* As part of composite sorting, we can sort the data in ascending order on some fields and descending order on other fields.\n", "* Typical query execution order\n", " 1. `FROM`\n", " 2. `WHERE`\n", " 3. `GROUP BY` and `HAVING`\n", " 4. `SELECT`\n", " 5. `ORDER BY`\n", "\n", "```sql\n", "SELECT order_date, count(1) AS order_count\n", "FROM orders\n", "WHERE order_status IN ('COMPLETE', 'CLOSED')\n", "GROUP BY order_date\n", "HAVING count(1) > 50\n", "ORDER BY order_count DESC\n", "```" ] }, { "cell_type": "code", "execution_count": 91, "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": 92, "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": 93, "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_idorder_dateorder_customer_idorder_status
12013-07-25 00:00:0011599CLOSED
22013-07-25 00:00:00256PENDING_PAYMENT
32013-07-25 00:00:0012111COMPLETE
42013-07-25 00:00:008827CLOSED
52013-07-25 00:00:0011318COMPLETE
62013-07-25 00:00:007130COMPLETE
72013-07-25 00:00:004530COMPLETE
82013-07-25 00:00:002911PROCESSING
92013-07-25 00:00:005657PENDING_PAYMENT
102013-07-25 00:00:005648PENDING_PAYMENT
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 11599, 'CLOSED'),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT'),\n", " (3, datetime.datetime(2013, 7, 25, 0, 0), 12111, 'COMPLETE'),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED'),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE'),\n", " (6, datetime.datetime(2013, 7, 25, 0, 0), 7130, 'COMPLETE'),\n", " (7, datetime.datetime(2013, 7, 25, 0, 0), 4530, 'COMPLETE'),\n", " (8, datetime.datetime(2013, 7, 25, 0, 0), 2911, 'PROCESSING'),\n", " (9, datetime.datetime(2013, 7, 25, 0, 0), 5657, 'PENDING_PAYMENT'),\n", " (10, datetime.datetime(2013, 7, 25, 0, 0), 5648, 'PENDING_PAYMENT')]" ] }, "execution_count": 93, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders LIMIT 10" ] }, { "cell_type": "code", "execution_count": 94, "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_idorder_dateorder_customer_idorder_status
229452013-12-13 00:00:001COMPLETE
338652014-02-18 00:00:002COMPLETE
678632013-11-30 00:00:002COMPLETE
151922013-10-29 00:00:002PENDING_PAYMENT
579632013-08-02 00:00:002ON_HOLD
561782014-07-15 00:00:003PENDING
576172014-07-24 00:00:003COMPLETE
236622013-12-19 00:00:003COMPLETE
226462013-12-11 00:00:003COMPLETE
351582014-02-26 00:00:003COMPLETE
" ], "text/plain": [ "[(22945, datetime.datetime(2013, 12, 13, 0, 0), 1, 'COMPLETE'),\n", " (33865, datetime.datetime(2014, 2, 18, 0, 0), 2, 'COMPLETE'),\n", " (67863, datetime.datetime(2013, 11, 30, 0, 0), 2, 'COMPLETE'),\n", " (15192, datetime.datetime(2013, 10, 29, 0, 0), 2, 'PENDING_PAYMENT'),\n", " (57963, datetime.datetime(2013, 8, 2, 0, 0), 2, 'ON_HOLD'),\n", " (56178, datetime.datetime(2014, 7, 15, 0, 0), 3, 'PENDING'),\n", " (57617, datetime.datetime(2014, 7, 24, 0, 0), 3, 'COMPLETE'),\n", " (23662, datetime.datetime(2013, 12, 19, 0, 0), 3, 'COMPLETE'),\n", " (22646, datetime.datetime(2013, 12, 11, 0, 0), 3, 'COMPLETE'),\n", " (35158, datetime.datetime(2014, 2, 26, 0, 0), 3, 'COMPLETE')]" ] }, "execution_count": 94, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders\n", "ORDER BY order_customer_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 95, "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_idorder_dateorder_customer_idorder_status
229452013-12-13 00:00:001COMPLETE
338652014-02-18 00:00:002COMPLETE
678632013-11-30 00:00:002COMPLETE
151922013-10-29 00:00:002PENDING_PAYMENT
579632013-08-02 00:00:002ON_HOLD
561782014-07-15 00:00:003PENDING
576172014-07-24 00:00:003COMPLETE
236622013-12-19 00:00:003COMPLETE
226462013-12-11 00:00:003COMPLETE
351582014-02-26 00:00:003COMPLETE
" ], "text/plain": [ "[(22945, datetime.datetime(2013, 12, 13, 0, 0), 1, 'COMPLETE'),\n", " (33865, datetime.datetime(2014, 2, 18, 0, 0), 2, 'COMPLETE'),\n", " (67863, datetime.datetime(2013, 11, 30, 0, 0), 2, 'COMPLETE'),\n", " (15192, datetime.datetime(2013, 10, 29, 0, 0), 2, 'PENDING_PAYMENT'),\n", " (57963, datetime.datetime(2013, 8, 2, 0, 0), 2, 'ON_HOLD'),\n", " (56178, datetime.datetime(2014, 7, 15, 0, 0), 3, 'PENDING'),\n", " (57617, datetime.datetime(2014, 7, 24, 0, 0), 3, 'COMPLETE'),\n", " (23662, datetime.datetime(2013, 12, 19, 0, 0), 3, 'COMPLETE'),\n", " (22646, datetime.datetime(2013, 12, 11, 0, 0), 3, 'COMPLETE'),\n", " (35158, datetime.datetime(2014, 2, 26, 0, 0), 3, 'COMPLETE')]" ] }, "execution_count": 95, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders\n", "ORDER BY order_customer_id ASC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 96, "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_idorder_dateorder_customer_idorder_status
229452013-12-13 00:00:001COMPLETE
579632013-08-02 00:00:002ON_HOLD
151922013-10-29 00:00:002PENDING_PAYMENT
678632013-11-30 00:00:002COMPLETE
338652014-02-18 00:00:002COMPLETE
226462013-12-11 00:00:003COMPLETE
614532013-12-14 00:00:003COMPLETE
236622013-12-19 00:00:003COMPLETE
351582014-02-26 00:00:003COMPLETE
463992014-05-09 00:00:003PROCESSING
" ], "text/plain": [ "[(22945, datetime.datetime(2013, 12, 13, 0, 0), 1, 'COMPLETE'),\n", " (57963, datetime.datetime(2013, 8, 2, 0, 0), 2, 'ON_HOLD'),\n", " (15192, datetime.datetime(2013, 10, 29, 0, 0), 2, 'PENDING_PAYMENT'),\n", " (67863, datetime.datetime(2013, 11, 30, 0, 0), 2, 'COMPLETE'),\n", " (33865, datetime.datetime(2014, 2, 18, 0, 0), 2, 'COMPLETE'),\n", " (22646, datetime.datetime(2013, 12, 11, 0, 0), 3, 'COMPLETE'),\n", " (61453, datetime.datetime(2013, 12, 14, 0, 0), 3, 'COMPLETE'),\n", " (23662, datetime.datetime(2013, 12, 19, 0, 0), 3, 'COMPLETE'),\n", " (35158, datetime.datetime(2014, 2, 26, 0, 0), 3, 'COMPLETE'),\n", " (46399, datetime.datetime(2014, 5, 9, 0, 0), 3, 'PROCESSING')]" ] }, "execution_count": 96, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders\n", "ORDER BY order_customer_id,\n", " order_date\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 97, "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_idorder_dateorder_customer_idorder_status
229452013-12-13 00:00:001COMPLETE
338652014-02-18 00:00:002COMPLETE
678632013-11-30 00:00:002COMPLETE
151922013-10-29 00:00:002PENDING_PAYMENT
579632013-08-02 00:00:002ON_HOLD
576172014-07-24 00:00:003COMPLETE
561782014-07-15 00:00:003PENDING
463992014-05-09 00:00:003PROCESSING
351582014-02-26 00:00:003COMPLETE
236622013-12-19 00:00:003COMPLETE
" ], "text/plain": [ "[(22945, datetime.datetime(2013, 12, 13, 0, 0), 1, 'COMPLETE'),\n", " (33865, datetime.datetime(2014, 2, 18, 0, 0), 2, 'COMPLETE'),\n", " (67863, datetime.datetime(2013, 11, 30, 0, 0), 2, 'COMPLETE'),\n", " (15192, datetime.datetime(2013, 10, 29, 0, 0), 2, 'PENDING_PAYMENT'),\n", " (57963, datetime.datetime(2013, 8, 2, 0, 0), 2, 'ON_HOLD'),\n", " (57617, datetime.datetime(2014, 7, 24, 0, 0), 3, 'COMPLETE'),\n", " (56178, datetime.datetime(2014, 7, 15, 0, 0), 3, 'PENDING'),\n", " (46399, datetime.datetime(2014, 5, 9, 0, 0), 3, 'PROCESSING'),\n", " (35158, datetime.datetime(2014, 2, 26, 0, 0), 3, 'COMPLETE'),\n", " (23662, datetime.datetime(2013, 12, 19, 0, 0), 3, 'COMPLETE')]" ] }, "execution_count": 97, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM orders\n", "ORDER BY order_customer_id,\n", " order_date DESC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 98, "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-25 00:00:0024319.96
2013-07-25 00:00:00821207.96
2013-07-25 00:00:00625199.99
2013-07-25 00:00:00705119.99
2013-07-25 00:00:00572119.97
2013-07-25 00:00:00666109.99
2013-07-25 00:00:00725108.00
2013-07-25 00:00:00134100.00
2013-07-25 00:00:0090699.96
2013-07-25 00:00:0082895.97
2013-07-25 00:00:0081079.96
2013-07-25 00:00:0092679.95
2013-07-25 00:00:0092479.95
2013-07-25 00:00:009374.97
2013-07-25 00:00:0083563.98
" ], "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, 25, 0, 0), 24, Decimal('319.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 821, Decimal('207.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 705, Decimal('119.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 666, Decimal('109.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 725, Decimal('108.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 906, Decimal('99.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 828, Decimal('95.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 810, Decimal('79.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 926, Decimal('79.95')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 924, Decimal('79.95')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98'))]" ] }, "execution_count": 98, "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", "ORDER BY o.order_date,\n", " revenue DESC\n", "LIMIT 25" ] }, { "cell_type": "code", "execution_count": 99, "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: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-26 00:00:00100410799.46
" ], "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, 26, 0, 0), 1004, Decimal('10799.46'))]" ] }, "execution_count": 99, "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) >= 1000\n", "ORDER BY o.order_date,\n", " revenue DESC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 100, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 100, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "DROP TABLE IF EXISTS users" ] }, { "cell_type": "code", "execution_count": 101, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 101, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "CREATE TABLE users (\n", " user_id SERIAL PRIMARY KEY,\n", " user_first_name VARCHAR(30) NOT NULL,\n", " user_last_name VARCHAR(30) NOT NULL,\n", " user_email_id VARCHAR(50) NOT NULL,\n", " user_email_validated BOOLEAN DEFAULT FALSE,\n", " user_password VARCHAR(200),\n", " user_role VARCHAR(1) NOT NULL DEFAULT 'U', --U and A\n", " user_country VARCHAR(2),\n", " is_active BOOLEAN DEFAULT FALSE,\n", " create_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n", " last_updated_ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n", ");" ] }, { "cell_type": "code", "execution_count": 102, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 102, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO users (user_first_name, user_last_name, user_email_id, user_country)\n", "VALUES ('Donald', 'Duck', 'donald@duck.com', 'IN')" ] }, { "cell_type": "code", "execution_count": 103, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "1 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 103, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO users (user_first_name, user_last_name, user_email_id, user_role, is_active, user_country)\n", "VALUES ('Mickey', 'Mouse', 'mickey@mouse.com', 'U', true, 'US')" ] }, { "cell_type": "code", "execution_count": 104, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "3 rows affected.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 104, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "INSERT INTO users \n", " (user_first_name, user_last_name, user_email_id, user_password, user_role, is_active, user_country) \n", "VALUES \n", " ('Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'h9LAz7p7ub', 'U', true, 'CA'),\n", " ('Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'oEofndp', 'U', true, 'FR'),\n", " ('Addie', 'Mesias', 'amesias2@twitpic.com', 'ih7Y69u56', 'U', true, 'AU')" ] }, { "cell_type": "code", "execution_count": 105, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 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", "
user_iduser_first_nameuser_last_nameuser_email_iduser_email_validateduser_passworduser_roleuser_countryis_activecreate_tslast_updated_ts
5AddieMesiasamesias2@twitpic.comFalseih7Y69u56UAUTrue2020-11-14 15:40:12.4149322020-11-14 15:40:12.414932
3GordanBradockgbradock0@barnesandnoble.comFalseh9LAz7p7ubUCATrue2020-11-14 15:40:12.4149322020-11-14 15:40:12.414932
4TobeLynesstlyness1@paginegialle.itFalseoEofndpUFRTrue2020-11-14 15:40:12.4149322020-11-14 15:40:12.414932
1DonaldDuckdonald@duck.comFalseNoneUINFalse2020-11-14 15:40:10.8789082020-11-14 15:40:10.878908
2MickeyMousemickey@mouse.comFalseNoneUUSTrue2020-11-14 15:40:11.6838872020-11-14 15:40:11.683887
" ], "text/plain": [ "[(5, 'Addie', 'Mesias', 'amesias2@twitpic.com', False, 'ih7Y69u56', 'U', 'AU', True, datetime.datetime(2020, 11, 14, 15, 40, 12, 414932), datetime.datetime(2020, 11, 14, 15, 40, 12, 414932)),\n", " (3, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', False, 'h9LAz7p7ub', 'U', 'CA', True, datetime.datetime(2020, 11, 14, 15, 40, 12, 414932), datetime.datetime(2020, 11, 14, 15, 40, 12, 414932)),\n", " (4, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', False, 'oEofndp', 'U', 'FR', True, datetime.datetime(2020, 11, 14, 15, 40, 12, 414932), datetime.datetime(2020, 11, 14, 15, 40, 12, 414932)),\n", " (1, 'Donald', 'Duck', 'donald@duck.com', False, None, 'U', 'IN', False, datetime.datetime(2020, 11, 14, 15, 40, 10, 878908), datetime.datetime(2020, 11, 14, 15, 40, 10, 878908)),\n", " (2, 'Mickey', 'Mouse', 'mickey@mouse.com', False, None, 'U', 'US', True, datetime.datetime(2020, 11, 14, 15, 40, 11, 683887), datetime.datetime(2020, 11, 14, 15, 40, 11, 683887))]" ] }, "execution_count": 105, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM users\n", "ORDER BY user_country" ] }, { "cell_type": "code", "execution_count": 106, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "5 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", "
user_iduser_first_nameuser_last_nameuser_email_iduser_country
2MickeyMousemickey@mouse.comUS
5AddieMesiasamesias2@twitpic.comAU
3GordanBradockgbradock0@barnesandnoble.comCA
4TobeLynesstlyness1@paginegialle.itFR
1DonaldDuckdonald@duck.comIN
" ], "text/plain": [ "[(2, 'Mickey', 'Mouse', 'mickey@mouse.com', 'US'),\n", " (5, 'Addie', 'Mesias', 'amesias2@twitpic.com', 'AU'),\n", " (3, 'Gordan', 'Bradock', 'gbradock0@barnesandnoble.com', 'CA'),\n", " (4, 'Tobe', 'Lyness', 'tlyness1@paginegialle.it', 'FR'),\n", " (1, 'Donald', 'Duck', 'donald@duck.com', 'IN')]" ] }, "execution_count": 106, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT user_id,\n", " user_first_name,\n", " user_last_name,\n", " user_email_id,\n", " user_country\n", "FROM users\n", "ORDER BY \n", " CASE WHEN user_country = 'US' THEN 0\n", " ELSE 1\n", " END, user_country" ] } ], "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 }