8.13.SQL numeric functions and aggregations [Exercise].ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "51b1254d",
   "metadata": {},
   "source": [
    "<div align=\"right\" style=\" font-size: 80%; text-align: center; margin: 0 auto\">\n",
    "<img src=\"https://raw.githubusercontent.com/Explore-AI/Pictures/master/ExploreAI_logos/Logo blue_dark.png\"  style=\"width:25px\" align=\"right\";/>\n",
    "</div>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f662d169",
   "metadata": {},
   "source": [
    "# SQL numeric functions and aggregations\n",
    "© ExploreAI Academy"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "26af890c",
   "metadata": {},
   "source": [
    "In this exercise, we will apply SQL numeric functions and aggregations to query a few tables in our Northwind database. Ensure that you have downloaded the database file, Northwind.db."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "2d230d14",
   "metadata": {},
   "source": [
    "## Learning objectives\n",
    "\n",
    "By the end of this train, you should:\n",
    "- Be able to apply AVG, SUM, MAX, MIN, and COUNT functions.\n",
    "- Know how to aggregate data at different levels using the GROUP BY clause. \n",
    "- Understand how to  order and limit the results."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "7fd5e3b8",
   "metadata": {},
   "source": [
    "First, let's load our sample database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2d0d820e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load and activate the SQL extension to allow us to execute SQL in a Jupyter notebook.\n",
    "%load_ext sql\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "599e3c6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load the Northwind database stored in your local machine. \n",
    "# Make sure the file is saved in the same folder as this notebook.\n",
    "%sql sqlite:///Northwind.db\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "06af8b46",
   "metadata": {},
   "source": [
    "Here is a view of all of our tables in the database:"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6ed1f036",
   "metadata": {},
   "source": [
    "<div align=\"center\" style=\" font-size: 80%; text-align: center; margin: 0 auto\">\n",
    "<img src=\"https://raw.githubusercontent.com/Explore-AI/Pictures/master/Northwind_ERD.png\"  style=\"width:500px\";/>\n",
    "<br>\n",
    "<br>\n",
    "    <em>Figure 1: Northwind ERD</em>\n",
    "</div>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "920e89d2",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "\n",
    "Run the necessary queries that will provide us with the following information. Compare your queries with the solutions at the end of this notebook."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "466e53c1",
   "metadata": {},
   "source": [
    "### Exercise 1\n",
    "\n",
    "Display the average unit price of products in the 'Products' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "038f28ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///Northwind.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>avg_unit_price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>28.866363636363637</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(28.866363636363637,)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT AVG(UnitPrice) AS avg_unit_price\n",
    "FROM products"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "86802726",
   "metadata": {},
   "source": [
    "### Exercise 2\n",
    "\n",
    "\n",
    "Show the total quantity of products sold for each ProductID. Use the 'OrderDetails' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "91f3421f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///Northwind.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>ProductID</th>\n",
       "            <th>total_quantity</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>828</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>2</td>\n",
       "            <td>1057</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>3</td>\n",
       "            <td>328</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>453</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>5</td>\n",
       "            <td>298</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>6</td>\n",
       "            <td>301</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>7</td>\n",
       "            <td>763</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>8</td>\n",
       "            <td>372</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9</td>\n",
       "            <td>95</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>742</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 828),\n",
       " (2, 1057),\n",
       " (3, 328),\n",
       " (4, 453),\n",
       " (5, 298),\n",
       " (6, 301),\n",
       " (7, 763),\n",
       " (8, 372),\n",
       " (9, 95),\n",
       " (10, 742)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "  productid,\n",
    "  SUM(quantity) AS total_quantity\n",
    "FROM orderdetails\n",
    "GROUP BY productid\n",
    "LIMIT 10"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f0fade51",
   "metadata": {},
   "source": [
    "### Exercise 3\n",
    "\n",
    "\n",
    "Find the maximum, minimum, and average freight from the 'Orders' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "748f4263",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///Northwind.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>min_freight</th>\n",
       "            <th>max_freight</th>\n",
       "            <th>avg_freight</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>0.02</td>\n",
       "            <td>1007.64</td>\n",
       "            <td>78.24420481927719</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(0.02, 1007.64, 78.24420481927719)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT\n",
    "  MIN(freight) AS min_freight,\n",
    "  MAX(freight) AS max_freight,\n",
    "  AVG(freight) AS avg_freight\n",
    "FROM orders"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f28aef00",
   "metadata": {},
   "source": [
    "### Exercise 4\n",
    "\n",
    "How many unique customers have made orders? Use the 'Orders' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f4621a53",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///Northwind.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>unique_customers</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>89</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(89,)]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT COUNT(DISTINCT customerid) AS unique_customers\n",
    "FROM orders"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ca92cba7",
   "metadata": {},
   "source": [
    "### Exercise 5\n",
    "\n",
    "What is the total price for each order? You need to multiply the unit price by the quantity and subtract the discount for each order detail, then sum all the order details for each order. Use the 'OrderDetails' table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "4342a0f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///Northwind.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>OrderID</th>\n",
       "            <th>total_price</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10248</td>\n",
       "            <td>440.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10249</td>\n",
       "            <td>1863.4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10250</td>\n",
       "            <td>1813.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10251</td>\n",
       "            <td>670.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10252</td>\n",
       "            <td>3730.0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10253</td>\n",
       "            <td>1444.8000000000002</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10254</td>\n",
       "            <td>625.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10255</td>\n",
       "            <td>2490.5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10256</td>\n",
       "            <td>517.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10257</td>\n",
       "            <td>1119.9</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10248, 440.0),\n",
       " (10249, 1863.4),\n",
       " (10250, 1813.0),\n",
       " (10251, 670.8),\n",
       " (10252, 3730.0),\n",
       " (10253, 1444.8000000000002),\n",
       " (10254, 625.2),\n",
       " (10255, 2490.5),\n",
       " (10256, 517.8),\n",
       " (10257, 1119.9)]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT\n",
    "  orderid,\n",
    "  SUM((unitprice * quantity) - discount) AS total_price\n",
    "FROM orderdetails\n",
    "GROUP BY orderid\n",
    "LIMIT 10"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "7a51093c",
   "metadata": {},
   "source": [
    "### Challenge question\n",
    "\n",
    "What are the 5 largest orders processed? (Hint: Use the 'OrderDetails' table.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "99479d5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///Northwind.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>OrderID</th>\n",
       "            <th>total_quantity</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10895</td>\n",
       "            <td>346</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>11030</td>\n",
       "            <td>330</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10847</td>\n",
       "            <td>288</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10515</td>\n",
       "            <td>286</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>10678</td>\n",
       "            <td>280</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10895, 346), (11030, 330), (10847, 288), (10515, 286), (10678, 280)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT orderid, \n",
    "  SUM(quantity) AS total_quantity\n",
    "FROM orderdetails\n",
    "GROUP BY orderid\n",
    "ORDER BY total_quantity DESC \n",
    "LIMIT 5"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b3c88335",
   "metadata": {},
   "source": [
    "## Solutions"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a5084b12",
   "metadata": {},
   "source": [
    "### Exercise 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c968d875",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    AVG(UnitPrice) \n",
    "FROM \n",
    "    Products;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "294d8eef",
   "metadata": {},
   "source": [
    "### Exercise 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03071e34",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    ProductID,\n",
    "    SUM(Quantity) as TotalQuantity\n",
    "FROM \n",
    "    OrderDetails\n",
    "GROUP BY \n",
    "    ProductID;\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "eafe40b6",
   "metadata": {},
   "source": [
    "### Exercise 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ffd2239",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    MAX(Freight) as MaxFreight,\n",
    "    MIN(Freight) as MinFreight,\n",
    "    AVG(Freight) as AvgFreight\n",
    "FROM \n",
    "    Orders;\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "e9956efe",
   "metadata": {},
   "source": [
    "### Exercise 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd662edc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    COUNT(DISTINCT CustomerID) \n",
    "FROM \n",
    "    Orders;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "dee904e7",
   "metadata": {},
   "source": [
    "### Exercise 5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ac14f515",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    OrderID, \n",
    "    SUM(UnitPrice * Quantity * (1 - Discount)) as TotalPrice \n",
    "FROM \n",
    "    OrderDetails\n",
    "GROUP BY \n",
    "    OrderID;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c1341a11",
   "metadata": {},
   "source": [
    "### Challenge question"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a0cf068",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT \n",
    "    OrderID, \n",
    "    SUM(Quantity) as TotalQuantity \n",
    "FROM \n",
    "    OrderDetails\n",
    "GROUP BY \n",
    "    OrderID\n",
    "ORDER BY \n",
    "    TotalQuantity DESC \n",
    "LIMIT 5;"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6d8d9bdd",
   "metadata": {},
   "source": [
    "<div align=\"center\" style=\" font-size: 80%; text-align: center; margin: 0 auto\">\n",
    "<img src=\"https://raw.githubusercontent.com/Explore-AI/Pictures/master/ExploreAI_logos/EAI_Blue_Dark.png\"  style=\"width:200px\";/>\n",
    "</div>"
   ]
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}