workshops/subqueries/notebook.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "# Session 2 - Subqueries\r\n",
                "\r\n",
                "\r\n",
                "## Definition\r\n",
                "- (Always) executed first\r\n",
                "- Always between `()`\r\n",
                "- Subqueries can be nested at > 1 level.\r\n",
                "- Outer Query\r\n",
                "    - Inner Query 1\r\n",
                "    - Inner Query 2\r\n",
                "    - ..."
            ],
            "metadata": {
                "azdata_cell_guid": "806648ab-c688-4c24-94f0-9b56b45afbe5"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Example - 1\r\n",
                "**What** is the highest salary of all the employees?"
            ],
            "metadata": {
                "azdata_cell_guid": "e969e223-c1c2-40de-bb6c-2435ed19329f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT MAX(Salary)​ AS [Highest Salary]\r\n",
                "FROM Employee;"
            ],
            "metadata": {
                "azdata_cell_guid": "9e94c06c-6bba-45d7-b9b5-dfe0a72e8d40",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning: Null value is eliminated by an aggregate or other SET operation."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(3 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.079"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 35,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "class"
                                    },
                                    {
                                        "name": "unitprice"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "NULL",
                                    "1": "NULL",
                                    "2": "NULL"
                                },
                                {
                                    "0": "1",
                                    "1": "4.59",
                                    "2": "3301"
                                },
                                {
                                    "0": "2",
                                    "1": "78.91",
                                    "2": "403000"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>class</th><th>unitprice</th><th>(No column name)</th></tr><tr><td>NULL</td><td>NULL</td><td>NULL</td></tr><tr><td>1</td><td>4.59</td><td>3301</td></tr><tr><td>2</td><td>78.91</td><td>403000</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 35
        },
        {
            "cell_type": "markdown",
            "source": [
                "**Who** has the highest salary?"
            ],
            "metadata": {
                "azdata_cell_guid": "e961dcc9-9528-4560-b282-103cbca108f0"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT \r\n",
                " Lastname\r\n",
                ",Firstname\r\n",
                ",Salary​\r\n",
                "FROM Employee​\r\n",
                "WHERE Salary = (SELECT MAX(Salary)​ FROM Employee)"
            ],
            "metadata": {
                "azdata_cell_guid": "794774ae-daae-4894-9775-553fe2eac810"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Warning: Null value is eliminated by an aggregate or other SET operation."
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(1 row affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.087"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 34,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "Lastname"
                                    },
                                    {
                                        "name": "Firstname"
                                    },
                                    {
                                        "name": "Salary"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Fuller",
                                    "1": "Andrew",
                                    "2": "90000.00"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>Lastname</th><th>Firstname</th><th>Salary</th></tr><tr><td>Fuller</td><td>Andrew</td><td>90000.00</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 34
        },
        {
            "cell_type": "markdown",
            "source": [
                "> ## Explanation\r\n",
                "> \r\n",
                "> First the table `Employee` is searched to determine the highest salary (= subquery). ​Then the table is searched a second time (= main query) to evaluate each employee's salary against the determined maximum of the subquery. "
            ],
            "metadata": {
                "azdata_cell_guid": "65735f6b-c0dd-453a-87be-1c73daa1cf08"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Subquery that returns 1 column 1 row.\r\n",
                "## Example 2\r\n",
                "Give a list of employees that earn more than the average wage.\r\n",
                "1. Determine the average wage\r\n",
                "2. Determine the Employees that earn more than this avergae."
            ],
            "metadata": {
                "azdata_cell_guid": "7fa18893-7fe2-46ed-a04b-008b7ee29792"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT \r\n",
                " Lastname\r\n",
                ",Firstname\r\n",
                ",Salary​\r\n",
                "FROM Employee​\r\n",
                "WHERE Salary > (SELECT AVG(Salary) FROM Employee);"
            ],
            "metadata": {
                "azdata_cell_guid": "28273151-d74d-4b6e-bd47-72c213fb4be8"
            },
            "outputs": [],
            "execution_count": 14
        },
        {
            "cell_type": "markdown",
            "source": [
                "Who (Lastname and Firstname) is the youngest employee from Canada?​\r\n",
                "1. What's the birthday of the youngest employee?\r\n",
                "2. Whats the birthday of the youngest employee from canada?\r\n",
                "3. The actual question: Who (Lastname and Firstname) is the youngest employee from Canada?​ "
            ],
            "metadata": {
                "azdata_cell_guid": "03cbfef8-9dc6-420a-a608-6b9d5f3bed3b"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT MAX(Birthdate) FROM Employee --WHERE Country = 'Canada')"
            ],
            "metadata": {
                "azdata_cell_guid": "55e1746d-c4d5-4030-aea0-d797c7175100"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT \r\n",
                " Lastname\r\n",
                ",Firstname​\r\n",
                "FROM Employee\r\n",
                "WHERE Country = 'Canada'​\r\n",
                "  AND Birthdate = (SELECT MAX(Birthdate) FROM Employee WHERE Country = 'Canada');​"
            ],
            "metadata": {
                "azdata_cell_guid": "d2bd1145-2ee6-4268-bdca-2910b7f543a6"
            },
            "outputs": [],
            "execution_count": 15
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Subquery that returns 1 column and multiple rows\r\n",
                "The result can be used as a list, there the following operators can be used. `IN, NOT IN, ANY, ALL​\r\n",
                "> Note that `IN` and `ANY` are equivalent."
            ],
            "metadata": {
                "azdata_cell_guid": "c6744d9e-452e-4c67-a2af-5b91887adc2e"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 3\r\n",
                "1. Give all players(PlayerNo) that played matches."
            ],
            "metadata": {
                "azdata_cell_guid": "73125369-375b-4499-909d-c949002b871f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "-- Using JOIN From Match to Players\r\n",
                "SELECT\r\n",
                " Match.PlayerNo\r\n",
                "FROM Matches Match\r\n",
                "    JOIN Players Player ON Player.PlayerNo = Match.PlayerNo "
            ],
            "metadata": {
                "azdata_cell_guid": "e91ae8ca-b796-4b77-931d-0f01b99af43b"
            },
            "outputs": [],
            "execution_count": 28
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "-- Using JOIN from Players to Matches\r\n",
                "SELECT\r\n",
                " Player.PlayerNo\r\n",
                "FROM Players Player\r\n",
                "    JOIN Matches Match ON Player.PlayerNo = Match.PlayerNo "
            ],
            "metadata": {
                "azdata_cell_guid": "0acb6a0d-5d51-4278-8512-2bd44e1f9ffc"
            },
            "outputs": [],
            "execution_count": 27
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "-- Using a Subquery\r\n",
                "SELECT\r\n",
                " PlayerNo\r\n",
                "FROM Players ​\r\n",
                "WHERE PlayerNo ANY  (SELECT PlayerNo FROM Matches);"
            ],
            "metadata": {
                "azdata_cell_guid": "40f2b70e-23ac-43d0-ab37-f646555b281a"
            },
            "outputs": [],
            "execution_count": 20
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 4\r\n",
                "Give name of the players who live in the same town as R. Parmenter​\r\n",
                "1. In which column is 'R' located?\r\n",
                "2. In which column is 'Parmenter' located?"
            ],
            "metadata": {
                "azdata_cell_guid": "147f5d88-648c-48db-8caa-9bc28cd185a3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT \r\n",
                " [Name]\r\n",
                "FROM Players​\r\n",
                "WHERE Town = (SELECT Town FROM Players WHERE [Name] = 'Parmenter' AND Initials = 'R');"
            ],
            "metadata": {
                "azdata_cell_guid": "042595d7-e4b3-4976-80e9-1ea004af55bd"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 5\r\n",
                "Why is the query below not working?​\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "7f1cb2c5-7b4c-4517-a4db-8dc3030df49f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT \r\n",
                " [Name]\r\n",
                "FROM Players\r\n",
                "WHERE Town = (SELECT Town FROM Players WHERE [Name] = 'Parmenter');"
            ],
            "metadata": {
                "azdata_cell_guid": "f5130aef-4f58-43da-ba5c-2fb698a5d667"
            },
            "outputs": [],
            "execution_count": 30
        },
        {
            "cell_type": "markdown",
            "source": [
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                "\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "7cc3851f-f2b4-453a-b9bb-164329a3944b"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Example 6\r\n",
                "Give all players that **did not play any** matches​"
            ],
            "metadata": {
                "azdata_cell_guid": "fdfe065b-e1a8-4875-9259-2d04767ef23d"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT\r\n",
                "PlayerNo​\r\n",
                "FROM Players​\r\n",
                "WHERE PlayerNo NOT IN (SELECT PlayerNo FROM Matches)"
            ],
            "metadata": {
                "azdata_cell_guid": "4e9644fa-8c62-422f-8ae4-098ab7cf28a5",
                "tags": []
            },
            "outputs": [],
            "execution_count": 33
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT\r\n",
                " PlayerNo\r\n",
                "FROM Players\r\n",
                "\r\n",
                "EXCEPT\r\n",
                "\r\n",
                "SELECT\r\n",
                "PlayerNo\r\n",
                "FROM Matches"
            ],
            "metadata": {
                "azdata_cell_guid": "01529c01-a1fe-48b4-8f85-cdf9660fe627"
            },
            "outputs": [],
            "execution_count": 32
        },
        {
            "cell_type": "markdown",
            "source": [
                "# `ANY`, `ALL`\r\n",
                "Slide 8-9"
            ],
            "metadata": {
                "azdata_cell_guid": "db81992e-7f1e-4f6b-94a9-b961e54ccc74"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Correlated Subqueries\r\n",
                ""
            ],
            "metadata": {
                "azdata_cell_guid": "f6dbf34d-e256-43f9-9634-806d1723b70e"
            }
        },
        {
            "cell_type": "markdown",
            "source": [
                "Give employees with a salary larger than the average salary\r\n",
                "1. Is this a correlated Subquery?"
            ],
            "metadata": {
                "azdata_cell_guid": "e8d3de0b-bb91-43f6-aa54-a7615d17c4a3"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT \r\n",
                " Lastname\r\n",
                ",Firstname\r\n",
                ",Salary​\r\n",
                "FROM Employee​\r\n",
                "WHERE Salary = (SELECT MAX(Salary)​ FROM Employee)"
            ],
            "metadata": {
                "azdata_cell_guid": "bc6f0b3b-9f4a-4cd7-b7c8-d2755653a98a"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Give the employees whose salary is larger than the average of the salary of the employees who report to the same boss.​"
            ],
            "metadata": {
                "azdata_cell_guid": "0fc79865-41e6-4289-83be-332dc6af507a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\r\n",
                "SELECT \r\n",
                " Lastname\r\n",
                ",Firstname\r\n",
                ",Salary​\r\n",
                "FROM Employee AS e​\r\n",
                "WHERE salary >​ \r\n",
                "    (SELECT AVG(salary)​\r\n",
                "     FROM employee​\r\n",
                "     WHERE reportsto = e.reportsto);​"
            ],
            "metadata": {
                "azdata_cell_guid": "3c7337aa-9a75-443d-8f7a-ab8956d5ee09"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Correlated Subqueries - EXISTS\r\n",
                "Give the players that did not play any matches yet."
            ],
            "metadata": {
                "azdata_cell_guid": "accc9a6f-5de7-4e12-81c3-4d6960786f7f"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT *​\r\n",
                "FROM Players AS p​\r\n",
                "WHERE NOT EXISTS​\r\n",
                "(SELECT * FROM Matches​ WHERE PlayerNo = p.playerno);​"
            ],
            "metadata": {
                "azdata_cell_guid": "a2a8a330-63cf-4b9a-9565-6dfcbb55574b"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Give the players that did play matches"
            ],
            "metadata": {
                "azdata_cell_guid": "0c573212-99ca-45cc-b86e-f816b084a9bf"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT *​\r\n",
                "FROM Players AS p​\r\n",
                "WHERE EXISTS​\r\n",
                "(SELECT * FROM Matches​ WHERE PlayerNo = p.playerno);​"
            ],
            "metadata": {
                "azdata_cell_guid": "43933e93-c58d-4e85-b624-95f88a349741"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "#  Multiple ways to achieve the same result:\r\n",
                "Who did not play any matches? \r\n",
                "1. Outer Join\r\n",
                "2. Simple Subquery\r\n",
                "3. Correlated Subquery\r\n",
                "4. Except"
            ],
            "metadata": {
                "azdata_cell_guid": "e03fd428-6e61-442d-84f8-5f408e86680a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT p.playerno​\r\n",
                "FROM players AS p​\r\n",
                "     LEFT JOIN​ matches AS m​ ON p.playerno = m.playerno​\r\n",
                "WHERE m.playerno IS NULL;"
            ],
            "metadata": {
                "azdata_cell_guid": "abea843c-92f5-42f2-ab78-7c713c6534fc"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT playerno ​\r\n",
                "FROM players​\r\n",
                "WHERE playerno NOT IN​ (SELECT playerno​ FROM matches);"
            ],
            "metadata": {
                "azdata_cell_guid": "740573e8-7d3d-4094-8005-ea66a4068a9c"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT playerno​\r\n",
                "FROM players AS p​\r\n",
                "WHERE NOT EXISTS​ (SELECT * FROM matches​ WHERE playerno =  p.playerno);"
            ],
            "metadata": {
                "azdata_cell_guid": "8f77905f-9021-40fd-9a0a-4c6430a5d843"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "code",
            "source": [
                "USE Tennis;\r\n",
                "SELECT\r\n",
                " PlayerNo\r\n",
                "FROM Players\r\n",
                "\r\n",
                "EXCEPT\r\n",
                "\r\n",
                "SELECT\r\n",
                "PlayerNo\r\n",
                "FROM Matches"
            ],
            "metadata": {
                "azdata_cell_guid": "3b25c38b-0084-4d0a-847e-024655fd594b"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "# Derived Tables\r\n",
                "Sculpt the table in the from clause, replication of the CASE statement."
            ],
            "metadata": {
                "azdata_cell_guid": "20c33953-c91c-42b8-b509-cb5d7efb7f24"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\n",
                "SELECT ​\r\n",
                "    CASE c.country​\r\n",
                "        WHEN 'USA'      THEN 'Northern America'​\r\n",
                "        WHEN 'Canada'   THEN 'Northern America'​\r\n",
                "        ELSE 'Rest of world' ​\r\n",
                "    END AS regionclass, sum(orderamount)​\r\n",
                "FROM customer c \r\n",
                "    JOIN orders o ON c.CustomerID = o.CustomerID​\r\n",
                "GROUP BY\r\n",
                "    CASE c.country​\r\n",
                "        WHEN 'USA' then 'Northern America'​\r\n",
                "        WHEN 'Canada' then 'Northern America'​\r\n",
                "        ELSE 'Rest of world' ​\r\n",
                "    END;"
            ],
            "metadata": {
                "azdata_cell_guid": "afa373c4-00ea-4740-80c6-88f15643c1d8"
            },
            "outputs": [
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "(2 rows affected)"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "display_data",
                    "data": {
                        "text/html": "Total execution time: 00:00:00.133"
                    },
                    "metadata": {}
                },
                {
                    "output_type": "execute_result",
                    "execution_count": 36,
                    "data": {
                        "application/vnd.dataresource+json": {
                            "schema": {
                                "fields": [
                                    {
                                        "name": "regionclass"
                                    },
                                    {
                                        "name": "(No column name)"
                                    }
                                ]
                            },
                            "data": [
                                {
                                    "0": "Rest of world",
                                    "1": "940289.31"
                                },
                                {
                                    "0": "Northern America",
                                    "1": "3143377.03"
                                }
                            ]
                        },
                        "text/html": "<table><tr><th>regionclass</th><th>(No column name)</th></tr><tr><td>Rest of world</td><td>940289.31</td></tr><tr><td>Northern America</td><td>3143377.03</td></tr></table>"
                    },
                    "metadata": {}
                }
            ],
            "execution_count": 36
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\n",
                "SELECT regionclass, sum(total) from​\r\n",
                "    (​\r\n",
                "    SELECT ​\r\n",
                "    CASE c.country​\r\n",
                "        WHEN 'USA'      THEN 'Northern America'​\r\n",
                "        WHEN 'Canada'   THEN 'Northern America'​\r\n",
                "        ELSE 'Rest of world' ​\r\n",
                "    END AS region, orderamount​\r\n",
                "    FROM customer c \r\n",
                "        JOIN orders o ON c.CustomerID = o.CustomerID​\r\n",
                "    ) ​AS totals(regionclass, total)​\r\n",
                "GROUP BY regionclass;"
            ],
            "metadata": {
                "azdata_cell_guid": "913ee7c8-ea70-44ca-b0b5-ea8791d11b89"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Give per productclass the price of the cheapest product and a product that has that price. "
            ],
            "metadata": {
                "azdata_cell_guid": "f47cdb72-e8a3-4f5e-b933-d45d91ea711a"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "USE Xtreme;\n",
                "SELECT class, unitprice,​\n",
                "    SELECT TOP 1 productid​\n",
                "    WHERE productclassid = class AND price = unitprice​\n",
                "FROM​\n",
                "    SELECT productclassid, MIN(price)​\n",
                "    GROUP BY productclassid​\n",
                "",
                "metaazdata_cell_guid",
                ": [],",
                "exec",
                {
                    "metadata": {
                        "azdata_cell_guid": "66b46f92-e780-4755-8e90-67f474a61027"
                    }
                }
            ],
            "metadata": {
                "azdata_cell_guid": "e5c0494a-b986-4570-af0d-98f34ab450ac"
            },
            "outputs": [],
            "execution_count": 0
        },
        {
            "cell_type": "markdown",
            "source": [
                "Montly Gross Margin"
            ],
            "metadata": {
                "azdata_cell_guid": "c4ba944a-e692-4d94-a6ad-bf6c522e3529"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT \r\n",
                " ISNULL(Ordered.Month, Purchased.Month) AS [Month]\r\n",
                ",ISNULL(Ordered.Amount, 0) - ISNULL(Purchased.Amount, 0) AS [Margin]\r\n",
                "FROM\r\n",
                "    (\r\n",
                "    SELECT \r\n",
                "    FORMAT(OrderDate, 'yyyy-MM')\r\n",
                "    ,SUM(OrderAmount)\r\n",
                "    FROM Orders\r\n",
                "    GROUP BY FORMAT(OrderDate, 'yyyy-MM')\r\n",
                "    ) AS Ordered(Month, Amount)\r\n",
                "FULL JOIN\r\n",
                "    (\r\n",
                "    SELECT \r\n",
                "    FORMAT(OrderDate, 'yyyy-MM')\r\n",
                "    ,SUM(Product.Price * Purchase.UnitsOnOrder)\r\n",
                "    FROM Purchases AS Purchase\r\n",
                "        JOIN Product ON Purchase.ProductId = Product.ProductId\r\n",
                "    GROUP BY FORMAT(OrderDate, 'yyyy-MM')\r\n",
                "    ) AS Purchased(Month, Amount) \r\n",
                "ON Ordered.Month = Purchased.Month\r\n",
                "ORDER BY [Month];"
            ],
            "metadata": {
                "azdata_cell_guid": "df2adce2-7d6c-4696-ba33-2bf9e9febf59"
            },
            "outputs": [],
            "execution_count": 0
        }
    ]
}