SPP57.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQL Practice Problems: 57 beginning, intermediate, and advanced challenges for you to solve using a “learn-by-doing” approach"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- This notebook contains my solution for SQL practice problems from [SPP 57 book](https://www.amazon.com/SQL-Practice-Problems-learn-doing-ebook/dp/B01N41VQFO) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting to MS SQL DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyodbc\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "class db_connection:\n",
    "    def __new__(self):\n",
    "        if not hasattr(self,'instance'):\n",
    "            self.instance = super(db_connection, self).__new__(self)\n",
    "            self.conn = pyodbc.connect('Driver={SQL Server};'\n",
    "                      f'Server={os.environ[\"MSSQL_SERVER\"]};'\n",
    "                      'Database=Northwind_SPP;'\n",
    "                      'Trusted_Connection=yes;')\n",
    "        return self.instance\n",
    "    \n",
    "    def get_conn(self):\n",
    "        return self.conn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute query using cursor object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Speedy Express', '(503) 555-9831')\n",
      "(2, 'United Package', '(503) 555-3199')\n",
      "(3, 'Federal Shipping', '(503) 555-9931')\n"
     ]
    }
   ],
   "source": [
    "connection = db_connection()\n",
    "conn = connection.get_conn()\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"\"\"\n",
    "    SELECT * FROM shippers\n",
    "\"\"\")\n",
    "\n",
    "for row in cursor:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Execute query using pd.read_sql_query() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ShipperID       CompanyName           Phone\n",
      "0          1    Speedy Express  (503) 555-9831\n",
      "1          2    United Package  (503) 555-3199\n",
      "2          3  Federal Shipping  (503) 555-9931\n",
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "sql_query = pd.read_sql_query('SELECT * FROM shippers', conn)\n",
    "print(sql_query)\n",
    "print(type(sql_query))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Query result in pandas dataframe looks better, so I'm going to use this approach to execute query and validate result\n",
    "- Let's make a helper function to execute sql query and print result in pandas data frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sql(query : str)->None:\n",
    "    \"\"\"\n",
    "    Take query as an argument, execute it and print the query result\n",
    "    \n",
    "    Arguments:\n",
    "    query -> string type. Query to execute\n",
    "    \"\"\"\n",
    "    result = pd.read_sql_query(query, db_connection().get_conn())\n",
    "    print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ShipperID       CompanyName           Phone\n",
      "0          1    Speedy Express  (503) 555-9831\n",
      "1          2    United Package  (503) 555-3199\n",
      "2          3  Federal Shipping  (503) 555-9931\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"SELECT * FROM shippers\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DB Diagram for Practice Database\n",
    "![ERD](ERD.PNG)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introductory Problems\n",
    "## 1. Which shippers do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ShipperID       CompanyName           Phone\n",
      "0          1    Speedy Express  (503) 555-9831\n",
      "1          2    United Package  (503) 555-3199\n",
      "2          3  Federal Shipping  (503) 555-9931\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT * FROM shippers\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Certain fields from categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     CategoryName                                        Description\n",
      "0       Beverages        Soft drinks, coffees, teas, beers, and ales\n",
      "1      Condiments  Sweet and savory sauces, relishes, spreads, an...\n",
      "2     Confections                Desserts, candies, and sweet breads\n",
      "3  Dairy Products                                            Cheeses\n",
      "4  Grains/Cereals                Breads, crackers, pasta, and cereal\n",
      "5    Meat/Poultry                                     Prepared meats\n",
      "6         Produce                          Dried fruit and bean curd\n",
      "7         Seafood                                   Seaweed and fish\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT CategoryName, Description FROM Categories\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Sales Representatives"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName   LastName   HireDate\n",
      "0     Nancy    Davolio 2010-05-01\n",
      "1     Janet  Leverling 2010-04-01\n",
      "2  Margaret    Peacock 2011-05-03\n",
      "3   Michael     Suyama 2011-10-17\n",
      "4    Robert       King 2012-01-02\n",
      "5      Anne  Dodsworth 2012-11-15\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT FirstName, LastName, HireDate FROM Employees\n",
    "    WHERE Title = 'Sales Representative'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Sales Representatives in the United States\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Country\n",
      "0      UK\n",
      "1     USA\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"SELECT DISTINCT Country FROM Employees\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName   LastName   HireDate\n",
      "0     Nancy    Davolio 2010-05-01\n",
      "1     Janet  Leverling 2010-04-01\n",
      "2  Margaret    Peacock 2011-05-03\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT FirstName, LastName, HireDate From Employees\n",
    "    WHERE Title = 'Sales Representative' and Country = 'USA'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Orders Placed by Specific Employee ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID           OrderDate\n",
      "0     10248 2014-07-04 08:00:00\n",
      "1     10254 2014-07-11 02:00:00\n",
      "2     10269 2014-07-31 00:00:00\n",
      "3     10297 2014-09-04 21:00:00\n",
      "4     10320 2014-10-03 12:00:00\n",
      "5     10333 2014-10-18 18:00:00\n",
      "6     10358 2014-11-20 05:00:00\n",
      "7     10359 2014-11-21 14:00:00\n",
      "8     10372 2014-12-04 10:00:00\n",
      "9     10378 2014-12-10 00:00:00\n",
      "10    10397 2014-12-27 17:00:00\n",
      "11    10463 2015-03-04 13:00:00\n",
      "12    10474 2015-03-13 16:00:00\n",
      "13    10477 2015-03-17 02:00:00\n",
      "14    10529 2015-05-07 01:00:00\n",
      "15    10549 2015-05-27 03:00:00\n",
      "16    10569 2015-06-16 15:00:00\n",
      "17    10575 2015-06-20 22:00:00\n",
      "18    10607 2015-07-22 09:00:00\n",
      "19    10648 2015-08-28 22:00:00\n",
      "20    10649 2015-08-28 00:00:00\n",
      "21    10650 2015-08-29 06:00:00\n",
      "22    10654 2015-09-02 07:00:00\n",
      "23    10675 2015-09-19 06:00:00\n",
      "24    10711 2015-10-21 03:00:00\n",
      "25    10714 2015-10-22 03:00:00\n",
      "26    10721 2015-10-29 08:00:00\n",
      "27    10730 2015-11-05 07:00:00\n",
      "28    10761 2015-12-02 08:00:00\n",
      "29    10812 2016-01-02 02:00:00\n",
      "30    10823 2016-01-09 17:00:00\n",
      "31    10841 2016-01-20 21:00:00\n",
      "32    10851 2016-01-26 00:00:00\n",
      "33    10866 2016-02-03 01:00:00\n",
      "34    10869 2016-02-04 09:00:00\n",
      "35    10870 2016-02-04 12:00:00\n",
      "36    10872 2016-02-05 06:00:00\n",
      "37    10874 2016-02-06 14:00:00\n",
      "38    10899 2016-02-20 09:00:00\n",
      "39    10922 2016-03-03 02:00:00\n",
      "40    10954 2016-03-17 16:00:00\n",
      "41    11043 2016-04-22 17:00:00\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID, OrderDate FROM Orders\n",
    "    WHERE EmployeeID = 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Suppliers and ContactTitles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    SupplierID                 ContactName                  ContactTitle\n",
      "0            1            Charlotte Cooper            Purchasing Manager\n",
      "1            2               Shelley Burke           Order Administrator\n",
      "2            3               Regina Murphy          Sales Representative\n",
      "3            5  Antonio del Valle Saavedra          Export Administrator\n",
      "4            6                 Mayumi Ohno      Marketing Representative\n",
      "5            8                Peter Wilson          Sales Representative\n",
      "6            9               Lars Peterson                   Sales Agent\n",
      "7           11               Petra Winkler                 Sales Manager\n",
      "8           12                 Martin Bein  International Marketing Mgr.\n",
      "9           13               Sven Petersen   Coordinator Foreign Markets\n",
      "10          14                  Elio Rossi          Sales Representative\n",
      "11          16               Cheryl Saylor         Regional Account Rep.\n",
      "12          17               Michael Björn          Sales Representative\n",
      "13          18              Guylène Nodier                 Sales Manager\n",
      "14          19               Robb Merchant       Wholesale Account Agent\n",
      "15          20                Chandra Leka                         Owner\n",
      "16          21              Niels Petersen                 Sales Manager\n",
      "17          22                 Dirk Luchte            Accounting Manager\n",
      "18          23              Anne Heikkonen               Product Manager\n",
      "19          24             Wendy Mackenzie          Sales Representative\n",
      "20          26            Giovanni Giudici           Order Administrator\n",
      "21          27              Marie Delamare                 Sales Manager\n",
      "22          28                  Eliane Noz          Sales Representative\n",
      "23          29              Chantal Goulet            Accounting Manager\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT SupplierID, ContactName, ContactTitle\n",
    "    FROM Suppliers\n",
    "    WHERE ContactTitle != 'Marketing Manager'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Products with \"queso\" in ProductName"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ProductID                ProductName\n",
      "0         11             Queso Cabrales\n",
      "1         12  Queso Manchego La Pastora\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT ProductID, ProductName\n",
    "    FROM Products\n",
    "    WHERE LOWER(ProductName) LIKE '%queso%'\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Orders Shipping to France or Belgium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID CustomerID ShipCountry\n",
      "0     10248      VINET      France\n",
      "1     10251      VICTE      France\n",
      "2     10252      SUPRD     Belgium\n",
      "3     10265      BLONP      France\n",
      "4     10274      VINET      France\n",
      "..      ...        ...         ...\n",
      "91    11035      SUPRD     Belgium\n",
      "92    11038      SUPRD     Belgium\n",
      "93    11043      SPECD      France\n",
      "94    11051      LAMAI      France\n",
      "95    11076      BONAP      France\n",
      "\n",
      "[96 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID, CustomerID, ShipCountry\n",
    "    FROM Orders\n",
    "    WHERE ShipCountry in ('France', 'Belgium')\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Order Shipping to Any Country in Latin America"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Since there's no list of Latin American countries, we're going to just use the list of Latin American countries that happen to be in the Orders table \n",
    "    - Brazil\n",
    "    - Mexico\n",
    "    - Argentina\n",
    "    - Venezuela"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     OrderID CustomerID ShipCountry\n",
      "0      10250      HANAR      Brazil\n",
      "1      10253      HANAR      Brazil\n",
      "2      10256      WELLI      Brazil\n",
      "3      10257      HILAA   Venezuela\n",
      "4      10259      CENTC      Mexico\n",
      "..       ...        ...         ...\n",
      "168    11065      LILAS   Venezuela\n",
      "169    11068      QUEEN      Brazil\n",
      "170    11069      TORTU      Mexico\n",
      "171    11071      LILAS   Venezuela\n",
      "172    11073      PERIC      Mexico\n",
      "\n",
      "[173 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID, CustomerID, ShipCountry\n",
    "    FROM Orders\n",
    "    WHERE ShipCountry IN ('Brazil', 'Mexico', 'Argentina', 'Venezuela')\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Employees, in Order of Age"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName   LastName                     Title  BirthDate\n",
      "0  Margaret    Peacock      Sales Representative 1955-09-19\n",
      "1     Nancy    Davolio      Sales Representative 1966-12-08\n",
      "2    Andrew     Fuller     Vice President, Sales 1970-02-19\n",
      "3    Steven   Buchanan             Sales Manager 1973-03-04\n",
      "4     Laura   Callahan  Inside Sales Coordinator 1976-01-09\n",
      "5    Robert       King      Sales Representative 1978-05-29\n",
      "6   Michael     Suyama      Sales Representative 1981-07-02\n",
      "7     Janet  Leverling      Sales Representative 1981-08-30\n",
      "8      Anne  Dodsworth      Sales Representative 1984-01-27\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT FirstName, LastName, Title, BirthDate\n",
    "    FROM Employees\n",
    "    ORDER BY BirthDate \n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Showing Only the Date with a DateTime Field"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName   LastName                     Title   BirthDate\n",
      "0  Margaret    Peacock      Sales Representative  1955-09-19\n",
      "1     Nancy    Davolio      Sales Representative  1966-12-08\n",
      "2    Andrew     Fuller     Vice President, Sales  1970-02-19\n",
      "3    Steven   Buchanan             Sales Manager  1973-03-04\n",
      "4     Laura   Callahan  Inside Sales Coordinator  1976-01-09\n",
      "5    Robert       King      Sales Representative  1978-05-29\n",
      "6   Michael     Suyama      Sales Representative  1981-07-02\n",
      "7     Janet  Leverling      Sales Representative  1981-08-30\n",
      "8      Anne  Dodsworth      Sales Representative  1984-01-27\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT FirstName, LastName, Title, CAST(BirthDate as DATE) AS BirthDate \n",
    "    FROM Employees\n",
    "    ORDER BY BirthDate\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Employees Full Name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName   LastName          FullName\n",
      "0     Nancy    Davolio     Nancy Davolio\n",
      "1    Andrew     Fuller     Andrew Fuller\n",
      "2     Janet  Leverling   Janet Leverling\n",
      "3  Margaret    Peacock  Margaret Peacock\n",
      "4    Steven   Buchanan   Steven Buchanan\n",
      "5   Michael     Suyama    Michael Suyama\n",
      "6    Robert       King       Robert King\n",
      "7     Laura   Callahan    Laura Callahan\n",
      "8      Anne  Dodsworth    Anne Dodsworth\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT FirstName,\n",
    "           LastName,\n",
    "           (FirstName + ' ' + LastName) AS FullName\n",
    "    FROM Employees\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Or, it could be done with CONCAT operator like below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  FirstName   LastName          FullName\n",
      "0     Nancy    Davolio     Nancy Davolio\n",
      "1    Andrew     Fuller     Andrew Fuller\n",
      "2     Janet  Leverling   Janet Leverling\n",
      "3  Margaret    Peacock  Margaret Peacock\n",
      "4    Steven   Buchanan   Steven Buchanan\n",
      "5   Michael     Suyama    Michael Suyama\n",
      "6    Robert       King       Robert King\n",
      "7     Laura   Callahan    Laura Callahan\n",
      "8      Anne  Dodsworth    Anne Dodsworth\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT FirstName,\n",
    "           LastName,\n",
    "           CONCAT(FirstName,' ',LastName) AS FullName\n",
    "    FROM Employees\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. OrderDetails Amount per Line Item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      OrderID  ProductID  UnitPrice  Quantity  TotalPrice\n",
      "0       10248         11      14.00        12       168.0\n",
      "1       10248         42       9.80        10        98.0\n",
      "2       10248         72      34.80         5       174.0\n",
      "3       10249         14      18.60         9       167.4\n",
      "4       10249         51      42.40        40      1696.0\n",
      "...       ...        ...        ...       ...         ...\n",
      "2150    11077         64      33.25         2        66.5\n",
      "2151    11077         66      17.00         1        17.0\n",
      "2152    11077         73      15.00         2        30.0\n",
      "2153    11077         75       7.75         4        31.0\n",
      "2154    11077         77      13.00         2        26.0\n",
      "\n",
      "[2155 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID,\n",
    "           ProductID,\n",
    "           UnitPrice,\n",
    "           Quantity,\n",
    "           UnitPrice * Quantity AS TotalPrice\n",
    "    FROM OrderDetails\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14. How Many Customers?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   TotalCustomers\n",
      "0              91\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT COUNT(*) AS TotalCustomers\n",
    "    FROM Customers\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15. When was the First Order?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           FirstOrder\n",
      "0 2014-07-04 08:00:00\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT MIN(OrderDate) AS FirstOrder\n",
    "    FROM Orders\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 16. Countries Where There are Customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Country\n",
      "0     Argentina\n",
      "1       Austria\n",
      "2       Belgium\n",
      "3        Brazil\n",
      "4        Canada\n",
      "5       Denmark\n",
      "6       Finland\n",
      "7        France\n",
      "8       Germany\n",
      "9       Ireland\n",
      "10        Italy\n",
      "11       Mexico\n",
      "12       Norway\n",
      "13       Poland\n",
      "14     Portugal\n",
      "15        Spain\n",
      "16       Sweden\n",
      "17  Switzerland\n",
      "18           UK\n",
      "19          USA\n",
      "20    Venezuela\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT DISTINCT(Country) AS Country\n",
    "    FROM Customers\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- It could be done using GROUP BY statement, as the hint says"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Country\n",
      "0     Argentina\n",
      "1       Austria\n",
      "2       Belgium\n",
      "3        Brazil\n",
      "4        Canada\n",
      "5       Denmark\n",
      "6       Finland\n",
      "7        France\n",
      "8       Germany\n",
      "9       Ireland\n",
      "10        Italy\n",
      "11       Mexico\n",
      "12       Norway\n",
      "13       Poland\n",
      "14     Portugal\n",
      "15        Spain\n",
      "16       Sweden\n",
      "17  Switzerland\n",
      "18           UK\n",
      "19          USA\n",
      "20    Venezuela\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT Country \n",
    "    FROM Customers\n",
    "    GROUP BY Country\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 17. Contact Titles for Customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                      ContactTitle  TotalContactTitle\n",
      "0                            Owner                 17\n",
      "1             Sales Representative                 17\n",
      "2                Marketing Manager                 12\n",
      "3                    Sales Manager                 11\n",
      "4               Accounting Manager                 10\n",
      "5                  Sales Associate                  7\n",
      "6              Marketing Assistant                  6\n",
      "7                      Sales Agent                  5\n",
      "8            Assistant Sales Agent                  2\n",
      "9              Order Administrator                  2\n",
      "10  Assistant Sales Representative                  1\n",
      "11       Owner/Marketing Assistant                  1\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT ContactTitle,\n",
    "           COUNT(*) AS TotalContactTitle\n",
    "    FROM Customers\n",
    "    GROUP BY ContactTitle\n",
    "    ORDER BY COUNT(*) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 18. Products with Associated Supplier Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    ProductID                      ProductName  \\\n",
      "0           1                             Chai   \n",
      "1           2                            Chang   \n",
      "2           3                    Aniseed Syrup   \n",
      "3           4     Chef Anton's Cajun Seasoning   \n",
      "4           5           Chef Anton's Gumbo Mix   \n",
      "..        ...                              ...   \n",
      "72         73                       Röd Kaviar   \n",
      "73         74                    Longlife Tofu   \n",
      "74         75             Rhönbräu Klosterbier   \n",
      "75         76                     Lakkalikööri   \n",
      "76         77  Original Frankfurter grüne Soße   \n",
      "\n",
      "                          CompanyName  \n",
      "0                      Exotic Liquids  \n",
      "1                      Exotic Liquids  \n",
      "2                      Exotic Liquids  \n",
      "3          New Orleans Cajun Delights  \n",
      "4          New Orleans Cajun Delights  \n",
      "..                                ...  \n",
      "72                  Svensk Sjöföda AB  \n",
      "73                      Tokyo Traders  \n",
      "74  Plutzer Lebensmittelgroßmärkte AG  \n",
      "75                          Karkki Oy  \n",
      "76  Plutzer Lebensmittelgroßmärkte AG  \n",
      "\n",
      "[77 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT ProductID, ProductName, CompanyName\n",
    "    FROM Products p\n",
    "    JOIN Suppliers s\n",
    "    ON p.SupplierID = s.SupplierID\n",
    "    ORDER BY ProductID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 19. Orders and the Shippers That was Used"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID   OrderDate           Shipper\n",
      "0     10248  2014-07-04  Federal Shipping\n",
      "1     10249  2014-07-05    Speedy Express\n",
      "2     10250  2014-07-08    United Package\n",
      "3     10251  2014-07-08    Speedy Express\n",
      "4     10252  2014-07-09    United Package\n",
      "5     10253  2014-07-10    United Package\n",
      "6     10254  2014-07-11    United Package\n",
      "7     10255  2014-07-12  Federal Shipping\n",
      "8     10256  2014-07-15    United Package\n",
      "9     10257  2014-07-16  Federal Shipping\n",
      "10    10258  2014-07-17    Speedy Express\n",
      "11    10259  2014-07-18  Federal Shipping\n",
      "12    10260  2014-07-19    Speedy Express\n",
      "13    10261  2014-07-19    United Package\n",
      "14    10262  2014-07-22  Federal Shipping\n",
      "15    10263  2014-07-23  Federal Shipping\n",
      "16    10264  2014-07-24  Federal Shipping\n",
      "17    10265  2014-07-25    Speedy Express\n",
      "18    10266  2014-07-26  Federal Shipping\n",
      "19    10267  2014-07-29    Speedy Express\n",
      "20    10268  2014-07-30  Federal Shipping\n",
      "21    10269  2014-07-31    Speedy Express\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID,\n",
    "           CAST(OrderDate AS DATE) AS OrderDate,\n",
    "           CompanyName AS Shipper\n",
    "    FROM Orders o\n",
    "    JOIN Shippers s\n",
    "    ON o.ShipVia = s.ShipperID\n",
    "    WHERE OrderID < 10270\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intermediate Problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 20. Categories, and the Total Products in each Category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     CategoryName  TotalProducts\n",
      "0     Confections             13\n",
      "1       Beverages             12\n",
      "2      Condiments             12\n",
      "3         Seafood             12\n",
      "4  Dairy Products             10\n",
      "5  Grains/Cereals              7\n",
      "6    Meat/Poultry              6\n",
      "7         Produce              5\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT CategoryName, Count(*) AS TotalProducts\n",
    "    FROM Products p\n",
    "    JOIN Categories c\n",
    "    ON p.CategoryID = c.CategoryID\n",
    "    GROUP BY CategoryName\n",
    "    ORDER BY Count(*) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 21. Total Customers per Country/City"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Country            City  TotalCustomers\n",
      "0            UK          London               6\n",
      "1        Mexico     México D.F.               5\n",
      "2        Brazil       Sao Paulo               4\n",
      "3        Brazil  Rio de Janeiro               3\n",
      "4         Spain          Madrid               3\n",
      "..          ...             ...             ...\n",
      "64  Switzerland            Bern               1\n",
      "65          USA           Boise               1\n",
      "66       Sweden          Bräcke               1\n",
      "67      Germany     Brandenburg               1\n",
      "68      Belgium       Bruxelles               1\n",
      "\n",
      "[69 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT Country, City, COUNT(*) AS TotalCustomers\n",
    "    FROM Customers\n",
    "    GROUP BY Country, City\n",
    "    ORDER BY TotalCustomers DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 22. Products That Need Reordering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    ProductID                ProductName  UnitsInStock  ReorderLevel\n",
      "0           2                      Chang            17            25\n",
      "1           3              Aniseed Syrup            13            25\n",
      "2           5     Chef Anton's Gumbo Mix             0             0\n",
      "3          11             Queso Cabrales            22            30\n",
      "4          17               Alice Mutton             0             0\n",
      "5          21        Sir Rodney's Scones             3             5\n",
      "6          29    Thüringer Rostbratwurst             0             0\n",
      "7          30      Nord-Ost Matjeshering            10            15\n",
      "8          31          Gorgonzola Telino             0            20\n",
      "9          32         Mascarpone Fabioli             9            25\n",
      "10         37                 Gravad lax            11            25\n",
      "11         43                Ipoh Coffee            17            25\n",
      "12         45                Rogede sild             5            15\n",
      "13         48                  Chocolade            15            25\n",
      "14         49                   Maxilaku            10            15\n",
      "15         53              Perth Pasties             0             0\n",
      "16         56     Gnocchi di nonna Alice            21            30\n",
      "17         64  Wimmers gute Semmelknödel            22            30\n",
      "18         66  Louisiana Hot Spiced Okra             4            20\n",
      "19         68        Scottish Longbreads             6            15\n",
      "20         70              Outback Lager            15            30\n",
      "21         74              Longlife Tofu             4             5\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT ProductID, ProductName, UnitsInStock, ReorderLevel\n",
    "    FROM Products\n",
    "    WHERE UnitsInStock <= ReorderLevel\n",
    "    ORDER BY ProductID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 23. Products That Need Reordering, Continued"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Product ID           Product Name  Units In Stock  Reorder Level  \\\n",
      "0          30  Nord-Ost Matjeshering              10             15   \n",
      "1          70          Outback Lager              15             30   \n",
      "\n",
      "   Discontinued  \n",
      "0         False  \n",
      "1         False  \n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT ProductID AS 'Product ID',\n",
    "           ProductName AS 'Product Name',\n",
    "           UnitsInStock AS 'Units In Stock',\n",
    "           ReorderLevel AS 'Reorder Level',\n",
    "           Discontinued\n",
    "    FROM Products\n",
    "    WHERE (UnitsInStock + UnitsOnOrder) <= ReorderLevel and \n",
    "          Discontinued = 0\n",
    "    ORDER BY ProductID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 24. Customer List by Region (RECAP!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID                    CompanyName    Region\n",
      "0       OLDWO         Old World Delicatessen        AK\n",
      "1       BOTTM          Bottom-Dollar Markets        BC\n",
      "2       LAUGB  Laughing Bacchus Wine Cellars        BC\n",
      "3       LETSS              Let's Stop N Shop        CA\n",
      "4       HUNGO   Hungry Owl All-Night Grocers  Co. Cork\n",
      "..        ...                            ...       ...\n",
      "86      VINET      Vins et alcools Chevalier      None\n",
      "87      WANDK              Die Wandernde Kuh      None\n",
      "88      WARTH                 Wartian Herkku      None\n",
      "89      WILMK                    Wilman Kala      None\n",
      "90      WOLZA                 Wolski  Zajazd      None\n",
      "\n",
      "[91 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT CustomerID,\n",
    "           CompanyName,\n",
    "           Region\n",
    "    FROM Customers\n",
    "    ORDER BY (CASE WHEN Region IS NOT null \n",
    "              THEN Region ELSE 'ZZ' END),\n",
    "              CustomerID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- CASE WHEN {condition} THEN {value1} ELSE {value2} END"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 25. High Freight Charges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ShipCountry  AverageFreight\n",
      "0     Austria        184.7875\n",
      "1     Ireland        145.0126\n",
      "2         USA        112.8794\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 3 ShipCountry,\n",
    "           AVG(Freight) AS AverageFreight\n",
    "    FROM Orders\n",
    "    GROUP BY ShipCountry\n",
    "    ORDER BY AverageFreight DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ShipCountry  AverageFreight\n",
      "0     Austria        184.7875\n",
      "1     Ireland        145.0126\n",
      "2         USA        112.8794\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT ShipCountry,\n",
    "           AVG(Freight) AS AverageFreight\n",
    "    FROM Orders\n",
    "    GROUP BY ShipCountry\n",
    "    ORDER BY AverageFreight DESC\n",
    "    OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- No LIMIT Clause in MSSQL\n",
    "- Use SELECT TOP or OFFSET {n} ROWS FETCH NEXT {n} ROWS ONLY"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 26. High Freight Charges - 2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ShipCountry  AverageFreight\n",
      "0      Austria        178.3642\n",
      "1  Switzerland        117.1775\n",
      "2       France        113.9910\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 3 ShipCountry,\n",
    "           AVG(Freight) AS AverageFreight\n",
    "    FROM Orders\n",
    "    WHERE YEAR(OrderDate) = 2015\n",
    "    GROUP BY ShipCountry\n",
    "    ORDER BY AverageFreight DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- No EXTRACT() function in SQL Server\n",
    "- Use YEAR() or DATEPART() instead."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 27. High Frieght Charges with Between"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   ShipCountry  AverageFreight\n",
      "0      Austria        178.3642\n",
      "1  Switzerland        117.1775\n",
      "2       France        113.9910\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 3 \n",
    "           ShipCountry,\n",
    "           AVG(Freight) AS AverageFreight\n",
    "    FROM ORDERS\n",
    "    WHERE OrderDate BETWEEN '20150101' AND '20160101'\n",
    "    GROUP BY ShipCountry\n",
    "    ORDER BY AverageFreight DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- Date 20151231 doesn't really include records from 2015-12-31. \n",
    "- OrderDate is DateTime type.\n",
    "- Date 20151231 sets upper limit date time to 2015-12-31 00:00:00, which excludes records with OrderDate in 20151231\n",
    "- If OrderDate were type of Date, 20151231 would have set upper limit as intended"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 28. High Freight Charges - Last Year (RECAP!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ShipCountry  AverageFreight\n",
      "0     Ireland        200.2100\n",
      "1     Austria        186.4596\n",
      "2         USA        119.3032\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 3\n",
    "           ShipCountry,\n",
    "           AVG(Freight) AS AverageFreight\n",
    "    FROM Orders\n",
    "    WHERE\n",
    "        OrderDate >= DATEADD(YEAR, -1, (SELECT MAX(Orderdate) FROM ORDERS))\n",
    "    GROUP BY ShipCountry\n",
    "    ORDER BY AverageFreight DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- DATEADD(DATEPART, number, DateTime)\n",
    "- This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                       \n",
      "0   2013-07-04 08:00:00\n",
      "1   2013-07-05 04:00:00\n",
      "2   2013-07-08 15:00:00\n",
      "3   2013-07-08 14:00:00\n",
      "4   2013-07-09 01:00:00\n",
      "..                  ...\n",
      "825 2015-05-05 12:00:00\n",
      "826 2015-05-06 01:00:00\n",
      "827 2015-05-06 18:00:00\n",
      "828 2015-05-06 00:00:00\n",
      "829 2015-05-06 17:00:00\n",
      "\n",
      "[830 rows x 1 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT DATEADD(YEAR, -1, OrderDate) FROM Orders\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 29. Employee/Order Detail Report"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      EmployeeID  LastName  OrderID                      ProductName  Quantity\n",
      "0              5  Buchanan    10248                   Queso Cabrales        12\n",
      "1              5  Buchanan    10248    Singaporean Hokkien Fried Mee        10\n",
      "2              5  Buchanan    10248           Mozzarella di Giovanni         5\n",
      "3              6    Suyama    10249                             Tofu         9\n",
      "4              6    Suyama    10249            Manjimup Dried Apples        40\n",
      "...          ...       ...      ...                              ...       ...\n",
      "2150           1   Davolio    11077        Wimmers gute Semmelknödel         2\n",
      "2151           1   Davolio    11077        Louisiana Hot Spiced Okra         1\n",
      "2152           1   Davolio    11077                       Röd Kaviar         2\n",
      "2153           1   Davolio    11077             Rhönbräu Klosterbier         4\n",
      "2154           1   Davolio    11077  Original Frankfurter grüne Soße         2\n",
      "\n",
      "[2155 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT\n",
    "        e.EmployeeID,\n",
    "        LastName,\n",
    "        od.OrderID,\n",
    "        p.ProductName,\n",
    "        Quantity\n",
    "    FROM OrderDetails od\n",
    "    JOIN Orders o\n",
    "    ON od.OrderID = o.OrderID\n",
    "    JOIN Products p\n",
    "    ON od.ProductID = p.ProductID\n",
    "    JOIN Employees e\n",
    "    ON o.EmployeeID = e.EmployeeID\n",
    "    ORDER BY od.OrderID, p.ProductID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 30. Customers with No Orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Customers_CustomerID Orders_CustomerID\n",
      "0                FISSA              None\n",
      "1                PARIS              None\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT c.CustomerID as Customers_CustomerID,\n",
    "           NULL as Orders_CustomerID\n",
    "    FROM Customers c\n",
    "    WHERE c.CustomerID NOT IN (\n",
    "        SELECT DISTINCT CustomerID FROM Orders\n",
    "    )\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Or, it could be done using left join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Customers_CustomerID Orders_CustomerID\n",
      "0                PARIS              None\n",
      "1                FISSA              None\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT c.CustomerID AS Customers_CustomerID,\n",
    "           o.CustomerID AS Orders_CustomerID\n",
    "    FROM Customers c\n",
    "    LEFT JOIN Orders o\n",
    "    ON c.CustomerID = o.CustomerID\n",
    "    WHERE o.CustomerID IS null\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 31. Customers with No Orders for EmployeeID 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID CustomerID\n",
      "0       CONSH       None\n",
      "1       DUMON       None\n",
      "2       FISSA       None\n",
      "3       FRANR       None\n",
      "4       GROSR       None\n",
      "5       LAUGB       None\n",
      "6       LAZYK       None\n",
      "7       NORTS       None\n",
      "8       PARIS       None\n",
      "9       PERIC       None\n",
      "10      PRINI       None\n",
      "11      SANTG       None\n",
      "12      SEVES       None\n",
      "13      SPECD       None\n",
      "14      THEBI       None\n",
      "15      VINET       None\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT c.CustomerID as CustomerID,\n",
    "           o.CustomerID as CustomerID\n",
    "    FROM Customers c\n",
    "    LEFT JOIN (\n",
    "        SELECT CustomerID FROM Orders\n",
    "        WHERE EmployeeID = 4\n",
    "    ) o\n",
    "    ON o.CustomerID = c.CustomerID\n",
    "    WHERE o.CustomerID IS null\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- With outer joins, the filters on the were clause are applied _after_ the join."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Advanced Problems"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 32. High-Value Customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  CustomerID                   CompanyName  OrderID  TotalOrderAmount\n",
      "0      QUICK                    QUICK-Stop    10865          17250.00\n",
      "1      SAVEA            Save-a-lot Markets    11030          16321.90\n",
      "2      HANAR                 Hanari Carnes    10981          15810.00\n",
      "3      KOENE               Königlich Essen    10817          11490.70\n",
      "4      RATTC    Rattlesnake Canyon Grocery    10889          11380.00\n",
      "5      HUNGO  Hungry Owl All-Night Grocers    10897          10835.24\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT c.CustomerID,\n",
    "           c.CompanyName,\n",
    "           o.OrderID,\n",
    "           SUM(od.UnitPrice * od.Quantity) AS TotalOrderAmount\n",
    "    FROM Orders o\n",
    "    JOIN OrderDetails od\n",
    "    ON o.OrderID = od.OrderID\n",
    "    JOIN Customers c\n",
    "    ON o.CustomerID = c.CustomerID\n",
    "    WHERE YEAR(o.OrderDate) = 2016\n",
    "    GROUP BY c.CustomerID, c.CompanyName, o.OrderID\n",
    "    HAVING SUM(od.UnitPrice * od.Quantity) >= 10000\n",
    "    ORDER BY SUM(od.UnitPrice * od.Quantity) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- Aggregate functions can only be used to filter(with some exceptions) in the HAVING clause, not the Where clause."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 33. High-Value Customers - Total Orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  CustomerID                   CompanyName  TotalOrderAmount\n",
      "0      SAVEA            Save-a-lot Markets          42806.25\n",
      "1      ERNSH                  Ernst Handel          42598.90\n",
      "2      QUICK                    QUICK-Stop          40526.99\n",
      "3      HANAR                 Hanari Carnes          24238.05\n",
      "4      HUNGO  Hungry Owl All-Night Grocers          22796.34\n",
      "5      RATTC    Rattlesnake Canyon Grocery          21725.60\n",
      "6      KOENE               Königlich Essen          20204.95\n",
      "7      FOLKO                Folk och fä HB          15973.85\n",
      "8      WHITC          White Clover Markets          15278.90\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT c.CustomerID,\n",
    "           c.CompanyName,\n",
    "           SUM(od.UnitPrice * od.Quantity) AS TotalOrderAmount\n",
    "    FROM Orders o \n",
    "    JOIN OrderDetails od\n",
    "    ON o.OrderID = od.OrderID\n",
    "    JOIN Customers c\n",
    "    ON o.CustomerID = c.CustomerID\n",
    "    WHERE YEAR(o.OrderDate) = 2016\n",
    "    GROUP BY c.CustomerID, c.CompanyName\n",
    "    HAVING SUM(od.UnitPrice * od.Quantity) >= 15000\n",
    "    ORDER BY SUM(od.UnitPrice * od.Quantity) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 34. High-Value Customers - with Discount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Let's first take a look how data are stored in Discount column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID  ProductID  UnitPrice  Quantity  Discount\n",
      "0    10250         51       42.4        35      0.15\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 1 * FROM OrderDetails WHERE Discount != 0\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- It seems Discount column represents 'rate of discount'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Customer ID                  Company Name  Totals Without Discount  \\\n",
      "0       ERNSH                  Ernst Handel                 42598.90   \n",
      "1       QUICK                    QUICK-Stop                 40526.99   \n",
      "2       SAVEA            Save-a-lot Markets                 42806.25   \n",
      "3       HANAR                 Hanari Carnes                 24238.05   \n",
      "4       RATTC    Rattlesnake Canyon Grocery                 21725.60   \n",
      "5       HUNGO  Hungry Owl All-Night Grocers                 22796.34   \n",
      "6       KOENE               Königlich Essen                 20204.95   \n",
      "7       WHITC          White Clover Markets                 15278.90   \n",
      "8       FOLKO                Folk och fä HB                 15973.85   \n",
      "\n",
      "   Totals With Discount  \n",
      "0          41210.650024  \n",
      "1          37217.315002  \n",
      "2          36310.109779  \n",
      "3          23821.199989  \n",
      "4          21238.270441  \n",
      "5          20402.119934  \n",
      "6          19582.773987  \n",
      "7          15278.899986  \n",
      "8          13644.067497  \n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT c.CustomerID AS 'Customer ID',\n",
    "           c.CompanyName AS 'Company Name',\n",
    "           SUM(od.UnitPrice * od.Quantity) AS 'Totals Without Discount',\n",
    "           SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS 'Totals With Discount'\n",
    "    FROM Orders o \n",
    "    JOIN OrderDetails od\n",
    "    ON o.OrderID = od.OrderID\n",
    "    JOIN Customers c\n",
    "    ON o.CustomerID = c.CustomerID\n",
    "    WHERE YEAR(o.OrderDate) = 2016\n",
    "    GROUP BY c.CustomerID, c.CompanyName\n",
    "    HAVING SUM(od.UnitPrice * od.Quantity) >= 15000\n",
    "    ORDER BY SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 35. Month-end Orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    EmployeeID  OrderID           OrderDate\n",
      "0            1    10461 2015-02-28 00:00:00\n",
      "1            1    10616 2015-07-31 00:00:00\n",
      "2            2    10583 2015-06-30 00:00:00\n",
      "3            2    10686 2015-09-30 00:00:00\n",
      "4            2    10989 2016-03-31 00:00:00\n",
      "5            2    11060 2016-04-30 00:00:00\n",
      "6            3    10432 2015-01-31 00:00:00\n",
      "7            3    10806 2015-12-31 11:00:00\n",
      "8            3    10988 2016-03-31 00:00:00\n",
      "9            3    11063 2016-04-30 00:00:00\n",
      "10           4    10343 2014-10-31 00:00:00\n",
      "11           4    10522 2015-04-30 00:00:00\n",
      "12           4    10584 2015-06-30 00:00:00\n",
      "13           4    10617 2015-07-31 00:00:00\n",
      "14           4    10725 2015-10-31 00:00:00\n",
      "15           4    10807 2015-12-31 11:00:00\n",
      "16           4    11061 2016-04-30 00:00:00\n",
      "17           4    11062 2016-04-30 00:00:00\n",
      "18           5    10269 2014-07-31 00:00:00\n",
      "19           6    10317 2014-09-30 00:00:00\n",
      "20           7    10490 2015-03-31 00:00:00\n",
      "21           8    10399 2014-12-31 00:00:00\n",
      "22           8    10460 2015-02-28 00:00:00\n",
      "23           8    10491 2015-03-31 00:00:00\n",
      "24           8    10987 2016-03-31 00:00:00\n",
      "25           9    10687 2015-09-30 00:00:00\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT EmployeeID, \n",
    "           OrderID,\n",
    "           OrderDate\n",
    "    FROM Orders\n",
    "    WHERE MONTH(OrderDate) != MONTH(DATEADD(DAY, 1, OrderDate))\n",
    "    ORDER BY EmployeeID, OrderID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- In SQL Server, I can use EOMONTH() as follows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    EmployeeID  OrderID  OrderDate\n",
      "0            1    10461 2015-02-28\n",
      "1            1    10616 2015-07-31\n",
      "2            2    10583 2015-06-30\n",
      "3            2    10686 2015-09-30\n",
      "4            2    10989 2016-03-31\n",
      "5            2    11060 2016-04-30\n",
      "6            3    10432 2015-01-31\n",
      "7            3    10988 2016-03-31\n",
      "8            3    11063 2016-04-30\n",
      "9            4    10343 2014-10-31\n",
      "10           4    10522 2015-04-30\n",
      "11           4    10584 2015-06-30\n",
      "12           4    10617 2015-07-31\n",
      "13           4    10725 2015-10-31\n",
      "14           4    11061 2016-04-30\n",
      "15           4    11062 2016-04-30\n",
      "16           5    10269 2014-07-31\n",
      "17           6    10317 2014-09-30\n",
      "18           7    10490 2015-03-31\n",
      "19           8    10399 2014-12-31\n",
      "20           8    10460 2015-02-28\n",
      "21           8    10491 2015-03-31\n",
      "22           8    10987 2016-03-31\n",
      "23           9    10687 2015-09-30\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT EmployeeID, \n",
    "           OrderID,\n",
    "           OrderDate\n",
    "    FROM Orders\n",
    "    WHERE EOMONTH(OrderDate) = OrderDate\n",
    "    ORDER BY EmployeeID, OrderID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 36. Orders with Many Line Items"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Let's first explore OrderDetails"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID  ProductID  UnitPrice  Quantity  Discount\n",
      "0    10324         16       13.9        21      0.15\n",
      "1    10324         35       14.4        70      0.15\n",
      "2    10324         46        9.6        30      0.00\n",
      "3    10324         59       44.0        40      0.15\n",
      "4    10324         63       35.1        80      0.15\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT * FROM OrderDetails\n",
    "    WHERE OrderID = 10324\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID  TotalOrderDetails\n",
      "0    11077                 25\n",
      "1    10657                  6\n",
      "2    10847                  6\n",
      "3    10979                  6\n",
      "4    10273                  5\n",
      "5    10294                  5\n",
      "6    10309                  5\n",
      "7    10324                  5\n",
      "8    10325                  5\n",
      "9    10337                  5\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 10 OrderID, COUNT(*) AS TotalOrderDetails FROM OrderDetails\n",
    "    GROUP BY OrderID\n",
    "    ORDER BY COUNT(*) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 37. Orders - Random Assortment "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID\n",
      "0     10734\n",
      "1     10911\n",
      "2     10374\n",
      "3     10776\n",
      "4     10833\n",
      "5     10449\n",
      "6     10534\n",
      "7     10272\n",
      "8     10375\n",
      "9     10447\n",
      "10    10936\n",
      "11    11065\n",
      "12    10264\n",
      "13    10395\n",
      "14    10523\n",
      "15    10545\n",
      "16    10832\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP (SELECT CAST(CEILING(COUNT(*) * 0.02) AS INT) FROM Orders)\n",
    "           OrderID\n",
    "    FROM Orders\n",
    "    ORDER BY NEWID()\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- https://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql Refer to this link to see different random function names for each SQL implementaion\n",
    "- Also, SQL Server syntax supports TOP n PERCENT."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID\n",
      "0     10451\n",
      "1     11034\n",
      "2     10946\n",
      "3     10821\n",
      "4     10937\n",
      "5     10572\n",
      "6     10682\n",
      "7     10977\n",
      "8     10270\n",
      "9     10615\n",
      "10    10290\n",
      "11    10554\n",
      "12    10539\n",
      "13    10929\n",
      "14    11022\n",
      "15    10903\n",
      "16    10435\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT TOP 2 PERCENT\n",
    "           OrderID\n",
    "    FROM Orders\n",
    "    ORDER BY NEWID()\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 38. Orders - Accidental Double-Entry (RECAP!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID\n",
      "0    10263\n",
      "1    10658\n",
      "2    10990\n",
      "3    11030\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT DISTINCT o.OrderID FROM OrderDetails o\n",
    "    JOIN OrderDetails oo\n",
    "    ON o.OrderID = oo.OrderID and o.Quantity = oo.Quantity and o.ProductID != oo.ProductID\n",
    "    WHERE o.Quantity >= 60\n",
    "    ORDER BY o.OrderID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 38. Solution Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID\n",
      "0    10263\n",
      "1    10263\n",
      "2    10658\n",
      "3    10990\n",
      "4    11030\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID FROM OrderDetails\n",
    "    WHERE Quantity >= 60\n",
    "    GROUP BY OrderID, Quantity\n",
    "    HAVING COUNT(*) > 1\n",
    "    ORDER BY OrderID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 39. Orders - Accidental Double-Entry Details"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   OrderID  ProductID  UnitPrice  Quantity  Discount\n",
      "0    10263         30       20.7        60      0.25\n",
      "1    10263         16       13.9        60      0.25\n",
      "2    10263         24        3.6        65      0.00\n",
      "3    10263         74        8.0        65      0.25\n",
      "4    10658         77       13.0        70      0.05\n",
      "5    10658         40       18.4        70      0.05\n",
      "6    10990         21       10.0        65      0.00\n",
      "7    10990         55       24.0        65      0.15\n",
      "8    11030          2       19.0       100      0.25\n",
      "9    11030         59       55.0       100      0.25\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT o.OrderID, o.ProductID, o.UnitPrice, o.Quantity, o.Discount FROM OrderDetails o\n",
    "    JOIN OrderDetails oo\n",
    "    ON o.OrderID = oo.OrderID and o.Quantity = oo.Quantity and o.ProductID != oo.ProductID\n",
    "    WHERE o.Quantity >= 60\n",
    "    ORDER BY o.OrderID, o.Quantity\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- The answer shows all line items by OrderIDs that include accidental double entry. \n",
    "- On the contrary, my solution shows only the line items that might be a accidental duplicate entry.\n",
    "- I do think my solution is better, but the solution introduces a concept that is new to me (CTE, Common Table Expression). \n",
    "    - CTE -> Named subquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID  ProductID  UnitPrice  Quantity  Discount\n",
      "0     10263         16      13.90        60      0.25\n",
      "1     10263         24       3.60        65      0.00\n",
      "2     10263         30      20.70        60      0.25\n",
      "3     10263         74       8.00        65      0.25\n",
      "4     10658         21      10.00        60      0.00\n",
      "5     10658         40      18.40        70      0.05\n",
      "6     10658         60      34.00        55      0.05\n",
      "7     10658         77      13.00        70      0.05\n",
      "8     10990         21      10.00        65      0.00\n",
      "9     10990         34      14.00        60      0.15\n",
      "10    10990         55      24.00        65      0.15\n",
      "11    10990         61      28.50        66      0.15\n",
      "12    11030          2      19.00       100      0.25\n",
      "13    11030          5      21.35        70      0.00\n",
      "14    11030         29     123.79        60      0.25\n",
      "15    11030         59      55.00       100      0.25\n"
     ]
    }
   ],
   "source": [
    "# Solution code with CTE\n",
    "sql(\"\"\"\n",
    ";WITH PotentialProblemOrders AS (\n",
    "    SELECT OrderID FROM OrderDetails\n",
    "    WHERE Quantity >= 60\n",
    "    GROUP BY OrderID, Quantity\n",
    "    HAVING COUNT(*) > 1\n",
    ")\n",
    "SELECT * FROM OrderDetails\n",
    "WHERE OrderID IN (SELECT OrderID FROM PotentialProblemOrders)\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 40. Orders - Accidental Double-Entry Details, Derived Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Same question as above, using derived table instead of CTE.\n",
    "- Derived table -> A derived table is a subquery nested within a FROM clause"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID  ProductID  UnitPrice  Quantity  Discount  OrderID\n",
      "0     10263         16      13.90        60      0.25    10263\n",
      "1     10263         24       3.60        65      0.00    10263\n",
      "2     10263         30      20.70        60      0.25    10263\n",
      "3     10263         74       8.00        65      0.25    10263\n",
      "4     10658         21      10.00        60      0.00    10658\n",
      "5     10658         40      18.40        70      0.05    10658\n",
      "6     10658         60      34.00        55      0.05    10658\n",
      "7     10658         77      13.00        70      0.05    10658\n",
      "8     10990         21      10.00        65      0.00    10990\n",
      "9     10990         34      14.00        60      0.15    10990\n",
      "10    10990         55      24.00        65      0.15    10990\n",
      "11    10990         61      28.50        66      0.15    10990\n",
      "12    11030          2      19.00       100      0.25    11030\n",
      "13    11030          5      21.35        70      0.00    11030\n",
      "14    11030         29     123.79        60      0.25    11030\n",
      "15    11030         59      55.00       100      0.25    11030\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT * FROM OrderDetails\n",
    "    JOIN (\n",
    "        SELECT DISTINCT OrderID FROM OrderDetails\n",
    "        WHERE Quantity >= 60\n",
    "        GROUP BY OrderID, Quantity\n",
    "        HAVING COUNT(*) > 1\n",
    "    ) PotentialProblemOrders\n",
    "    ON OrderDetails.OrderID = PotentialProblemOrders.OrderID\n",
    "    ORDER BY OrderDetails.OrderID, ProductID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 41. Late Orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    OrderID   OrderDate RequiredDate ShippedDate\n",
      "0     10264  2014-07-24   2014-08-21  2014-08-23\n",
      "1     10271  2014-08-01   2014-08-29  2014-08-30\n",
      "2     10280  2014-08-14   2014-09-11  2014-09-12\n",
      "3     10302  2014-09-10   2014-10-08  2014-10-09\n",
      "4     10309  2014-09-19   2014-10-17  2014-10-23\n",
      "5     10380  2014-12-12   2015-01-09  2015-01-16\n",
      "6     10423  2015-01-23   2015-02-06  2015-02-24\n",
      "7     10427  2015-01-27   2015-02-24  2015-03-03\n",
      "8     10433  2015-02-03   2015-03-03  2015-03-04\n",
      "9     10451  2015-02-19   2015-03-05  2015-03-12\n",
      "10    10483  2015-03-24   2015-04-21  2015-04-25\n",
      "11    10515  2015-04-23   2015-05-07  2015-05-23\n",
      "12    10523  2015-05-01   2015-05-29  2015-05-30\n",
      "13    10545  2015-05-22   2015-06-19  2015-06-26\n",
      "14    10578  2015-06-24   2015-07-22  2015-07-25\n",
      "15    10593  2015-07-09   2015-08-06  2015-08-13\n",
      "16    10596  2015-07-11   2015-08-08  2015-08-12\n",
      "17    10660  2015-09-08   2015-10-06  2015-10-15\n",
      "18    10663  2015-09-10   2015-09-24  2015-10-03\n",
      "19    10687  2015-09-30   2015-10-28  2015-10-30\n",
      "20    10705  2015-10-15   2015-11-12  2015-11-18\n",
      "21    10709  2015-10-17   2015-11-14  2015-11-20\n",
      "22    10726  2015-11-03   2015-11-17  2015-12-05\n",
      "23    10727  2015-11-03   2015-12-01  2015-12-05\n",
      "24    10749  2015-11-20   2015-12-18  2015-12-19\n",
      "25    10777  2015-12-15   2015-12-29  2016-01-21\n",
      "26    10779  2015-12-16   2016-01-13  2016-01-14\n",
      "27    10807  2015-12-31   2016-01-28  2016-01-30\n",
      "28    10816  2016-01-06   2016-02-03  2016-02-04\n",
      "29    10827  2016-01-12   2016-01-26  2016-02-06\n",
      "30    10828  2016-01-13   2016-01-27  2016-02-04\n",
      "31    10847  2016-01-22   2016-02-05  2016-02-10\n",
      "32    10924  2016-03-04   2016-04-01  2016-04-08\n",
      "33    10927  2016-03-05   2016-04-02  2016-04-08\n",
      "34    10960  2016-03-19   2016-04-02  2016-04-08\n",
      "35    10970  2016-03-24   2016-04-07  2016-04-24\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT OrderID,\n",
    "           CAST(OrderDate AS DATE) AS OrderDate,\n",
    "           CAST(RequiredDate AS DATE) AS RequiredDate,\n",
    "           CAST(ShippedDate AS DATE) AS ShippedDate\n",
    "    FROM Orders\n",
    "    WHERE CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE)\n",
    "    ORDER BY OrderID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 42. Late Orders - Which Employee?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Let's first practice how to use CTE, then I'll solve the problem in much easier way."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  TotalLateOrders\n",
      "0           4    Peacock               10\n",
      "1           3  Leverling                5\n",
      "2           7       King                4\n",
      "3           8   Callahan                4\n",
      "4           9  Dodsworth                4\n",
      "5           2     Fuller                4\n",
      "6           6     Suyama                3\n",
      "7           1    Davolio                2\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH LateOrders AS (\n",
    "    SELECT OrderID\n",
    "    FROM Orders\n",
    "    WHERE CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE)\n",
    ")\n",
    "\n",
    "SELECT o.EmployeeID, LastName, COUNT(*) AS TotalLateOrders FROM Orders o\n",
    "JOIN Employees e\n",
    "ON e.EmployeeID = o.EmployeeID\n",
    "WHERE o.OrderID IN (SELECT OrderID FROM LateOrders)\n",
    "GROUP BY o.EmployeeID, LastName\n",
    "ORDER BY COUNT(*) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  TotalLateOrders\n",
      "0           4    Peacock               10\n",
      "1           3  Leverling                5\n",
      "2           7       King                4\n",
      "3           8   Callahan                4\n",
      "4           9  Dodsworth                4\n",
      "5           2     Fuller                4\n",
      "6           6     Suyama                3\n",
      "7           1    Davolio                2\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT o.EmployeeID, LastName, COUNT(*) AS TotalLateOrders FROM Orders o\n",
    "    JOIN Employees e\n",
    "    ON e.EmployeeID = o.EmployeeID\n",
    "    WHERE CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE)\n",
    "    GROUP BY o.EmployeeID, LastName\n",
    "    ORDER BY COUNT(*) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 43. Late Orders vs. Total Orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  TotalOrders  LateOrders\n",
      "0           1    Davolio          123           2\n",
      "1           2     Fuller           96           4\n",
      "2           3  Leverling          127           5\n",
      "3           4    Peacock          156          10\n",
      "4           6     Suyama           67           3\n",
      "5           7       King           72           4\n",
      "6           8   Callahan          104           4\n",
      "7           9  Dodsworth           43           4\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH LateOrderCounts AS (\n",
    "    SELECT o.EmployeeID, LastName, COUNT(*) AS LateOrders FROM Orders o\n",
    "    JOIN Employees e\n",
    "    ON e.EmployeeID = o.EmployeeID\n",
    "    WHERE CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE)\n",
    "    GROUP BY o.EmployeeID, LastName \n",
    "),\n",
    "TotalOrders AS (\n",
    "    SELECT EmployeeID, COUNT(*) AS TotalOrders\n",
    "    FROM Orders \n",
    "    GROUP BY EmployeeID\n",
    ")\n",
    "\n",
    "SELECT DISTINCT o.EmployeeID, lo.LastName, total.TotalOrders, lo.LateOrders\n",
    "FROM Orders o\n",
    "JOIN LateOrderCounts lo\n",
    "ON o.EmployeeID = lo.EmployeeID\n",
    "JOIN TotalOrders total\n",
    "ON o.EmployeeID = total.EmployeeID\n",
    "ORDER BY o.EmployeeID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- Using multiple CTEs, separate each declration with comma as below.\n",
    "- WITH    cte1 AS\n",
    "        (\n",
    "        SELECT  1 AS id\n",
    "        ),\n",
    "        cte2 AS\n",
    "        (\n",
    "        SELECT  2 AS id\n",
    "        )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 44. Late Orders vs. Total Orders - Missing Employee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  TotalOrders  LateOrders\n",
      "0           1    Davolio          123         2.0\n",
      "1           2     Fuller           96         4.0\n",
      "2           3  Leverling          127         5.0\n",
      "3           4    Peacock          156        10.0\n",
      "4           5       None           42         NaN\n",
      "5           6     Suyama           67         3.0\n",
      "6           7       King           72         4.0\n",
      "7           8   Callahan          104         4.0\n",
      "8           9  Dodsworth           43         4.0\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH LateOrderCounts AS (\n",
    "    SELECT o.EmployeeID, LastName, COUNT(*) AS LateOrders FROM Orders o\n",
    "    JOIN Employees e\n",
    "    ON e.EmployeeID = o.EmployeeID\n",
    "    WHERE CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE)\n",
    "    GROUP BY o.EmployeeID, LastName \n",
    "),\n",
    "TotalOrders AS (\n",
    "    SELECT EmployeeID, COUNT(*) AS TotalOrders\n",
    "    FROM Orders \n",
    "    GROUP BY EmployeeID\n",
    ")\n",
    "\n",
    "SELECT DISTINCT o.EmployeeID, lo.LastName, total.TotalOrders, lo.LateOrders\n",
    "FROM Orders o\n",
    "LEFT JOIN LateOrderCounts lo\n",
    "ON o.EmployeeID = lo.EmployeeID\n",
    "JOIN TotalOrders total\n",
    "ON o.EmployeeID = total.EmployeeID\n",
    "ORDER BY o.EmployeeID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 45. Late Orders vs. Total Orders - Fix Null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Cause of the Null value was the use of LEFT JOIN clause.\n",
    "- Let's write a same query without using LEFT JOIN."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  AllOrders  LateOrders\n",
      "0           1    Davolio        123           2\n",
      "1           2     Fuller         96           4\n",
      "2           3  Leverling        127           5\n",
      "3           4    Peacock        156          10\n",
      "4           5   Buchanan         42           0\n",
      "5           6     Suyama         67           3\n",
      "6           7       King         72           4\n",
      "7           8   Callahan        104           4\n",
      "8           9  Dodsworth         43           4\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT o.EmployeeID,\n",
    "           LastName,\n",
    "           COUNT(*) AS AllOrders,\n",
    "           SUM(CASE WHEN CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE) THEN 1 ELSE 0 END) AS LateOrders\n",
    "    FROM Orders o\n",
    "    JOIN Employees e\n",
    "    ON o.EmployeeID = e.EmployeeID\n",
    "    GROUP BY o.EmployeeID, LastName\n",
    "    ORDER BY o.EmployeeID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 46. Late Orders vs. Total Orders - Percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  AllOrders  LateOrders  Percent Late Orders\n",
      "0           1    Davolio        123           2             0.016260\n",
      "1           2     Fuller         96           4             0.041666\n",
      "2           3  Leverling        127           5             0.039370\n",
      "3           4    Peacock        156          10             0.064102\n",
      "4           5   Buchanan         42           0             0.000000\n",
      "5           6     Suyama         67           3             0.044776\n",
      "6           7       King         72           4             0.055555\n",
      "7           8   Callahan        104           4             0.038461\n",
      "8           9  Dodsworth         43           4             0.093023\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT o.EmployeeID,\n",
    "           LastName,\n",
    "           COUNT(*) AS AllOrders,\n",
    "           SUM(CASE WHEN CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE) THEN 1 ELSE 0 END) AS LateOrders,\n",
    "           SUM(CASE WHEN CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE) THEN 1.0 ELSE 0.0 END) / COUNT(*) AS 'Percent Late Orders'\n",
    "    FROM Orders o\n",
    "    JOIN Employees e\n",
    "    ON o.EmployeeID = e.EmployeeID\n",
    "    GROUP BY o.EmployeeID, LastName\n",
    "    ORDER BY o.EmployeeID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 47. Late Orders vs. Total Orders - Fix Decimal"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   EmployeeID   LastName  AllOrders  LateOrders  Percent Late Orders\n",
      "0           1    Davolio        123           2                 0.02\n",
      "1           2     Fuller         96           4                 0.04\n",
      "2           3  Leverling        127           5                 0.04\n",
      "3           4    Peacock        156          10                 0.06\n",
      "4           5   Buchanan         42           0                 0.00\n",
      "5           6     Suyama         67           3                 0.04\n",
      "6           7       King         72           4                 0.06\n",
      "7           8   Callahan        104           4                 0.04\n",
      "8           9  Dodsworth         43           4                 0.09\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT o.EmployeeID,\n",
    "           LastName,\n",
    "           COUNT(*) AS AllOrders,\n",
    "           SUM(CASE WHEN CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE) THEN 1 ELSE 0 END) AS LateOrders,\n",
    "           ROUND(SUM(CASE WHEN CAST(ShippedDate AS DATE) > CAST(RequiredDate AS DATE) THEN 1.0 ELSE 0.0 END) / COUNT(*),2) AS 'Percent Late Orders'\n",
    "    FROM Orders o\n",
    "    JOIN Employees e\n",
    "    ON o.EmployeeID = e.EmployeeID\n",
    "    GROUP BY o.EmployeeID, LastName\n",
    "    ORDER BY o.EmployeeID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 48. Customer Grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID                        Company Name  Total Order Amount  \\\n",
      "0       ALFKI                 Alfreds Futterkiste         2250.499998   \n",
      "1       ANATR  Ana Trujillo Emparedados y helados          514.400002   \n",
      "2       ANTON             Antonio Moreno Taquería          660.000000   \n",
      "3       AROUT                     Around the Horn         5604.750000   \n",
      "4       BERGS                  Berglunds snabbköp         6754.162476   \n",
      "..        ...                                 ...                 ...   \n",
      "76      WARTH                      Wartian Herkku          270.000000   \n",
      "77      WELLI              Wellington Importadora         1135.250000   \n",
      "78      WHITC                White Clover Markets        15278.899986   \n",
      "79      WILMK                         Wilman Kala         1987.000008   \n",
      "80      WOLZA                      Wolski  Zajazd         1865.099976   \n",
      "\n",
      "   Customer Group  \n",
      "0          Medium  \n",
      "1             Low  \n",
      "2             Low  \n",
      "3            High  \n",
      "4            High  \n",
      "..            ...  \n",
      "76            Low  \n",
      "77         Medium  \n",
      "78      Very High  \n",
      "79         Medium  \n",
      "80         Medium  \n",
      "\n",
      "[81 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH TotalAmount AS (\n",
    "    SELECT o.CustomerID,\n",
    "           SUM(od.UnitPrice * od.Quantity * (1-od.Discount)) AS Total\n",
    "    FROM Orders o\n",
    "    JOIN OrderDetails od\n",
    "    ON o.OrderId = od.OrderID\n",
    "    WHERE YEAR(o.OrderDate) = 2016\n",
    "    GROUP BY o.CustomerID\n",
    ")\n",
    "\n",
    "SELECT t.CustomerID,\n",
    "       c.CompanyName AS 'Company Name',\n",
    "       t.Total AS 'Total Order Amount',\n",
    "       (CASE WHEN t.Total BETWEEN 0 AND 1000 THEN 'Low'\n",
    "             WHEN t.Total BETWEEN 1001 AND 5000 THEN 'Medium'\n",
    "             WHEN t.Total BETWEEN 5001 AND 10000 THEN 'High'\n",
    "             ELSE 'Very High'\n",
    "        END) AS 'Customer Group'\n",
    "FROM TotalAmount t\n",
    "JOIN Customers c\n",
    "ON t.CustomerID = c.CustomerID\n",
    "ORDER BY t.CustomerID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 49. Customer Grouping - Fix Null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  CustomerGroup\n",
      "0          High\n",
      "1           Low\n",
      "2        Medium\n",
      "3     Very High\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH TotalAmount AS (\n",
    "    SELECT o.CustomerID,\n",
    "           SUM(od.UnitPrice * od.Quantity * (1-od.Discount)) AS Total\n",
    "    FROM Orders o\n",
    "    JOIN OrderDetails od\n",
    "    ON o.OrderId = od.OrderID\n",
    "    WHERE YEAR(o.OrderDate) = 2016\n",
    "    GROUP BY o.CustomerID\n",
    "),\n",
    "CustomerGroupTable AS(\n",
    "SELECT t.CustomerID,\n",
    "       c.CompanyName AS 'Company Name',\n",
    "       t.Total AS 'Total Order Amount',\n",
    "       (CASE WHEN t.Total BETWEEN 0 AND 1000 THEN 'Low'\n",
    "             WHEN t.Total BETWEEN 1001 AND 5000 THEN 'Medium'\n",
    "             WHEN t.Total BETWEEN 5001 AND 10000 THEN 'High'\n",
    "             ELSE 'Very High'\n",
    "        END) AS CustomerGroup\n",
    "FROM TotalAmount t\n",
    "JOIN Customers c\n",
    "ON t.CustomerID = c.CustomerID)\n",
    "\n",
    "SELECT DISTINCT CustomerGroup FROM CustomerGroupTable\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- It looks like there's no null value in the CustomerGroup column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 50. Customer Grouping with Percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  CustomerGroup  TotalInGroup  PercentageInGroup\n",
      "0        Medium            37           0.456790\n",
      "1           Low            20           0.246914\n",
      "2          High            13           0.160494\n",
      "3     Very High            11           0.135802\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH TotalAmount AS (\n",
    "    SELECT o.CustomerID,\n",
    "           SUM(od.UnitPrice * od.Quantity * (1-od.Discount)) AS Total\n",
    "    FROM Orders o\n",
    "    JOIN OrderDetails od\n",
    "    ON o.OrderId = od.OrderID\n",
    "    WHERE YEAR(o.OrderDate) = 2016\n",
    "    GROUP BY o.CustomerID\n",
    "),\n",
    "CustomerGroupTable AS(\n",
    "SELECT t.CustomerID,\n",
    "       c.CompanyName AS 'Company Name',\n",
    "       t.Total AS 'Total Order Amount',\n",
    "       (CASE WHEN t.Total >= 0 AND t.Total < 1000 THEN 'Low'\n",
    "             WHEN t.Total >= 1000 AND t.Total < 5000 THEN 'Medium'\n",
    "             WHEN t.Total >= 5000 AND t.Total < 10000 THEN 'High'\n",
    "             ELSE 'Very High'\n",
    "        END) AS CustomerGroup\n",
    "FROM TotalAmount t\n",
    "JOIN Customers c\n",
    "ON t.CustomerID = c.CustomerID)\n",
    "\n",
    "SELECT CustomerGroup,\n",
    "       COUNT(*) AS TotalInGroup,\n",
    "       CAST(COUNT(*) AS DECIMAL) / (SELECT COUNT(*) FROM CustomerGroupTable) AS PercentageInGroup\n",
    "FROM CustomerGroupTable\n",
    "GROUP BY CustomerGroup\n",
    "ORDER BY COUNT(*) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 51. Customer Grouping - Flexible"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Now, let's do the same thing (customer grouping) with CustomerGroupThresholds table, which defines customer groups and corresponding group ranges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  CustomerGroupName  RangeBottom      RangeTop\n",
      "0               Low          0.0  9.999999e+02\n",
      "1            Medium       1000.0  5.000000e+03\n",
      "2              High       5000.0  1.000000e+04\n",
      "3         Very High      10000.0  9.223372e+14\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"SELECT * FROM CustomerGroupThresholds\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID                         CompanyName    TotalOrder  \\\n",
      "0       ALFKI                 Alfreds Futterkiste   2250.499998   \n",
      "1       ANATR  Ana Trujillo Emparedados y helados    514.400002   \n",
      "2       ANTON             Antonio Moreno Taquería    660.000000   \n",
      "3       AROUT                     Around the Horn   5604.750000   \n",
      "4       BERGS                  Berglunds snabbköp   6754.162476   \n",
      "..        ...                                 ...           ...   \n",
      "76      WARTH                      Wartian Herkku    270.000000   \n",
      "77      WELLI              Wellington Importadora   1135.250000   \n",
      "78      WHITC                White Clover Markets  15278.899986   \n",
      "79      WILMK                         Wilman Kala   1987.000008   \n",
      "80      WOLZA                      Wolski  Zajazd   1865.099976   \n",
      "\n",
      "   CustomerGroupName  \n",
      "0             Medium  \n",
      "1                Low  \n",
      "2                Low  \n",
      "3               High  \n",
      "4               High  \n",
      "..               ...  \n",
      "76               Low  \n",
      "77            Medium  \n",
      "78         Very High  \n",
      "79            Medium  \n",
      "80            Medium  \n",
      "\n",
      "[81 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH TotalOrderAmounts AS (\n",
    "    SELECT o.CustomerID,\n",
    "       c.CompanyName,\n",
    "       SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalOrder\n",
    "    FROM Orders o\n",
    "    JOIN OrderDetails od \n",
    "    ON o.OrderID = od.OrderID\n",
    "    JOIN Customers c\n",
    "    ON o.CustomerID = c.CustomerID\n",
    "    WHERE YEAR(OrderDate) = 2016\n",
    "    GROUp BY o.CustomerID, c.CompanyName\n",
    ")\n",
    "\n",
    "SELECT CustomerID,\n",
    "       CompanyName,\n",
    "       TotalOrder,\n",
    "       CustomerGroupName\n",
    "FROM TotalOrderAmounts\n",
    "JOIN CustomerGroupThresholds\n",
    "ON TotalOrder BETWEEN RangeBottom AND RangeTop\n",
    "ORDER BY CustomerID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 52. Countries with Suppliers or Customers "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Country\n",
      "0     Argentina\n",
      "1     Australia\n",
      "2       Austria\n",
      "3       Belgium\n",
      "4        Brazil\n",
      "5        Canada\n",
      "6       Denmark\n",
      "7       Finland\n",
      "8        France\n",
      "9       Germany\n",
      "10      Ireland\n",
      "11        Italy\n",
      "12        Japan\n",
      "13       Mexico\n",
      "14  Netherlands\n",
      "15       Norway\n",
      "16       Poland\n",
      "17     Portugal\n",
      "18    Singapore\n",
      "19        Spain\n",
      "20       Sweden\n",
      "21  Switzerland\n",
      "22           UK\n",
      "23          USA\n",
      "24    Venezuela\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "SELECT DISTINCT Country FROM Suppliers \n",
    "UNION \n",
    "SELECT DISTINCT Country FROM Customers\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 53. Countries with Suppliers or Customers, Version 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   SupplierCountry CustomerCountry\n",
      "0             None       Argentina\n",
      "1             None         Austria\n",
      "2             None         Belgium\n",
      "3             None         Ireland\n",
      "4             None          Mexico\n",
      "5             None          Poland\n",
      "6             None        Portugal\n",
      "7             None     Switzerland\n",
      "8             None       Venezuela\n",
      "9        Australia            None\n",
      "10          Brazil          Brazil\n",
      "11          Canada          Canada\n",
      "12         Denmark         Denmark\n",
      "13         Finland         Finland\n",
      "14          France          France\n",
      "15         Germany         Germany\n",
      "16           Italy           Italy\n",
      "17           Japan            None\n",
      "18     Netherlands            None\n",
      "19          Norway          Norway\n",
      "20       Singapore            None\n",
      "21           Spain           Spain\n",
      "22          Sweden          Sweden\n",
      "23              UK              UK\n",
      "24             USA             USA\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "SELECT DISTINCT s.Country AS SupplierCountry,\n",
    "                c.Country AS CustomerCountry \n",
    "FROM Suppliers s\n",
    "FULL OUTER JOIN\n",
    "(SELECT DISTINCT cs.Country From Customers cs) AS c \n",
    "ON s.Country = c.Country\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 54. Countries with Suppliers or Customers, Version 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Country  TotalSuppliers  TotalCustomers\n",
      "0     Argentina               0               3\n",
      "1     Australia               2               0\n",
      "2       Austria               0               2\n",
      "3       Belgium               0               2\n",
      "4        Brazil               1               9\n",
      "5        Canada               2               3\n",
      "6       Denmark               1               2\n",
      "7       Finland               1               2\n",
      "8        France               3              11\n",
      "9       Germany               3              11\n",
      "10      Ireland               0               1\n",
      "11        Italy               2               3\n",
      "12        Japan               2               0\n",
      "13       Mexico               0               5\n",
      "14  Netherlands               1               0\n",
      "15       Norway               1               1\n",
      "16       Poland               0               1\n",
      "17     Portugal               0               2\n",
      "18    Singapore               1               0\n",
      "19        Spain               1               5\n",
      "20       Sweden               2               2\n",
      "21  Switzerland               0               2\n",
      "22           UK               2               7\n",
      "23          USA               4              13\n",
      "24    Venezuela               0               4\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH SupplierCounts AS (\n",
    "    SELECT Country, COUNT(*) AS TotalSuppliers\n",
    "    FROM Suppliers\n",
    "    GROUP BY Country\n",
    "),\n",
    "CustomerCounts AS (\n",
    "    SELECT Country, COUNT(*) AS TotalCustomers\n",
    "    FROM Customers\n",
    "    GROUP BY Country\n",
    ")\n",
    "\n",
    "SELECT CASE WHEN s.Country IS NULL THEN c.Country ELSE s.Country END AS Country,\n",
    "       CASE WHEN s.TotalSuppliers IS NULL THEN 0 ELSE CAST(s.TotalSuppliers AS Integer) END AS TotalSuppliers,\n",
    "       CASE WHEN c.TotalCustomers IS NULL THEN 0 ELSE CAST(c.TotalCustomers AS Integer) END AS TotalCustomers\n",
    "FROM SupplierCounts s\n",
    "FULL OUTER JOIN CustomerCounts c\n",
    "ON s.Country = c.Country\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- In MS-SQL, CASE WHEN {} IS NULL THEN {} ELSE {} END can be replaced by ISNULL({Value for Null Check}, {Alternative Value})\n",
    "- Postgres version is COALESCE()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 55. First Order in Each Country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    ShipCountry CustomerID  OrderID   OrderDate\n",
      "0     Argentina      OCEAN    10409  2015-01-09\n",
      "1       Austria      ERNSH    10258  2014-07-17\n",
      "2       Belgium      SUPRD    10252  2014-07-09\n",
      "3        Brazil      HANAR    10250  2014-07-08\n",
      "4        Canada      MEREP    10332  2014-10-17\n",
      "5       Denmark      SIMOB    10341  2014-10-29\n",
      "6       Finland      WARTH    10266  2014-07-26\n",
      "7        France      VINET    10248  2014-07-04\n",
      "8       Germany      TOMSP    10249  2014-07-05\n",
      "9       Ireland      HUNGO    10298  2014-09-05\n",
      "10        Italy      MAGAA    10275  2014-08-07\n",
      "11       Mexico      CENTC    10259  2014-07-18\n",
      "12       Norway      SANTG    10387  2014-12-18\n",
      "13       Poland      WOLZA    10374  2014-12-05\n",
      "14     Portugal      FURIB    10328  2014-10-14\n",
      "15        Spain      ROMEY    10281  2014-08-14\n",
      "16       Sweden      FOLKO    10264  2014-07-24\n",
      "17  Switzerland      CHOPS    10254  2014-07-11\n",
      "18           UK      BSBEV    10289  2014-08-26\n",
      "19          USA      RATTC    10262  2014-07-22\n",
      "20    Venezuela      HILAA    10257  2014-07-16\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH FirstOrders AS (\n",
    "    SELECT ShipCountry, MIN(OrderDate) AS FirstOrder\n",
    "    FROM Orders\n",
    "    GROUP BY ShipCountry\n",
    ")\n",
    "\n",
    "SELECT o.ShipCountry, o.CustomerID, o.OrderID, CAST(o.OrderDate AS DATE) AS OrderDate\n",
    "FROM Orders o \n",
    "JOIN FirstOrders f\n",
    "ON f.ShipCountry = o.ShipCountry AND f.FirstOrder = o.OrderDate\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 56. Customers with Multiple Orders in 5 Day Period (Solution without Window Function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID  InitialOrderID InitialOrderDate  NextOrderID NextOrderDate  \\\n",
      "0       ANTON           10677       2015-09-22        10682    2015-09-25   \n",
      "1       AROUT           10741       2015-11-14        10743    2015-11-17   \n",
      "2       BERGS           10278       2014-08-12        10280    2014-08-14   \n",
      "3       BERGS           10444       2015-02-12        10445    2015-02-13   \n",
      "4       BERGS           10866       2016-02-03        10875    2016-02-06   \n",
      "..        ...             ...              ...          ...           ...   \n",
      "64      WARTH           10412       2015-01-13        10416    2015-01-16   \n",
      "65      WELLI           10803       2015-12-30        10809    2016-01-01   \n",
      "66      WELLI           10900       2016-02-20        10905    2016-02-24   \n",
      "67      WHITC           10693       2015-10-06        10696    2015-10-08   \n",
      "68      WILMK           10873       2016-02-06        10879    2016-02-10   \n",
      "\n",
      "    DaysBetweenOrders  \n",
      "0                   3  \n",
      "1                   3  \n",
      "2                   2  \n",
      "3                   1  \n",
      "4                   3  \n",
      "..                ...  \n",
      "64                  3  \n",
      "65                  2  \n",
      "66                  4  \n",
      "67                  2  \n",
      "68                  4  \n",
      "\n",
      "[69 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "    SELECT f.CustomerID,\n",
    "           f.OrderID AS InitialOrderID,\n",
    "           CAST(f.OrderDate AS DATE) AS InitialOrderDate,\n",
    "           s.OrderID AS NextOrderID,\n",
    "           CAST(s.OrderDate AS DATE) AS NextOrderDate,\n",
    "           DATEDIFF(DAY , f.OrderDate , s.OrderDate ) AS DaysBetweenOrders\n",
    "    FROM Orders f\n",
    "    JOIN Orders s\n",
    "    ON f.OrderID != s.OrderID AND f.CustomerID = s.CustomerID AND f.OrderDate < s.OrderDate\n",
    "    WHERE DATEDIFF (DAY , f.OrderDate , s.OrderDate )  <= 5\n",
    "    ORDER BY CustomerID\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 57. Customers with Multiple Orders in 5 Day Period, Version 2 (Solution with Window Function)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID   OrderDate    NextDate  DaysBetween\n",
      "0       ANTON  2015-09-22  2015-09-25            3\n",
      "1       AROUT  2015-11-14  2015-11-17            3\n",
      "2       BERGS  2014-08-12  2014-08-14            2\n",
      "3       BERGS  2015-02-12  2015-02-13            1\n",
      "4       BERGS  2016-02-03  2016-02-06            3\n",
      "..        ...         ...         ...          ...\n",
      "64      WARTH  2015-01-13  2015-01-16            3\n",
      "65      WELLI  2015-12-30  2016-01-01            2\n",
      "66      WELLI  2016-02-20  2016-02-24            4\n",
      "67      WHITC  2015-10-06  2015-10-08            2\n",
      "68      WILMK  2016-02-06  2016-02-10            4\n",
      "\n",
      "[69 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "sql(\"\"\"\n",
    "WITH NextOrderDates AS(\n",
    "    SELECT CustomerID, OrderDate, \n",
    "           LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS NextDate,\n",
    "           DATEDIFF(DAY, OrderDate, LEAD(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate)) AS DaysBetween\n",
    "    FROM Orders\n",
    ")\n",
    "\n",
    "SELECT CustomerID,\n",
    "       CAST(OrderDate AS DATE) AS OrderDate,\n",
    "       CAST(NextDate AS DATE) AS NextDate,\n",
    "       DaysBetween\n",
    "       FROM NextOrderDates\n",
    "WHERE DaysBetween <= 5\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Note\n",
    "- LAG() -> Selects a row behind the current row \n",
    "- LEAD() -> Selects a row after the current row\n",
    "- [Reference](https://docs.microsoft.com/ko-kr/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:py37] *",
   "language": "python",
   "name": "conda-env-py37-py"
  },
  "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.7.4"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}