{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9090334b-1a86-4d41-8861-fb398f0ccd02",
   "metadata": {},
   "source": [
    "## Lecture 13 - Even more SQL\n",
    "\n",
    "**Announcements**\n",
    "\n",
    "  - No new lecture exercise today, Lex12 due on monday at midnight. \n",
    "  - Expect a new homework today or tomorrow with more complex SQL queries.\n",
    "\n",
    "\n",
    "### SQL pointers\n",
    "\n",
    "- There is more than one way to write a query. The performance of the query is determined by a combination of different factors.\n",
    "- Databases differ in how the data are stored and which indices are available. \n",
    "- Each query is sent to a **very powerful** query optimizer that can find the most efficient way to execute a query. The way you write the query may make it difficult for the optimizer to find some good execution methods.\n",
    "- DB servers differ in their implementation of query operations and how they keep track of the data statistics.\n",
    "- Database queries don't follow the basic programming logic of modularity. It is good to push some complexity to queries. The main point is figuring out when to break up a query to modules.   \n",
    "- **Wisdom from an older db programmer:** Learn to write a query in multiple ways. Check the expected and real costs. \n",
    "      \n",
    "```explain SQLquery; ```\n",
    "\n",
    "will show you the expected cost of a query in Postgresql."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "5c43b389-d103-4376-a39a-e42b65feda13",
   "metadata": {},
   "outputs": [],
   "source": [
    "load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "33105b25-fd75-4155-89cf-b53c57ef5c1e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Connecting to &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Connecting to 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%sql --section baking"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df25f2ea-8be4-469a-8f82-7a51159e229d",
   "metadata": {},
   "source": [
    "### Baker database\n",
    "\n",
    "Bakers(<u>baker</u>, fullname, age, occupation, hometown)  \n",
    "Episodes(<u>id</u>, title, firstaired, viewers7day, signature, technical, showstopper)  \n",
    "Favorites(<u>episodeid, baker</u>)  \n",
    "Results(<u>episodeid, baker</u>, result)  \n",
    "Showstoppers(<u>episodeid, baker</u>, make)  \n",
    "Signatures(<u>episodeid, baker</u>, make)  \n",
    "Technicals(<u>episodeid, baker</u>, rank)\n",
    "\n",
    "\n",
    "3. Find all bakers who have used an orange in the showstopper or\n",
    "signature challenge. Return the name of the baker and the number of\n",
    "episodes in which they competed in.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "17611775-26f1-4be6-afa6-d59f2e8457bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id</th>\n",
       "            <th>firstaired</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "            <td>2018-10-30</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+------------+\n",
       "| id | firstaired |\n",
       "+----+------------+\n",
       "| 10 | 2018-10-30 |\n",
       "+----+------------+"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- 1. Find the episodes in which no one was chosen to be a favorite.\n",
    "-- Return the episodeid and air date of the episode.\n",
    "\n",
    "-- (all episodes) except (episodes with favorites)\n",
    "\n",
    "select id, firstaired from episodes\n",
    "except\n",
    "select e.id, e.firstaired\n",
    "from favorites f, episodes e\n",
    "where f.episodeid = e.id;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "073cf18b-2fa9-41b7-b7ef-2c2f31faa3ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">2 rows affected.</span>"
      ],
      "text/plain": [
       "2 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>wheneliminated</th>\n",
       "            <th>numfav</th>\n",
       "            <th>numstar</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>9</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+----------------+--------+---------+\n",
       "| baker  | wheneliminated | numfav | numstar |\n",
       "+--------+----------------+--------+---------+\n",
       "| Briony |       9        |   2    |    1    |\n",
       "|  Dan   |       6        |   2    |    1    |\n",
       "+--------+----------------+--------+---------+"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "\n",
    "-- 2. For each baker, find the total number of shows in which they were a\n",
    "-- favorite, the number of shows in which they were 'star baker' and the\n",
    "-- episode id of the show in which they were eliminated.\n",
    "\n",
    "select\n",
    "    f.baker\n",
    "    , r2.episodeid as wheneliminated\n",
    "    , count(distinct f.episodeid) as numfav\n",
    "    , count(distinct r.episodeid) as numstar\n",
    "from\n",
    "    favorites f\n",
    "    , results r\n",
    "    , results r2\n",
    "where\n",
    "    f.baker = r.baker\n",
    "    and r.result = 'star baker'\n",
    "    and r2.baker = r.baker\n",
    "    and r2.result = 'eliminated'\n",
    "group by\n",
    "    f.baker\n",
    "    , r2.episodeid ;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2dfa67f5-b802-425b-a8e9-61075fec0a44",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">6 rows affected.</span>"
      ],
      "text/plain": [
       "6 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numepisodes</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+-------------+\n",
       "|  baker  | numepisodes |\n",
       "+---------+-------------+\n",
       "|  Antony |      3      |\n",
       "|  Briony |      9      |\n",
       "|   Dan   |      6      |\n",
       "| Kim-Joy |      10     |\n",
       "|  Rahul  |      10     |\n",
       "|   Ruby  |      10     |\n",
       "+---------+-------------+"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "    \n",
    "-- Find all bakers who have used an orange in the showstopper or\n",
    "-- signature challenge. Return the name of the baker and the number of\n",
    "-- episodes in which they competed in.\n",
    "\n",
    "select\n",
    "   s.baker\n",
    "   , count(distinct s2.episodeid) as numepisodes\n",
    "from\n",
    "   showstoppers s, showstoppers s2\n",
    "where\n",
    "   lower(s.make) like '%orange%'\n",
    "   and s.baker = s2.baker\n",
    "group by\n",
    "   s.baker \n",
    "union\n",
    "select\n",
    "   s.baker\n",
    "   , count(distinct s2.episodeid) as numepisodes\n",
    "from\n",
    "   signatures s, showstoppers s2\n",
    "where\n",
    "   lower(s.make) like '%orange%'\n",
    "   and s.baker = s2.baker\n",
    "group by\n",
    "   s.baker ;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4994273f-e190-462d-b256-7b994d8713df",
   "metadata": {},
   "source": [
    "Note you can write above query (3) using joins only as seen below, but this join is not very selective (it is mostly a Cartesian product) and as a result this query is grossly inefficient (use explain to see for yourself).\n",
    "\n",
    "```\n",
    "select\n",
    "   t.baker\n",
    "   , count(distinct t.episodeid) as numepisodes\n",
    "from\n",
    "   signatures s, showstoppers s2, technicals t\n",
    "where\n",
    "   (lower(s.make) like '%orange%' and s.baker = t.baker)\n",
    "   or (lower(s2.make) like '%orange%' and s2.baker = t.baker)\n",
    "group by\n",
    "   t.baker ;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41211419-9f32-4c73-9498-bc78143dd337",
   "metadata": {},
   "source": [
    "### OUTER JOIN vs. INNER JOIN \n",
    "\n",
    "```\n",
    "select baker from bakers ;\n",
    "\n",
    "select distinct baker from favorites;\n",
    "\n",
    "select distinct baker from results where result = 'star baker' ;\n",
    "```\n",
    "This query only returns bakers who were both star baker and were favorite.\n",
    "    \n",
    "```\n",
    "select distinct r.baker from favorites f, results r\n",
    "where r.baker = f.baker and r.result = 'star baker' ;\n",
    "```\n",
    "\n",
    "This query only returns bakers who were favorite.\n",
    "```\n",
    "select distinct b.baker, f.episodeid from bakers b, favorites f\n",
    "where f.baker = b.baker ;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b772627b-35a6-4322-be01-76d640ac6a80",
   "metadata": {},
   "source": [
    "![Outer Join](outer_join.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "f712613e-d860-4c91-a56f-b7dc6fcf7cfc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">18 rows affected.</span>"
      ],
      "text/plain": [
       "18 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>episodeid</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>3</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>None</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>9</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+---------+-----------+\n",
       "|  baker  | episodeid |\n",
       "+---------+-----------+\n",
       "|  Antony |    None   |\n",
       "|  Briony |     1     |\n",
       "|  Briony |     8     |\n",
       "|   Dan   |     2     |\n",
       "|   Dan   |     3     |\n",
       "|  Imelda |    None   |\n",
       "|   Jon   |     2     |\n",
       "|   Jon   |     4     |\n",
       "|  Karen  |    None   |\n",
       "| Kim-Joy |     9     |\n",
       "+---------+-----------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "-- For each baker, find all episodes in which they were a favorite\n",
    "\n",
    "select distinct\n",
    "   b.baker\n",
    "   , f.episodeid\n",
    "from\n",
    "   bakers b left join\n",
    "   favorites f  on f.baker = b.baker\n",
    "order by b.baker\n",
    ";\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a4f7ac06-e462-40bb-9ec2-032112656101",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">12 rows affected.</span>"
      ],
      "text/plain": [
       "12 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numfav</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>4</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+--------+--------+\n",
       "| baker  | numfav |\n",
       "+--------+--------+\n",
       "| Rahul  |   4    |\n",
       "| Imelda |   0    |\n",
       "|  Jon   |   2    |\n",
       "| Karen  |   0    |\n",
       "|  Ruby  |   1    |\n",
       "|  Luke  |   0    |\n",
       "|  Dan   |   2    |\n",
       "| Antony |   0    |\n",
       "| Manon  |   0    |\n",
       "| Briony |   2    |\n",
       "+--------+--------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "-- For each baker, find all episodes in which they were a favorite\n",
    "\n",
    "\n",
    "select \n",
    "   b.baker\n",
    "   , count(f.episodeid) as numfav\n",
    "from\n",
    "   bakers b left join\n",
    "   favorites f  on f.baker = b.baker\n",
    "group by b.baker\n",
    ";\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e3edf4b3-0d93-4349-896b-ec18f559e4c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">12 rows affected.</span>"
      ],
      "text/plain": [
       "12 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numep</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+--------+-------+\n",
       "| baker  | numep |\n",
       "+--------+-------+\n",
       "| Rahul  |   2   |\n",
       "| Imelda |   0   |\n",
       "|  Jon   |   0   |\n",
       "| Karen  |   0   |\n",
       "|  Ruby  |   2   |\n",
       "|  Luke  |   0   |\n",
       "|  Dan   |   1   |\n",
       "| Antony |   0   |\n",
       "| Manon  |   1   |\n",
       "| Briony |   1   |\n",
       "+--------+-------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "-- For each baker, find the number of episodes in which\n",
    "-- they were a star baker.\n",
    "\n",
    "-- (bakers)  left join/\n",
    "--          keep bakers even if they don't participate in the join\n",
    "-- (bakers who were star baker)\t  \n",
    "\n",
    "\n",
    "select\n",
    "   b.baker\n",
    "   , count(r.episodeid) as numep\n",
    "from\n",
    "   bakers b left join\n",
    "   results r on b.baker = r.baker and r.result = 'star baker'\n",
    "group by\n",
    "   b.baker ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "963164dd-3f42-438c-a0ef-9b3b0f1bc1d8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">12 rows affected.</span>"
      ],
      "text/plain": [
       "12 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>wheneliminated</th>\n",
       "            <th>numfav</th>\n",
       "            <th>numstar</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>3</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>9</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>7</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>5</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>None</td>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>2</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "            <td>8</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>None</td>\n",
       "            <td>4</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+---------+----------------+--------+---------+\n",
       "|  baker  | wheneliminated | numfav | numstar |\n",
       "+---------+----------------+--------+---------+\n",
       "|  Antony |       3        |   0    |    0    |\n",
       "|  Briony |       9        |   2    |    1    |\n",
       "|   Dan   |       6        |   2    |    1    |\n",
       "|  Imelda |       1        |   0    |    0    |\n",
       "|   Jon   |       7        |   2    |    0    |\n",
       "|  Karen  |       5        |   0    |    0    |\n",
       "| Kim-Joy |      None      |   1    |    2    |\n",
       "|   Luke  |       2        |   0    |    0    |\n",
       "|  Manon  |       8        |   0    |    1    |\n",
       "|  Rahul  |      None      |   4    |    2    |\n",
       "+---------+----------------+--------+---------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "-- 2. For each baker, find the total number of shows in which they were a\n",
    "-- favorite, the number of shows in which they were 'star baker' and the\n",
    "-- episode id of the show in which they were eliminated.\n",
    "\n",
    "\n",
    "select\n",
    "    b.baker\n",
    "    , r2.episodeid as wheneliminated\n",
    "    , count(distinct f.episodeid) as numfav\n",
    "    , count(distinct r.episodeid) as numstar\n",
    "from\n",
    "    bakers b\n",
    "    left join favorites f on f.baker = b.baker\n",
    "    left join results r on b.baker = r.baker and r.result = 'star baker'\n",
    "    left join results r2 on b.baker = r2.baker and r2.result = 'eliminated'\n",
    "group by\n",
    "    b.baker\n",
    "    , r2.episodeid\n",
    "order by\n",
    "    b.baker asc;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65015d69-e5b5-4a03-87d2-aa94ca3489bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "-- 1. Find the episodes in which no one was chosen to be a favorite.\n",
    "-- Return the episodeid and air date of the episode.\n",
    "\n",
    "-- You can now do set subtraction using left join\n",
    "\n",
    "select\n",
    "   e.id\n",
    "   , e.firstaired\n",
    "from\n",
    "   episodes e\n",
    "   left join favorites f on e.id = f.episodeid\n",
    "where\n",
    "   f.episodeid is null;\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "57fe70cd-2e23-4ca0-b09d-5bb1a539ef1a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">2 rows affected.</span>"
      ],
      "text/plain": [
       "2 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>wheneliminated</th>\n",
       "            <th>numfav</th>\n",
       "            <th>numstar</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>9</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>6</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+--------+----------------+--------+---------+\n",
       "| baker  | wheneliminated | numfav | numstar |\n",
       "+--------+----------------+--------+---------+\n",
       "| Briony |       9        |   2    |    1    |\n",
       "|  Dan   |       6        |   2    |    1    |\n",
       "+--------+----------------+--------+---------+"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "    \n",
    "-- For inner join, using join in from or using in WHERE\n",
    "-- are identical logically and for execution costs    \n",
    "\n",
    "select\n",
    "    f.baker\n",
    "    , r2.episodeid as wheneliminated\n",
    "    , count(distinct f.episodeid) as numfav\n",
    "    , count(distinct r.episodeid) as numstar\n",
    "from\n",
    "    favorites f\n",
    "       join results r on f.baker = r.baker\n",
    "       join results r2 on r2.baker = r.baker\n",
    "where\n",
    "    r.result = 'star baker'\n",
    "    and r2.result = 'eliminated'\n",
    "group by\n",
    "    f.baker\n",
    "    , r2.episodeid ;\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "15e6d975-dc77-4e64-8a41-1dd274d3128d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">12 rows affected.</span>"
      ],
      "text/plain": [
       "12 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "            <th>numep</th>\n",
       "            <th>besttechnical</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Antony</td>\n",
       "            <td>0</td>\n",
       "            <td>8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Briony</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Dan</td>\n",
       "            <td>1</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Imelda</td>\n",
       "            <td>0</td>\n",
       "            <td>11</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Jon</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Karen</td>\n",
       "            <td>0</td>\n",
       "            <td>5</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Luke</td>\n",
       "            <td>0</td>\n",
       "            <td>7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Manon</td>\n",
       "            <td>1</td>\n",
       "            <td>2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "            <td>2</td>\n",
       "            <td>1</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">Truncated to <a href=\"https://jupysql.ploomber.io/en/latest/api/configuration.html#displaylimit\">displaylimit</a> of 10.</span>"
      ],
      "text/plain": [
       "+---------+-------+---------------+\n",
       "|  baker  | numep | besttechnical |\n",
       "+---------+-------+---------------+\n",
       "|  Antony |   0   |       8       |\n",
       "|  Briony |   1   |       1       |\n",
       "|   Dan   |   1   |       1       |\n",
       "|  Imelda |   0   |       11      |\n",
       "|   Jon   |   0   |       1       |\n",
       "|  Karen  |   0   |       5       |\n",
       "| Kim-Joy |   2   |       1       |\n",
       "|   Luke  |   0   |       7       |\n",
       "|  Manon  |   1   |       2       |\n",
       "|  Rahul  |   2   |       1       |\n",
       "+---------+-------+---------------+\n",
       "Truncated to displaylimit of 10."
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "    \n",
    "select\n",
    "   b.baker\n",
    "   , count(distinct r.episodeid) as numep\n",
    "   , min(t.rank) as besttechnical\n",
    "from\n",
    "   bakers b left join\n",
    "   results r on b.baker = r.baker and r.result = 'star baker'\n",
    "   , technicals t\n",
    "where\n",
    "   t.baker = b.baker\n",
    "group by\n",
    "   b.baker ;\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9cf3c82-4978-4c4b-b60a-128710e93340",
   "metadata": {},
   "source": [
    "### Advanced SQL\n",
    "\n",
    "Queries may return:\n",
    "\n",
    "  - Multiple tuples - treat it like a relation/table in the database\n",
    "  - Single number - treat the query like a number (Scalar queries)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cf2391e2-84e0-4cbb-8031-6e7f65de1416",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">3 rows affected.</span>"
      ],
      "text/plain": [
       "3 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>baker</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Rahul</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Ruby</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Kim-Joy</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+---------+\n",
       "|  baker  |\n",
       "+---------+\n",
       "|  Rahul  |\n",
       "|   Ruby  |\n",
       "| Kim-Joy |\n",
       "+---------+"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "    \n",
    "-- Find all bakers who won star baker more times than 'Briony'\n",
    "\n",
    "select\n",
    "   baker\n",
    "from\n",
    "   results\n",
    "where\n",
    "   result = 'star baker'\n",
    "group by\n",
    "   baker\n",
    "having\n",
    "   count(*) > (select count(*) from results\n",
    "               where result = 'star baker'\n",
    "\t             and baker = 'Briony');\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "17a0a46b-444f-449e-b157-15afaec2001e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<span style=\"None\">Running query in &#x27;baking&#x27;</span>"
      ],
      "text/plain": [
       "Running query in 'baking'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<span style=\"color: green\">1 rows affected.</span>"
      ],
      "text/plain": [
       "1 rows affected."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "+----+\n",
       "| id |\n",
       "+----+\n",
       "| 10 |\n",
       "+----+"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "\n",
    "-- Find all episodes after the episode that 'Briony'\n",
    "-- was eliminated in.\n",
    "\n",
    "select\n",
    "   e.id\n",
    "from\n",
    "   episodes e\n",
    "where\n",
    "   e.id > (select episodeid from results\n",
    "           where baker = 'Briony' and result = 'eliminated')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67f207e0-8bff-4b18-98df-bcd8c3b35b1d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.13.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
