{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Overview of Sub Queries\n", "Let us understand details related to Sub Queries. We will also briefly discuss about nested sub queries." ] }, { "cell_type": "code", "execution_count": 24, "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 have queries in from clause and such queries are called as sub queries.\n", "* Sub queries are commonly used with queries using analytic functions to filter the data further. We will see details after going through analytic functions as part of this section.\n", "* It is mandatory to have alias for the sub query.\n", "* Sub queries can also be used in `WHERE` clause with `IN` as well as `EXISTS`. As part of the sub query we can have join like conditions between tables in `FROM` clause of the main query and sub query. Such queries are called as **Nested Sub Queries**." ] }, { "cell_type": "code", "execution_count": 25, "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": 26, "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": "markdown", "metadata": {}, "source": [ "```{note}\n", "Simplest example for a subquery\n", "```" ] }, { "cell_type": "code", "execution_count": 27, "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", "
current_date
2020-12-01
" ], "text/plain": [ "[(datetime.date(2020, 12, 1),)]" ] }, "execution_count": 27, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM (SELECT current_date) AS q" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "Realistic example for a subquery. We will get into details related to this query after covering analytic functions\n", "```" ] }, { "cell_type": "code", "execution_count": 28, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "20 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", "
order_dateorder_item_product_idrevenuedrnk
2013-07-25 00:00:0010045599.721
2013-07-25 00:00:001915099.492
2013-07-25 00:00:009574499.703
2013-07-25 00:00:003653359.444
2013-07-25 00:00:0010732999.855
2013-07-26 00:00:00100410799.461
2013-07-26 00:00:003657978.672
2013-07-26 00:00:009576899.543
2013-07-26 00:00:001916799.324
2013-07-26 00:00:0010144798.085
2013-07-27 00:00:0010049599.521
2013-07-27 00:00:001915999.402
2013-07-27 00:00:009575699.623
2013-07-27 00:00:0010735399.734
2013-07-27 00:00:003655099.155
2013-07-28 00:00:0010045599.721
2013-07-28 00:00:009575099.662
2013-07-28 00:00:003654799.203
2013-07-28 00:00:004034419.664
2013-07-28 00:00:001914299.575
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 2),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 3),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 4),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 5),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 1),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 2),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 3),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 4),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1014, Decimal('4798.08'), 5),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1004, Decimal('9599.52'), 1),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 191, Decimal('5999.40'), 2),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 957, Decimal('5699.62'), 3),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 1073, Decimal('5399.73'), 4),\n", " (datetime.datetime(2013, 7, 27, 0, 0), 365, Decimal('5099.15'), 5),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 1004, Decimal('5599.72'), 1),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 957, Decimal('5099.66'), 2),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 365, Decimal('4799.20'), 3),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 403, Decimal('4419.66'), 4),\n", " (datetime.datetime(2013, 7, 28, 0, 0), 191, Decimal('4299.57'), 5)]" ] }, "execution_count": 28, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM (\n", " SELECT nq.*,\n", " dense_rank() OVER (\n", " PARTITION BY order_date\n", " ORDER BY revenue DESC\n", " ) AS drnk\n", " 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 \n", " 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, oi.order_item_product_id\n", " ) nq\n", ") nq1\n", "WHERE drnk <= 5\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 20" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "Multiple realistic examples for nested sub queries. You can see example with `IN` as well as `EXISTS` operators.\n", "```" ] }, { "cell_type": "code", "execution_count": 29, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "0 rows affected.\n" ] }, { "data": { "text/html": [ "\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
" ], "text/plain": [ "[]" ] }, "execution_count": 29, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM order_items oi\n", "WHERE oi.order_item_order_id \n", " NOT IN (\n", " SELECT order_id FROM orders o\n", " WHERE o.order_id = oi.order_item_order_id\n", " )\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 30, "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
172198
" ], "text/plain": [ "[(172198,)]" ] }, "execution_count": 30, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT count(1) FROM order_items oi\n", "WHERE oi.order_item_order_id \n", " IN (\n", " SELECT order_id FROM orders o\n", " WHERE o.order_id = oi.order_item_order_id\n", " )\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "0 rows affected.\n" ] }, { "data": { "text/html": [ "\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
" ], "text/plain": [ "[]" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM order_items oi\n", "WHERE NOT EXISTS (\n", " SELECT 1 FROM orders o\n", " WHERE o.order_id = oi.order_item_order_id\n", " )\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 32, "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", " \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
119571299.98299.98
2210731199.99199.99
325025250.050.0
424031129.99129.99
54897249.9824.99
643655299.9559.99
745023150.050.0
8410144199.9249.98
959571299.98299.98
1053655299.9559.99
" ], "text/plain": [ "[(1, 1, 957, 1, 299.98, 299.98),\n", " (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),\n", " (5, 4, 897, 2, 49.98, 24.99),\n", " (6, 4, 365, 5, 299.95, 59.99),\n", " (7, 4, 502, 3, 150.0, 50.0),\n", " (8, 4, 1014, 4, 199.92, 49.98),\n", " (9, 5, 957, 1, 299.98, 299.98),\n", " (10, 5, 365, 5, 299.95, 59.99)]" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM order_items oi\n", "WHERE EXISTS (\n", " SELECT 1 FROM orders o\n", " WHERE o.order_id = oi.order_item_order_id\n", " )\n", "LIMIT 10" ] } ], "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 }