{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "376c57d5-8c9f-4c51-a27d-9977c73070a5",
   "metadata": {},
   "source": [
    "# Checkpoint #2 Exam Solutions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e7b91b5-3075-4aed-9c97-1e0439c6895b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from data201 import db_connection, df_query\n",
    "conn = db_connection('northwind.ini')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f1710ba-be67-4d99-a147-95a50594332a",
   "metadata": {},
   "source": [
    "### **PROBLEM 1**\n",
    "#### [5 points] For each product, display the product ID and product name, and the product's supplier ID and supplier name. Sort the results by product ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4dfa9af8-b3d5-476e-838e-7f847f364425",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT product_id AS 'Product ID', \n",
    "           product_name AS 'Product name', \n",
    "           supplier_id AS 'Supplier ID', \n",
    "           company_name AS 'Supplier name'\n",
    "    FROM products\n",
    "    JOIN suppliers USING (supplier_id)\n",
    "    ORDER BY product_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a27719ee-ea39-49d0-919c-967001c60d3e",
   "metadata": {},
   "source": [
    "### **PROBLEM 2**\n",
    "#### [10 points] Display the countries that had both customers and suppliers. Sort the results by country."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54ee43e1-6929-4208-b721-67558b709fda",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT c.country\n",
    "    FROM customers c\n",
    "    JOIN suppliers s USING (country)\n",
    "    ORDER BY c.country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88e828d9-5097-49fd-ba69-8e8db2a6dced",
   "metadata": {},
   "source": [
    "#### Another solution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad1d849e-3dcf-48f0-80c9-ff665df5bae4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT country\n",
    "    FROM customers\n",
    "    WHERE country IN (\n",
    "    \t\tSELECT country\n",
    "    \t\tFROM suppliers\n",
    "        )\n",
    "    ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fde7bc16-8cf1-47c3-960f-c0bdb87f2ce8",
   "metadata": {},
   "source": [
    "#### Newer versions of MySQL have the `INTERSECT` operator. But this solution is the least efficient:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "576af17c-8538-4c71-abfe-6c6defe86d10",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT country FROM customers\n",
    "    INTERSECT\n",
    "    SELECT country FROM suppliers\n",
    "    ORDER BY country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a006afe1-cc81-4e3f-87c0-b392e6749b13",
   "metadata": {},
   "source": [
    "### **PROBLEM 3**\n",
    "#### [10 points] A product needs to be reordered if it has not been discontinued and the units in stock plus the units on order is less than or equal to the reorder level.\n",
    "\n",
    "#### For each product that needs to be reordered, display the product's ID, name, the numbers of units in stock, units in order, and the reorder level. In the last column of your results, display 'yes' if the product has been discontinued (`products.discontinued` = 1) or 'no' if not (`products.discontinued` = 0). Sort the results by product ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6a27b89-f0c2-48dd-a049-1cc56314b1b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT product_id AS 'Product ID', \n",
    "           product_name AS 'Product name', \n",
    "           units_in_stock AS 'Units in stock', \n",
    "           units_on_order AS 'Units on order',\n",
    "           reorder_level AS 'Reorder level', \n",
    "           IF(discontinued = 0, 'no', 'yes') AS 'Discontinued?'\n",
    "    FROM products\n",
    "    WHERE units_in_stock + units_on_order <= reorder_level\n",
    "      AND discontinued = 0\n",
    "    ORDER BY product_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc9400ee-8d16-4d28-9a90-ad09bb6482ce",
   "metadata": {},
   "source": [
    "### **PROBLEM 4**\n",
    "#### [15 points] Display the IDs of the customers who have never placed an order with employee #4. Sort the results by customer ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2d148a08-84ff-4985-93d1-28dd753a09dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT c.customer_id AS 'Customer ID'\n",
    "    FROM customers c\n",
    "    LEFT OUTER JOIN orders o\n",
    "                 ON o.customer_id = c.customer_id\n",
    "    \t\t\tAND o.employee_id = 4\n",
    "    WHERE o.customer_id IS NULL\n",
    "    ORDER BY c.customer_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41fe72a8-9fb9-4d3d-9600-29e930d565ba",
   "metadata": {},
   "source": [
    "#### If you don't like to do outer joins, here's a less efficient solution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a39e398-a321-423e-bd35-8829c74d9223",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "    \tcustomers_of_4 AS\n",
    "        (\n",
    "    \t\tSELECT customer_id\n",
    "    \t\tFROM orders\n",
    "    \t\tWHERE employee_id = 4\n",
    "        )\n",
    "    SELECT DISTINCT customer_id AS 'Customer ID'\n",
    "    FROM customers\n",
    "    WHERE customer_id NOT IN (\n",
    "                    SELECT * FROM customers_of_4\n",
    "                )\n",
    "    ORDER BY customer_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cddfb7f6-affa-4d24-a564-5b489777b411",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Why does this fail?\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT c.customer_id AS 'Customer ID'\n",
    "    FROM customers c\n",
    "    LEFT OUTER JOIN orders o USING (customer_id)\n",
    "    WHERE o.employee_id = 4\n",
    "      AND o.customer_id IS NULL\n",
    "    ORDER BY c.customer_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9905e59e-0fc7-4cf8-b931-e8ce00b9aada",
   "metadata": {},
   "source": [
    "### **PROBLEM 5**\n",
    "#### [15 points] Each month has a last day during which there was at least one order. For each month and its last day, display the order date, the ID of the employee who handled the order, and the order ID for each order made during that day. Sort the results by order date, employee ID, and order ID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d53f900-2d25-41c8-963c-227eb8f80068",
   "metadata": {},
   "outputs": [],
   "source": [
    "# What are the last order dates for each month?\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT MAX(order_date) AS last_order_date_of_month\n",
    "    FROM orders\n",
    "    GROUP BY YEAR(order_date), MONTH(order_date)\n",
    "    \"\"\"\n",
    ")     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be1a5093-3abb-4255-8f7e-75564d49c907",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the previous query a CTE.\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        month_end_dates AS\n",
    "        (\n",
    "    \t\tSELECT MAX(order_date) AS last_order_date_of_month\n",
    "    \t\tFROM orders\n",
    "    \t\tGROUP BY YEAR(order_date), MONTH(order_date)\n",
    "    \t)\n",
    "    SELECT order_date AS 'Last order date', \n",
    "           employee_id AS 'Employee ID', \n",
    "           order_id AS 'Order ID'\n",
    "    FROM orders o\n",
    "    JOIN month_end_dates med \n",
    "      ON order_date = last_order_date_of_month\n",
    "    ORDER BY o.order_date, employee_id, order_id\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2a37cb5-6de3-469e-8c2f-ce15fffa11df",
   "metadata": {},
   "source": [
    "### **PROBLEM 6**\n",
    "#### [15 points] An order can include multiple products. For those orders that were among the orders that had the top two product counts, display the order ID and the product count. Sort the results by product count."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22013743-dbc5-4db7-b188-5fc3b7ab8062",
   "metadata": {},
   "outputs": [],
   "source": [
    "# What are the top two product counts?\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT COUNT(*)\n",
    "    FROM order_details\n",
    "    GROUP BY order_id\n",
    "    ORDER BY COUNT(*) DESC\n",
    "    LIMIT 2\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "55ac4cca-d36b-423e-a70c-30f9f3b342a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the previous query a CTE.\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        top_2_counts AS\n",
    "        (\n",
    "    \t\tSELECT DISTINCT COUNT(*)\n",
    "    \t\tFROM order_details\n",
    "    \t\tGROUP BY order_id\n",
    "            ORDER BY COUNT(*) DESC\n",
    "    \t\tLIMIT 2\n",
    "    \t)\n",
    "    SELECT order_id AS 'Order ID',\n",
    "           COUNT(*) AS 'Product count'\n",
    "    FROM order_details od\n",
    "    GROUP BY order_id\n",
    "    HAVING COUNT(*) IN (SELECT * FROM top_2_counts)\n",
    "    ORDER BY COUNT(*) DESC\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24ab5334-774d-4634-aa66-4b346f84ab11",
   "metadata": {},
   "source": [
    "### **PROBLEM 7**\n",
    "#### [15 points] For each country and the first order placed by that country, display the country name and the customer ID, order ID, and order date. Sort the results by country name."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d34a9780-5dbe-4f33-aa6d-093e00ee7751",
   "metadata": {},
   "outputs": [],
   "source": [
    "# What's the first order placed by each country?\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT ship_country, \n",
    "           MIN(order_id) AS first_order_id\n",
    "    FROM orders\n",
    "    GROUP BY ship_country\n",
    "    ORDER BY ship_country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c62d94e6-ac8f-4e50-ad69-1d17da243ff8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the previous query a CTE.\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    WITH\n",
    "        country_first_order AS\n",
    "        (\n",
    "    \t\tSELECT ship_country, \n",
    "                   MIN(order_id) AS first_order_id\n",
    "    \t\tFROM orders\n",
    "            GROUP BY ship_country\n",
    "        )\n",
    "    SELECT o.ship_country AS 'Ship Country',\n",
    "           customer_id AS 'Customer ID',\n",
    "           order_id AS 'First order ID',\n",
    "           order_date AS 'Order Date'\n",
    "    FROM orders o\n",
    "    JOIN country_first_order\n",
    "      ON order_id = first_order_id\n",
    "    ORDER BY o.ship_country\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "attachments": {
    "9853f31e-6584-4835-a451-5323c1a03ec8.png": {
     "image/png": "iVBORw0KGgoAAAANSUhEUgAAAWUAAACHCAYAAADOfkiGAAABfGlDQ1BJQ0MgUHJvZmlsZQAAKJFjYGAqSSwoyGFhYGDIzSspCnJ3UoiIjFJgv8PAzcDDIMRgxSCemFxc4BgQ4MOAE3y7xsAIoi/rgsxK8/x506a1fP4WNq+ZclYlOrj1gQF3SmpxMgMDIweQnZxSnJwLZOcA2TrJBUUlQPYMIFu3vKQAxD4BZIsUAR0IZN8BsdMh7A8gdhKYzcQCVhMS5AxkSwDZAkkQtgaInQ5hW4DYyRmJKUC2B8guiBvAgNPDRcHcwFLXkYC7SQa5OaUwO0ChxZOaFxoMcgcQyzB4MLgwKDCYMxgwWDLoMjiWpFaUgBQ65xdUFmWmZ5QoOAJDNlXBOT+3oLQktUhHwTMvWU9HwcjA0ACkDhRnEKM/B4FNZxQ7jxDLX8jAYKnMwMDcgxBLmsbAsH0PA4PEKYSYyjwGBn5rBoZt5woSixLhDmf8xkKIX5xmbARh8zgxMLDe+///sxoDA/skBoa/E////73o//+/i4H2A+PsQA4AJHdp4IxrEg8AAABWZVhJZk1NACoAAAAIAAGHaQAEAAAAAQAAABoAAAAAAAOShgAHAAAAEgAAAESgAgAEAAAAAQAAAWWgAwAEAAAAAQAAAIcAAAAAQVNDSUkAAABTY3JlZW5zaG90rbQ3LAAAAdZpVFh0WE1MOmNvbS5hZG9iZS54bXAAAAAAADx4OnhtcG1ldGEgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iIHg6eG1wdGs9IlhNUCBDb3JlIDYuMC4wIj4KICAgPHJkZjpSREYgeG1sbnM6cmRmPSJodHRwOi8vd3d3LnczLm9yZy8xOTk5LzAyLzIyLXJkZi1zeW50YXgtbnMjIj4KICAgICAgPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIKICAgICAgICAgICAgeG1sbnM6ZXhpZj0iaHR0cDovL25zLmFkb2JlLmNvbS9leGlmLzEuMC8iPgogICAgICAgICA8ZXhpZjpQaXhlbFlEaW1lbnNpb24+MTM1PC9leGlmOlBpeGVsWURpbWVuc2lvbj4KICAgICAgICAgPGV4aWY6UGl4ZWxYRGltZW5zaW9uPjM1NzwvZXhpZjpQaXhlbFhEaW1lbnNpb24+CiAgICAgICAgIDxleGlmOlVzZXJDb21tZW50PlNjcmVlbnNob3Q8L2V4aWY6VXNlckNvbW1lbnQ+CiAgICAgIDwvcmRmOkRlc2NyaXB0aW9uPgogICA8L3JkZjpSREY+CjwveDp4bXBtZXRhPgrEicQUAAA0oElEQVR4Ae2dCfxVw/vHn/YIhUolRJI9a0iLKMpSlEKJZEtatEpCZCeJFhQKUSFbKUuUpZRKoQhZKtnS7yf8Uv6c/7wfv7m/873de793rVvf53m97j3nzJmZM+czM595nmfm3ikWOBETQ8AQMAQMgbxAoHhelMIKYQgYAoaAIaAIGClbQzAEDAFDII8QMFLOo8qwohgChoAhYKRsbcAQMAQMgTxCwEg5jyrDimIIGAKGgJGytQFDwBAwBPIIASPlPKoMK4ohYAgYAkbK1gYMAUPAEMgjBIyU86gyrCiGgCFgCBgpWxswBAwBQyCPEDBSzqPKsKIYAoaAIZDXpPyf//xHFi9eLN9+++0mNfXss8/KbbfdJu+9994m9ywgcwT+/vtv+eKLL+TTTz+V//u//yuQIWFg/8gjjxQID1/ceuutcvDBB8tbb70VDrbzHCPw448/ygcffCC//vprgSfxFze333671tu//vWvAvf8xZw5c+SQQw6Rm266yQdtNcdk2uTW8jJ5Sco0mksuuUQOPfRQOeuss6RBgwZy1FFHyfTp0yO4vvbaazJ69GhZuHBhJCyfT2655RbZZ5995Morr9RiMthwHf40adJE+vXrt0UHGjrvqFGjFPuTTz5ZTj31VDnooINk0KBB8ueff2rZP/vsM8V+8uTJcSH/5JNPhEH1q6++ihvHbmQPgfnz58sJJ5wgxx57rLRu3Vrq1KmjxxUrVuhDGGQfeughrbd169bFfPA333wjv//+uyxZsiTm/XwOTKZN5nP5w2UrGb7Il3OICw147733lpNOOklWr16thNylSxd57rnntMHlS1lTLYf/Uz5/JP1xxx0n69evF4jsyy+/lOeff17uvfdeJcRU8880/vjx4+Wuu+6S7bbbTs477zwpXbq0vPjii/LYY49JmTJl5JprrknqEQ8++KAsX75cteWkEliktBFAO6au/vrrL2nUqJHUrl1bZs+erRpzhw4dZObMmUnlfc4552h90e9MthwCeUfKv/zyi8ybN08RGTp0qGpsXNDJ33//fSUttAAvjOyQNY1w9913l6uuukqaNm0q5NO2bVuN1qtXLxk+fLj89NNPqkn06dNHqlevnlQcMsCMv//++3Wg2H777ZVE+/fvLzvttFOBPC699FLVRHbccUd55plnfBELPT7++ONSvHhx2bhxo1oI77zzjlBm3qNUqVKFps9mBCwQhM7MOyJnnHGGjBw5Un777Te9Dn+hfT3xxBPq4jj99NOVtIsVKyY33nijLFq0SLp27arp+/btKx9++KG0b99e6wrNDvLo2LGjvid5JhMHbZ1BA7cI9XnkkUfK5ZdfrsdwHueee67MmDFDtT5cXVgkXqZMmaL1STuqWrWqPP3001KxYkWBlCgfsmHDBrnvvvsEPHCf0V5atGihbY3382W94IIL5PXXXxfep1atWnLPPfdo3U+cOFHrrk2bNtKzZ0//aB3guPfRRx/JfvvtJ6eddppcdNFFkfvpnLz55ptKyJTx0Ucf1SzAib7Ae6xatUqqVasWyRrrknsoAIcddpjiWblyZVV8UAZwYdx9990Sxom8J02aJLT/Zs2aaXrarMchUb3yYAb2eO8dfg59Z+rUqdKpUye57LLLImXmZOnSpeqCQZMvUaKEWnDXXXddgbolXrw2WVidJipHovLzzGxK3pHyDjvsIGXLllXTd/DgwUpSdevW1Y5H54uWhx9+WAkNwNE0u3fvLvjGMNc+//xzjd6jRw9ttGgSgEtjhDS5LizO2rVr5cILL5TvvvtOatasqRrthAkTlKjRKsN5DBw4UIl13333jS5mUtdopRAOmjMEvWzZss2uaUJOCERFR27YsKEcfvjhMmbMmE3eAU34zjvvVPIBf+Lsv//+0qpVKyUysPX+S4iNa9w4+KghNuppwYIFAmniIkkmTu/evZUsKlSooEQJ8WJVQRg82+eB/xRi4jnUUVj+/e9/a1lWrlypcejg33//vXz88cdy9NFHK1lCSrStcuXKyV577SWYx0OGDBGeCwH559x8881SsmRJba8MQhD7zz//rJYGRwZz8MO18PLLLytBQ2b42/GDQpCUM5qAwuUt7NzXGWXCzcTgQf4jRoyIJA1jMGDAAO0zWGdvv/22DqS8K3VFHe28886azuOEC4T6BSfyQcHhnH7lcUhUr4W9t38O7hPaPXWG6yssYMRAi2KAiwZlbNasWUrUKGReErXJwuo0XjkKK79/draOxbOVUbbyobLRrhA67BVXXKFaEB0dQo0WtBMaNqMc4sk5HK9ly5aqMaFFkD+dD59uWOLFwV0CIWMWvvrqq6oVQc5oRkyohOXMM89U7ZCRPl3ZbbfdpFKlSpqccm5uQUPBAqCD0sFPPPFEOf744+WOO+6QP/74o0BxIFfqBCzQrpDCfPy8H51o7ty56p6iEzIAhCVeHDot9QxR0lF4NnMPdNSnnnoqnIUSNlouZBpvkKTzE8eXhQzQehHaFf79F154QZ918cUXazgDSVgYQGlLKBAI2juDNQTNIIH4toavHrcVBEa7Ih7C3EgmQv0wmJA3bqazzz5bNWD6kVc6wvmj3NC2unXrpsHR7Tgcl3Pq6IEHHlClBwsK8WXXC/cVr864n+x7e8JHGcH6DQsKF/WMWw2rDfwYEOGH8KRmojaZbJ1GlyPZ8ofLm8l53pEyL9O5c2ftZDQuXBIIjRyTi8oIC6SBZn3AAQeoRsM9r535eHRctFCIlXgIWkpY4sXxHYpRmYEB3x2jMeLv+XxoSBBaJi4HOhYaDIIpt7kFjZVBBS2IcwYxtEjcR74T+zLVqFFDDjzwQCXJ+vXrazDaRiJhEooOvOuuuwqmPUInDEu8OB5vtCQ6I5PAntBwjYSFvBk8KT/kG0toC7xD+fLl1TohDpofQt677LKLvrd3SxHOs8OCBozm611q1P0xxxyjzwUbBEWBj59Ao5OTP4Megkbtn6sBKX7R/seNG6dWC5YN1iYDKAMXmvsPP/xQIMfmzZsrJkzkIoXVGYMacbEI6CfImjVrBCvSS7w6S+W9qQ8mlnkO/TUs1CUaPH0DTZl+SBkoF9aLl0RtMtk6DZeD/pirevNljj6WjA7Il2saNh8EUPCxMurT0ADXC5NPXsLnPoxjuIJ9HEb/sMSLE9YO99hjD02CNoRJWqVKlXAWWSFRCMr7bjFBt4QwEELKfCAM/KRoojNnzixgVnosKSPEkIzEwpmOG5Z4cWLVBSSIq4qVOmFJZkALlz98Tj4dna8brRiCi847/BxIH/HED0F7CZ+H2xsTaeSLgC9+Xd4hEwF/lBg+aIusVEKJgXCxTHBpePF15Y8+PN4xrGSE6yZcb+FwjyX3U3nvRHWGdYRPmkGY+QTml/gwGMEJXvyzuY5+v2TrNFyOVMrvy5DpMe9IGVMK8JhQQGNDW0Fj4wMpUzmpCmYuWh7Ls/yohykTlnhxmDCkEaAJ4O9F0NjQHr3WHc4n3XNGZAjZL5nDhbHnnnumm13a6VjtggaCOU5HRqNt3LixkjINPtxB03nItGnTBM0TEvOdiQmvsMSL48mRMuEzpo1QD9SH94OG80n3nPf3bgrIDd86a3fHjh2bbpY6YEPGtEFcAGAKEeMT54j1kK7gWnryySfV/8/kJJomFiRWG6TsB4B088cFRNtkYta7CSEuJkm9xKsz4mXjvXEh8mnXrp260rB0mcjHKi7M/UIZ063TbJXf45TMMe9IGTMQ4qWzYZowyceSH++rZC1vqjJs2DB56aWXdGkd2hYdAE2XyQMv8eIwK82PJJiMwncFEZAXgisl0wZPPpj+mMXeN8YzmEH22hdxNpfg4oF8mFDjnSFiJoMQ1otnSsrMoJMP70YdI6eccooe/Ve8OAykEDOuCgZJ/Kj4hDHPGcxoK9kQzGHqgMkmVuowkebJKJP8cakwMYrVhwsBJYN5E0xzVtqkKwykuJdQYlgpRH64+yBk3qNevXrpZq3p0LyxTrEUyR9Jts6Im433hlSZcMf68Es1cWXQjrA0/IotnhdLMqnTbJQ/Vpnihf3P1ooXYzOHAzqrGyBNfFZoKhAyoz7aG8uukLBp6IsYK4x7rK1llKWTMdozKx02URLFYbKGSQ60OQiANcRosSwdwr0QJs7wuS+TP8YrG/chJwiZzsToj0nmfZQ+/eY6sgwOTY53gYyZ+GLig2VxrD4IS6x38hjEukdaOhbY885ovEymoTWGJV4cXAVM1jJwMOnHUjy/OgZTHfHPD+cX7zxWXMqNpskkEm2OlR1MZHqXmX+v6LT+2h9jPZO5EgYPTH3aOITMQMKqFZ9vrHSFhfHDKlZEYFWi0WKBsLafQYxJ1HjWZbyyRpeFMqIgQcgMygwCgwYNKlCseHVGpGTfO155yIOJZPo/dcIkI+2AfohlEHYjRpedtOSbTJ0SF4kuR7Ll/yd1Fr6d2Zy34ogqcKZp4NZZplxG56sLnNmkH9dxAzfaB87UKZBPMnHCCZwrI3D+5HDQNnvurIjAma2B6+SB86tl/J5Ou9G6cESqeVEnzmwvkG8ycXwCyuQmx7RefVi2j5SPNhJdzmw8xykJgbOOspFVgTzI15nzAW01U3Hr57XOnMtAs6K8TrEpkG0qdUbCbLy3s4wC2k86kmmdZqP8hZU779wX4XGmsEmWcNzCztGywrO0seIXFodRuqgImkW03z2b756MDzhRHDS28A8islk2nxcaE9pnLiSs3WUzf/LNVd64QgqTRHVG2myUDZdiupJpnWaj/IWVvYQzQwYVFmlrvA/4dFyWz7CWFJKJlmTiRKex6/QQwKxkiRh1Ea9TJRMnvadbqnQQoH9AQvik/Zrr6HyszqIRyfy6GKp05tlYDoaAIWAIGALZQCDvJvqy8VKWhyFgCBgCWysCRspba81ZuQ0BQ2CbRMBIeZusVnspQ8AQ2FoRMFLeWmvOym0IGALbJAJGyttktdpLGQKGwNaKgJHy1lpzVm5DwBDYJhHYdPHuf1/T/1nMNvnW9lKGgCFgCGwGBPgr0lTF1imnipjFNwQMAUMghwiY+yKH4FrWhoAhYAikioCRcqqIWXxDwBAwBHKIgJFyDsG1rA0BQ8AQSBUBI+VUEbP4hoAhYAjkEAEj5RyCa1kbAoaAIZAqAkbKqSJm8Q0BQ8AQyCECRso5BNeyNgQMAUMgVQSMlFNFzOIbAoaAIZBDBIyUcwiuZW0IGAKGQKoIZEzKGzZskI8//ljYhtwkMwTcpoy643ZmueQuNVvWs4uw24A2Jw9xm9Jq/uw6nkh+++03bXMcw1JYejbZYWfypUuXhpOlfZ7t/NIuSBFJyI7vP/74Y8K3Xb58eVb6UK7aeMLC+5tsB5WO/PHHH0H37t0Dt++a7njrtgAPrrnmmpzuLpxOObeGNE899VRw5JFHKo41a9YMzj777OCbb77JuOjNmjXTPP2u3v44bdq0tPL+5JNPND92uc6FzJs3T/Nnx+BYsnr16oDdk/fZZx+Nx7FDhw7B999/r9ELS79mzZpg3333DW699dZY2acclu38Ui5AEUlAuzv99NMj9X7MMccE7r95Crz9m2++GZx00knaLuCkvn37BuzIHi1+h27fF8LHt99+O3BKZnD++ecHbk/C4IQTTghoc2EZOXKkhhMvVyLpZtyjRw8FoH79+sH1118fABQveOONN6abZZFMN2vWrAAifu6553Rb+K+//jo466yzglatWmWMB6R81VVXBZ9//nmBj9Mw08p7S5MymLRo0SJwmm7AdvcfffRRcNppp+kgxgsVRsrE+eWXX4K//vqL06xItvPLSqG2oUxQ/o466qigd+/eAYP12rVrgzvuuEMJ+ttvv9U3ddpxgFI4cODA4Oeff9Z2cOihhyovRUNBfX3xxRcFPsOHD9fBeuXKlcELL7wQNGnSJPBKZ5jPaP+Q9fvvvx+dbVav0yLlL7/8UgkYEqZjIJCLH3WcmZvVQm7LmX3wwQdBtOZKo2OQy1Qg5XCjis4PTeLmm28OGjduHDC4DhgwQMnOx6NsHTt21AbfsmXL4Omnn9Y6DmvKNOK2bdsGhx12WHDxxRcHH374oU+ujXvUqFE6yBxwwAGaN2TKIH788cdrmvbt20esgkSk6txjQa1atYL7778/kj8nq1atCl5//fXg77//jpDyxIkTA/fvXJr/RRddpB3ZJ2revLl2PK7pfI899pi+48EHHxy0bt06mDp1qo8azJ49O2jYsGHwyiuvBE2bNtX8OnfuHDBwegnnN3jw4KBfv36Ked26dQO3k3pw7733+qh6fOSRR7RskAaKzX333afafoFIdhFBAGsESxIy9QLnwDXz58/XIDCmv0CkXkaPHh3Url270AGYAZq6RbNGHnrooaBTp056Tlu74oor9Hzjxo1ab7fddpte5/IrLZ+y65Tq/ahevbq4xqznrvHK9ttvr+dOM/PeETsWgoAjM3HkKa5xqC/MDW7iSEXatGlTSMrMbw8aNEgmTJggziUgjlBl+vTp4hqnZuw6gzgCkh9++EGuvfZa3Wb+uuuuK/BQZ+6J02CkRo0aGmfdunVyySWXRPx+K1askCFDhmgbueGGG6RMmTLiSEgcuYsjS+nTp48481CuvPLKAvnGuihRooQcfvjh8uCDD4ozQcVpRBpt9913F2e2SrFixSLJXCeVdu3aiXNtiNNqhPf04rQh8b5oynfLLbfIzjvvrHF23HFH6dmzpyxevFijuwFEiO/ccnLmmWdKr1691JftOqowl4KE83NanEyePFmc60lxdGa0DBs2TN+RuNxzxC0HHXSQgAf5jxgxQpz7hdsmMRDYdddd5dxzzxXqxikRgs/49ttvl/3220/cwKYpnJIobsDW9uWzcJqzOCIVN2j7oJjHl156Sb766ivp2rWr3icf5sicFi5uUNZ8uUE9Mm9GG8i5pMP4jCCMVPg+w+J9OuPHjw8H23kSCKClekuDUTsbJjaaMhob9eQ/aL6ImzRRExDt1wtaImXARHziiSdU0wj71PDFcn/ZsmWahLx8fgQ4Ug7QAEmLuI6jJqVe/PcLExBz08ukSZO0HG5yL6LpxvMpE44263E6+eSTAzdxF6xfv16z85p2WNvF7MUK8IJG7Nsn5UNT94KfED8icyPIa6+9ps/y70PYwoULNeydd97hMgjn5wg9OProoyO+TNxEaPdjx47VuNRBGC+0fzR6NDWTxAjg7/X1jmXnXRekworzmq7PhTkZ4r/11ls+aJMj+J944okF0tLvnGKhabGk6AtYjLgt0NA//fTT4O677w6mTJmySX7ZCoj7J/eJRgM0HoSRIyz+umzZsuFgO08Cgcsuu0zcZIbMnTtXnLmmI7ebVEgiZeIolSpVUg3Tx/J1wwoE14jEkaK8++67epvVC8iSJUvENT5xk2JStWpVDeOrUaNG4szCyDVaPYJ26YUZctd4/WVEm/EBaCKONFXbRbPknHJ4zdPHi3WsUqWKPPPMM4Kl5jqbPPvss+JcPTJnzhxxxBdJ4q03AurUqSPO/RC5F33SoEGDSFDp0qXFDWLiBo5IGCe8txe09R122EHjOBeMD44cncksJUv+063KlSsnbr4goimTr/PxR+Ki/R933HH6LpFAO4mJABYlbRUrnHo/44wzxA2asssuu6gWjdURFn+Nhh1PnOtNsJacSykSpXjx4trGsfp22mkncS4RteiwGrHKHFGLG3i1/cJ3bkCIpM3WSVqkTMdCMD29YH57MwzTwiQ1BPbaay/hQyeFSDFvwRMiykQgGaeFb5IFjQ1xKxgEExGBgCtWrCiVK1dW069UqVIa7r/8ddhVAPFUq1bNRxE3ESdhsgrHJRLm36uvvqpxaEeU77333oukT+aE9sUHV4nzmcu4cePEadGRpJCrF4gvkYTjEo9rzN6w+Pf2YVxHx/H3PCHHuoYgGLTCQuc3KRwBBkI34acf3FXO+hFIFTcY7jNcDmHx3MS9WAJfOYtf3VL0u2iBkJE777xTaOO4N9544w0lapZVDh06VAeFXJByWj5lp8qrDw/Nyi1F0cI///zz2lDpBGgHJskh0L9/f3GTQwUig5+buBKvuRa4maUL/J2IM53V/4kvGf8wpElDpo7RSrz/lbgLFizgoJotRwg1nJ48eB86TCxBG3aTmuKWUsqYMWPk6quvVn96rLjRYWjHdMZFixYVuHXEEUcUuE71wr8T6dDYyZ93D4tzWUQu3SSfrtOOjhOJkOAEPyf9xE1aaSy0NLQ9k/gIYOU4d0WBdojSAmn6NctYdFh34OmF7exQMCpUqOCDChzx7+Nv9r7kAjf/e4EFxpyLc1cIvMbaZZ6NoLjkai1zWqSM9sbEENKtWzdxs5XilqPoNR17u+2203P7KhwBTCG3HE5HbUiQhsDEFFpr2AwvPKfUYtBg69WrJ0zeQQxoGrghmHSEiJ2vTTOkfpksoxG7pUMFHoJWjLaC5oBp7lYyCO6AGTNmFIjnL3B70UmYIOR5dDgm5ZIRtGomepgcdKstBHKk3Ggy3Au7WZLJz8eh8zLZBhmDO53bLbPzt/VIONo9Ez9uxYTstttu4taVF4iTzAUDFj/AAVsmV9m/zVspyaQvinHcqgq1GGmHzrer7QzLD0LElYAwKU6dEE67wv1Hu/RuNdojmPsFCLgdaMtoubG0ZPKkD6AsoaR4zwBWJW49Jr9pC1znQkqmmykFZqSig8ycOVP9bG4SRi6//PJ0syyS6cCMlQSsKsAkYkTGD4rvNtr0TxWgwtJDRvg4IRrcGaymcUvY1E+Hr46VE24JkJxzzjm6QoFVEnfddVekXKxwoA3wKz/IEX819c87IbGez+oDyAlC5xluOZ24ZUiCLy+RkBerLtCy8b97wdcYPVj4e7GO0WVCuWDA4V0pD+3aD0g+Pc+jc/JLQ9wm4Fa+fHm9Hc4vfO7TEubfjU7MbD8Ez+w+fko3OWnasgcrxpFB/Mknn1Sryk2UqjWDUsgqHj8wsuqL9oeCQbtCKYSoWbWBMHeBO8O7jlCCuE6kJdPuccuhcHrhecwv4J5DU8bHnRPJdMaQtYHMSsb69UymeRe19KwucBMUm/21WXXALHM8YUF+YatBnKsl6V9zsqY4lfjR5WLNqtOIdLVH9L1Urh3B6tpr0lCe6Hd0mrjOwlMntO9EGCXzXDdBpT988H3FaWy62qNLly7JJC/ycVjdQz0lEre8LWYf8pgnSpvsPdofbThXkram7EcITNJcmtn+OUXhmOmkXroYMbkVPeEVzgsNsjDBHZKsoD2mEj86X/yJrPXNphRWHibwWM+ciTB7z5pv/OpozbhMHFlIx44dM8m2yKRlojTRagqAqBFnYi96AjYT0PwkYCZ5JEpbDLZPFMHuGQLbKgIPP/yw8KMn7zOMfk9+BILfGxdHYas4otPGu/a+cFbWQCBuXbTsscce8aJbeBFEwEi5CFa6vbIhYAjkLwKJZ1fyt9xWMkPAEDAEtkkEjJS3yWq1lzIEDIGtFQEj5a215qzchoAhsE0iYKS8TVarvZQhYAhsrQgYKW+tNWflNgQMgW0SASPlbbJa7aUMAUNga0Ug7o9Hwn9Es7W+nJXbEDAEDIEtiQD/bpeqmKacKmIW3xAwBAyBHCJgpJxDcC1rQ8AQMARSRcBIOVXELL4hYAgYAjlEwEg5h+Ba1oaAIWAIpIqAkXKqiFl8Q8AQMARyiICRcg7BtawNAUPAEEgVASPlVBGz+IaAIWAI5BABI+UcgmtZGwKGgCGQKgJGyqkiZvENAUPAEMghAkbKOQQ3n7N2+9Hp7r7sgJEPwoaiH374YT4UxcqQhwiwAzWbzbq98eKWjh2u3T6Xce+Hb7ANF7tbx9t4iV3Ht5RkTMqAxG7GRx11lO7Vx27HJukhQEMYOHCg4sju1rkQyPiee+6RunXryllnnaXbtDdt2lTmzJmTi8clnSc7Wr/99ttJx/cR2V6JPSJjfZLtoD4vO+YfAuwgPmDAADnmmGPklFNO0eOtt96qexv60kLG7DjO1lq05fbt28uXX37pb29yvPfee3VXato/8dnd2svGjRvl0ksvlZNOOklOPfVUiVZaxowZo+HEy5VkRMpul2M577zzdHt2tqhH4o08uXqBbSXf9957T8444wxZvny58Hv5RBpBJu/8xBNP6JbtDJ48c8aMGdpAr7jiCt12PZO8t0Ra397YEv7FF18s8Nltt922RJHsmVlE4Pbbb1cLaty4cTJ37ly57777xO0KLk899ZQ+hX7Sq1cv+fHHHzWcPRXLlSsn3bp1k1j/30P7R4m88cYbZfbs2XLuuefKoEGDtC+Q4euvvy4//PCD3mOgHzt2rD6Hr88++0xGjRolN998c8KNhiMJ0jzJiJQxJ5YuXSodOnTQjp1mGSyZQ2Dx4sXSqVMnGT9+vGy33XbCjs+5EFwEe+65p5x44olK/hBX//79Be3B7/jbokULbfQQNRrK+eefrxuIhsszdepUufDCC+X444+XK6+8UpYsWRK+LYnuo/1cf/310rhxY9XUfQfzGaCpoM2H5ZZbbpGePXuGgwqcs1M0O0SHP8WLF9edo5s1ayYff/yxxqcTk/8NN9yg13feeaee09kbNWokxB0yZEhkUIQICJs4caI0b95cunbtqunQxHr37i0NGjTQjs19L+vXr9f3A2Py7NOnj6DNIYnu+fR2/B8Cxx57rKBAQJCQ7XHHHSeVK1eOuCmoh/fff1/b8L777iu0A0iWTW+pu2h5+umnpVWrVqohsyv1JZdcojujT548WaNC7mxkW6ZMGalZs6YSNDdwd1xzzTWqhR9xxBHR2Wb1OiNSZut5zM6rr75aXyKrJStimWF+XXTRRQKR5FKOPPJIHfEhJUZ+BDLG9KOxIytXrtSOUKFCBTUd0dwh7o8++kjvo2FgUu61117St29f+fXXX5WYf/rpp6TuQ8jTpk2Tdu3a6U7RaCOrVq3StHzRAdF6vbWAP3HKlClyyCGHROJEn6Axhz/+PibvzjvvLIMHD9b8JkyYIB988IEOgMSBLHnWrFmzpHv37krAaGXDhw/XLBhAKBvtvHXr1nLOOefIunXrlJwJR0urU6eO5o+WhgwbNkxmzpyp2IDPggULBE2+sHsawb4KIIAL4YADDhDawNq1a7Ue0GQJRyBf5KCDDtIjX9WqVRO4CddWWGhPtO0DDzwwHKzXPh+IGAVj9erVqj1zjaAh4/rzg3KBDLJ8EfevO5N5TvXq1YWPSeYI5Eozji4Z5hoNEI0BMxDCOvnkk+WCCy5QkvXx0QY8kaAhnnnmmRofYnzsscekXr16ctNNN2n0Jk2aqObxxhtvKGkluo/WOX36dLn22mvV9UUG5Em5vNDhRo8eLYsWLRLKgcaDKeo7oo8XPqK1hwVr4OWXX9ZBDnOzTZs28sADDwjmKx2LAcVLiRIlZOTIkeLdHWhFkDck7QUs8MMjzz//vJrLWDXgh6xZs0bTMAh88sknUrt2bdWsucdA6CeOEt0jrklsBBo2bKiDYalSpQTrxg/QkOz2228vO+64Y4GE1OWKFSsKhOEfxhdcpUqVAuFcM+gjWH4oBfQJrC7801iXDNS0HfoOCgX1S13nQjIi5VwUyPLMLQJo4phhXbp0UW0OjQ6CfvXVV9Vl4QdZSNdL6dKldSJ32bJlGvTOO+/oEe3ZC9oyriwk0f29995b44TzpxOUL19ew/mqVauWNnr83ZAyR4gtujNFErgTCBTz1QsuIC9oO7hi0GDpzAxAYSGdJ2TCKRudMDxRGNbEFi5cKMyh3HHHHZFsvJZMAJ2VySjcQLgvmEw69NBDNW6ie5HM7GQTBBhQGfiwaLA+cGeddtpp6tLYsGGDarEMrl6wcKKJ2v+3MffCEo5L/8BKoj2TnrxRIHBz0P6oU9oigzaD9+mnnx7OKivnubWVs1JEyyQXCECCLVu2lKFDh+rEB2Y85OcFIg4LGkp4xhn/XtWqVSMfOgj+Py/x7vs8yC8s0dfkR3kwOdHAuU4krP7Bh+s/+B7DUqlSJb3EVxhtlUQ/21/7spIw7FaiMyLh90eLv/jii9WFglvm8ccfV82awY5r/NRIonsawb5iIsCgRt0yQcdADSkiWDy4FfAFe8GNhVYctoa4hw8ZyyY82BLOdXRcT+j0D9oy7kXcXoQz/0I9vvnmmyTPupimnHVI8ztDiAMCQ3P0gqbqiciH0QCZ4ENo5JhwmGwI6SGkHj166DVf+PkgPCTRfU905IfvD8E3ixYUFkiODsEE2i+//KLmZPh+KufkzWRR27ZtdfkTlgHnXliv+vvvv2vnI4xJVzRtJnxiLa1C20Zj69y5c+Sd0az4QPgQBH7Qww8/XAcVJp4mTZqkE4OJ7vny2PEfBLBGmJS76qqrCtQ/bgW/fBLLC8wZ/LwLi7pBwyVetBD22muvSceOHfUWWjKWHS66aJk3b54888wz2gbRwnFBVaxYUaMxyGMx5UKMlHOBah7nCVk8/PDD6oerX7++khFrL5lIwZ/mBXMcEkb75Ycd+EL9JAdaK/5k3AKsPmAiC1MeLQbfc2H30XRY6oQ2jj8QzSNaMBUxEzFTmYQMuzei43LNIMIEXFjQlhko8CkziYkZiqaD1oqP0rtD8FdjEjNgsaKINeL4FMPacThfXBKYuKy+wKyF0CknfmzMbMIgdciY49duwsm7bRLdCz/DzkXKli2ruNG2qAvcTPPnz1efPnMECOTI5CttGG0XrZb6pe5pPwyUrA4jPv5h3HbU84gRI7SO6QsQOJPsYaFN8JsBltb5yb4aNWro5DgDK/Mcvk7D6bJxnjVS9iahP2ajcJZH9hHA98qyLIjQ/9AHkw5/a9gni9bBqgSWijGTzXI0yAihgdMwmfggHzoPDR13SDL36TQsEyNPtHA6C8QW3XYgdzphMn47tOpowf2BRs7aU3zEaDtot0wAQtR+hQUDD+/AO2MxMDhdd911BbILl41lVzwPsqDD8w74ockT4UiHBifS7b///upjLuyeJravAggweINlv3791H3GIE47Y0D0gibNpB6WG64MVsMwICJo27gysOQQlniyhJO2y4oKLD5WInEMC22UMOrXC5YPigwT2wzytKlcSDHXoIJYGcdaeB0rnoVtnQjgF2Um2ft+w29B46OhovXyAyFIO57WmMl92hjL8SDEWEKnofNArvHixEqXShiTnpilPIvyQNzhScLC8kITg8hjlY/8wA0iiZZE96Lj2rWoJYcigHUTry0yL4J7LDxpC3bMS0SnwTKEyL37IxWMqXMmDcMDdbz0fnIx3v1Y4VnTlGNlbmH5iwBEEtaM45V01113jXdLwzO5H6/BotXw4xE0XMzNWISXsFBp3oxXnkTZ+QmhWHES5ZfoXqy8inoYg7efg4iHhV+eGH0/mpC5T36xfM7RaWNdJ6rzWPFTDTNSThWxIhAfc5DJvy0lGG9MqOC3LmzVRaZlZELR/0VApnlZekMgGwiY+yIbKFoehoAhYAjEQCAdi8jWKccA0oIMAUPAENhSCBgpbynk7bmGgCFgCMRAwEg5BigWZAgYAobAlkLASHlLIW/PNQQMAUMgBgJGyjFAsSBDwBAwBLYUAkbKWwp5e64hYAgYAjEQiLskLkZcCzIEDAFDwBDIMQKmKecYYMveEDAEDIFUEDBSTgUti2sIGAKGQI4RMFLOMcCWvSFgCBgCqSBgpJwKWhbXEDAEDIEcI2CknGOALXtDwBAwBFJBwEg5FbQsriFgCBgCOUbASDnHAFv2hoAhYAikgoCRcipoWVxDwBAwBHKMgJFyjgG27A0BQ8AQSAWBjEmZ/arYSZh9zkwyQ4Bdj1etWpVZJps5Nbs/s7kqW7VnS2hLjz76qLDn2uaWn376SZ+dzffZ3O+wLT4PnmGPvnjChqmffvqp7ikZL050+HfffRe33W6JthcpHxunpiMOoODcc88N3D5Xgdt8UD9t27YN3M6x6WRXpNPMmjUrqFu3bgTHpk2bBp9//nlOMXG7UAdu1+aYz3Dbswf3339/zHvRgePHj9dyL1q0SG+53aOD5557LjpaSteffPKJ5vnZZ5/FTOfbmz9S3r59+wauI8WMn0rgvHnz9Nmuw6aSzOLmCAHagtvNPMIzbjfqwO1GHnma2xQ1uPvuu4ODDjoo0n8uv/zyYO3atZE40SdPPfVUcOSRR2r8mjVrBmeffXbgNhHWaBs2bAjOP//8wO1AHpxwwgnB6tWrCyQfOXKkhhMvV5K2psyW8nPnzhX3crq1N5twvv/++9KrV68I4dtJ4QisWbNGwLJdu3by8ccfy5w5c6RChQq6DXrhqTOLwS6/sYTwePei41PuhQsX6rbu3GOz07vuuis6Wlava9SoIQ0bNtRdrqdPn67tz5Fpge3gs/pAy2yLIOCIT+u0du3a8u6778qCBQukVatW0rVrV3FkqWUaPXq0PPLII3Lrrbdq/3GELStXrpTu3buLI81Nyv3WW2/JwIED9eMUCW1DaNk9e/bUuLQnp1gK9w499FAhfy9o4liFtO/SpUv74Kwf0yblZcuWCbu6sgV8jx495LbbbtPC0Tl4SZPkEKABuJFaOnfurFvRsz260wzkiy++EBrllhYGiUaNGsnzzz8vp5xyipLvJZdcEnEtzJ8/X1q2bCnr1q2TG264QcaOHatmJmnGjRunxedd6EgM4GeeeaY47brAa9EBLrroIu0E3GdwSiRlypTRnYjZHn6//faTc845Ry699FJZsmSJdkhcD5Slfv36cvjhh4vTfHQ7eZ8n28vfcsst4iwSLRPv4zu5j+OPTlvX93aWgwY5C0aJgnwbNGgg119/vZCfF9wubPZap04dYQNa0l1wwQX+ttZrIiwiEe1EfvvtN+ndu7diXKVKFWG3aja6hWxxPSCvvfaanHTSSdKiRQvtP07DlSuuuEJJfOnSpZuguNNOO8nw4cO1HXK+1157ybHHHiu44RB2Ut9zzz2FNlarVi0laML//PNPLcuFF14oRx11FEE5k4xIefHixZERw5mtWsjdd99dSpQokbMCb2sZs2s0AxqNwItzZwjaQTjM39vcx99//12JbtiwYdKxY0c577zztME/++yzWhQIEM0EzRpCrVevng7WV199tTj3iPzyyy9KmCtWrJD+/fsrSV533XUydepUTY+lwIBEZ7j22ms1PfdTlY0bN2qSUqVKqTbz9NNPK9H36dNHCffKK6+MZImmxMDAYIJC8dVXX+l7RSsTzqRVAqajd+nSRd/xsssu0wGJPBhMnSkc0aYmT54sgwcPFmdK66AAdiNGjIh07MKwiBTQThSBXXfdVZyLVNsTpLh8+XK5/fbbdSBGi0WKFy8uvu41wH0xL4DEmp857LDDpFmzZqo40nbpaxMnTpQ2bdpoGogYpQCSnj17thIzN2j/DL6bwxNQUkuS4RfasTdZ0Q5M0kcAsnjzzTfVJEs/l+ynvPnmm5VkyZnO8eqrrwoaZljQHtFcGaDRaJBnnnlGCRcS32WXXTSMTsN7olG+8sorOkns/NBStWpVvQ85YjbGMj81QuiLjkLHeuyxxwTNGY2KwcHNb6g2TVRMTQaE9evXa8eCsNFw0XoQ56dUIvfaEmFozhC28/XLPffco4oGz6KdOz+kam3EQ8vHrEYgaCyEO++8U68hfTQ4CAVBq2PwiYeFRrKvTRDAdeGtDVxXWOcMvoibT5BRo0bJzJkzxfmA1UqbMGGC3vPatF5EfVH/1BfC4OrdF7jFIHysIOoZRYQ6fvjhh4V2wwA+ZcoUYaCm/eZCMiZl/In4RP/44w890iFM0kPg7bfflkGDBikZ0MDySQ455JBIcWi0kyZNilwnOmGegbaBBukFv58X/HTMR3hCJhxiC/vyfFx/xHXGh84JcUPiaFVPPvmkRkHbQRN//PHHxU346DnxcAfxPM55hhesEjRaBNJE6KjIG2+8EbH8SpYsqeYuAwguGTRfBgQ6L+ImpdRloRfuC4sR0sCPiRSGhUayr00QQLulveE6YkA744wzdIBjkMfKeu+996RTp07aBlg10aRJE8HKwRUYT7B4cBMyLwY541Jyk3iqedP2cMfh3qDtYm3xHLwA5H300Udr22KQZuDNtmREyiyFYyTBTOvQoYOan9kuYFHJD/8XJrI3qXP93rhGWGaE2wET0AsEhy+vbNmyPkiPEJKX8LkPi3f0WmK1atUiUehUNHDIEdPTaz0+gr8uVqyYDypwRBumQ+A3RiB0fLjebYaJiSaP+wSCRtul4yKkQ5J9BzoqGjPy888/KyFwjkblyXi77bYjSM1sMA0LndtLYVjEe1+fvqged9hhB/Xj4svFf8xcwQsvvKDuKdopgzPWCnMKDPBo09Q/llM8wZfMh0GzUqVK2paY36FtIRAygtVTrlw5JW0GaMIfeOABGTp0qA4MeUXKqPSYf3RgtwRF8CGapIcApjIjPRMOd9xxR3qZpJgKEmMwZWBlAs4Llg++Nu+z8+GpHMM+PrQcGjOz4d5HDlFBXpAQZiBaLe2Izod4dwCkHUvKly+vnSmW+Yg2PG3aNDVHmfBBcG14Usbfi6C1Vq9eXc9xp+BmYWbfC+YwYUzUHXHEEWrO4mNkrSz3mAxCcN35wQBrgglR6pIy4kfHZUGnRwrDQiPZVwQBXFv4kF966aVI2wBLiNGvWcZqoS3h3sANhQwZMkRdVpBztODGQhHxLibuM7gSRjvwpEw4k9y4QhgAqGO0cF+XlStXFia5cyH/U5FSzN0TMskYlRjB/AfTziQ5BCAoVh7QyM466yzt8DNmzNClOrn8QQ4EAbFAXC+++KKahhyZEEOD4H46QgNHo4TQaOSNGzfWbDAPIXxMebQLJsqQE088UY/dunVTomSyjNnxdAXiZ0khriAmbOjYbk12JDtm8HEN4SOHvCFV5kHGjBmj6XxE/NCEM1ByxD/pzWHKiPuEfEnvhQ5PnfFOTIjiV8et4qUwLHw8O/6DACSL9krbQHnAPTRgwAAlR9wIXnD54fNlEGQApi5ZtYEWTf9i1RCrghBcDxA5gy3uEIiX9FhbTLp7QUno16+fTuxhbSHuNxnq/sLFxQDNdU7EaSNpiV+4H+vozIi08iyKiRxpRBa9R2PpNMacQsIPJFq3bh04k0/LwJFrwr04TU/vOe3ZBwUsoHcmpF47n6red/5VvXZaS+AmtzSMH6ggrgEHbsY78gMA5+oK+PGRF7c2NHA+Xk3jtFLNHyzi/XikefPmwY033uiTb3IkPzeoaH78SMCtbtmkjG6SMjjwwAM13JFl4LF2JKthHgO3OiTgxylOiw6c+yFwg0ngOqnGASt+qNC+fftIGfgRgvNJBq6jB06jDpxGFvBjIC+FYeHj2fEfBNxArvj5H6lRF26iLQKPc0cFTpuOtGFHrIGz2gPnhtM4jkADZ8FoHJ/owQcfDGhntDHf5h2h+9t6dOQf8GM4nw+BTpsOnHIROMVD24RbdVQgTbYubOPUnAx1W1emuCuYGEFD3n777bNSeCZI0DbD/mq0FvzF3gcb/SAm5dByw2mi4yR77TqIauxoxt69EJ0WHy8rMrz/MPp+vGtcJGBG3mFBg8ZKxKeNzxr/PBqdI//IRKKPXxgWPp4d/0EAFwW4V6xYMSYk1COaMtqrn5PwEamHWG0ALZy6T7XNU3f8RiNXcwBGyr7m7GgIZIgAM/lMfOOXhByYd4H4Ma0xm00MgWQQMFJOBiWLYwgkicDXX3+tk3toYUw04b/eY489kkxt0QwBESNlawWGgCFgCOQRAsXzqCxWFEPAEDAEijwCRspFvgkYAIaAIZBPCBgp51NtWFkMAUOgyCNgpFzkm4ABYAgYAvmEgJFyPtWGlcUQMASKPAJGykW+CRgAhoAhkE8IGCnnU21YWQwBQ6DII2CkXOSbgAFgCBgC+YSAkXI+1YaVxRAwBIo8AkbKRb4JGACGgCGQTwgYKedTbVhZDAFDoMgjYKRc5JuAAWAIGAL5hICRcj7VhpXFEDAEijwCRspFvgkYAIaAIZBPCPw/lE/LvrdQMLYAAAAASUVORK5CYII="
    }
   },
   "cell_type": "markdown",
   "id": "679d0a61-bead-4e33-8bac-adfa8f99932a",
   "metadata": {},
   "source": [
    "### **PROBLEM 8**\n",
    "#### [15 points] For each shipper, display the shipper's ID, name, the number of orders shipped by the shipper (as determined by orders.ship_via), and the percentage of the overall total orders that were shipped by the shipper. Sort the results by shipper name. Your output should match\n",
    "![Screenshot 2025-03-20 at 5.22.35 PM.png](attachment:9853f31e-6584-4835-a451-5323c1a03ec8.png)\n",
    "#### **Tip:** Field `orders.ship_via` is a foreign key that matches primary key `shippers.shipper_id`.\n",
    "#### Full credit if you can do this with a single `SELECT` statement (i.e., no views, CTEs, or stored procedures).\n",
    "### One solution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7201018-ae49-42e9-86c9-9004c01e6778",
   "metadata": {},
   "outputs": [],
   "source": [
    "# How many orders did each shipper get?\n",
    "# The window function returns the total number of orders.\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT shipper_id AS 'Shipper ID', \n",
    "    \t   company_name AS 'Shipper name', \n",
    "           COUNT(*) AS 'Orders shipped',\n",
    "    \t   SUM(COUNT(*)) OVER () AS 'Overall count of orders'\n",
    "    FROM orders\n",
    "    JOIN shippers\n",
    "      ON shipper_id = ship_via\n",
    "    GROUP BY shipper_id\n",
    "    ORDER BY company_name\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97edf38b-63b1-4f89-94ea-d1c4e210b970",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT shipper_id AS 'Shipper ID', \n",
    "    \t   company_name AS 'Shipper name', \n",
    "           COUNT(*) AS 'Orders shipped',\n",
    "\n",
    "           -- 100 * shipper_count / overall_count\n",
    "           -- Format to have one decimal place.\n",
    "           -- Concatenate '%' at the end.\n",
    "    \t   CONCAT(FORMAT(100*COUNT(*)/SUM(COUNT(*)) OVER (), 1), '%') \n",
    "               AS 'Shipper share'\n",
    "    FROM orders\n",
    "    JOIN shippers\n",
    "      ON shipper_id = ship_via\n",
    "    GROUP BY shipper_id\n",
    "    ORDER BY company_name\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46a0d426-eacb-40dc-b710-1d294fac38b8",
   "metadata": {},
   "source": [
    "### Another solution:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84b887be-558e-410f-aab6-33bd2f58da8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Two window functions and no GROUP BY.\n",
    "# Must use DISTINCT.\n",
    "\n",
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT \n",
    "        shipper_id AS 'Shipper ID', \n",
    "    \tcompany_name AS 'Shipper name', \n",
    "\n",
    "        COUNT(orders.order_id) OVER (PARTITION BY shipper_id) AS 'Orders shipped',\n",
    "    \tCOUNT(*) OVER () AS 'Overall count of orders'\n",
    "    FROM orders\n",
    "    JOIN shippers\n",
    "      ON shipper_id = ship_via\n",
    "    ORDER BY company_name\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d4070ff-b390-4b33-a9b1-7fdef8ee56ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_query(conn,\n",
    "    \"\"\"\n",
    "    SELECT DISTINCT \n",
    "        shipper_id AS 'Shipper ID', \n",
    "    \tcompany_name AS 'Shipper name', \n",
    "\n",
    "        COUNT(orders.order_id) OVER (PARTITION BY shipper_id) AS 'Orders shipped',\n",
    "    \tCOUNT(*) OVER () AS 'Overall count of orders',\n",
    "     \n",
    "        -- 100 * shipper_count / overall_count\n",
    "        -- Format to have one decimal place.\n",
    "        -- Concatenate '%' at the end.\n",
    "        CONCAT(FORMAT(100*(COUNT(orders.order_id) OVER (PARTITION BY shipper_id))\n",
    "                         /(COUNT(*) OVER ()), \n",
    "               1), '%') \n",
    "                   AS 'Shipper share'\n",
    "    FROM orders\n",
    "    JOIN shippers\n",
    "      ON shipper_id = ship_via\n",
    "    ORDER BY company_name\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a368d6c-176e-4cc9-bec2-b10837d3e869",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}