solution_sql.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ed1e090e",
   "metadata": {},
   "source": [
    "# SQL Murder Mystery\n",
    "\n",
    "A crime has taken place and the detective needs your help. The detective gave\n",
    "you the crime scene report, but you somehow lost it.\n",
    "\n",
    "## Clue 1\n",
    "\n",
    "You vaguely remember that the crime was a **murder** that occurred sometime on\n",
    "**Jan. 15, 2018** and that it took place in **SQL City**. Start by retrieving\n",
    "the corresponding crime scene report from the police department's database. If\n",
    "you want to get the most out of this mystery, try to work through it only\n",
    "using your SQL environment and refrain from using a notepad."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c3d38b83",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.783230Z",
     "iopub.status.busy": "2023-03-17T14:56:53.783083Z",
     "iopub.status.idle": "2023-03-17T14:56:53.788977Z",
     "shell.execute_reply": "2023-03-17T14:56:53.788599Z"
    }
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "import pandas as pd\n",
    "from IPython.display import Markdown, display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6d5f872a",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.790619Z",
     "iopub.status.busy": "2023-03-17T14:56:53.790491Z",
     "iopub.status.idle": "2023-03-17T14:56:53.792611Z",
     "shell.execute_reply": "2023-03-17T14:56:53.792323Z"
    }
   },
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(\"sql-murder-mystery.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "57b54224",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.794058Z",
     "iopub.status.busy": "2023-03-17T14:56:53.793934Z",
     "iopub.status.idle": "2023-03-17T14:56:53.796023Z",
     "shell.execute_reply": "2023-03-17T14:56:53.795714Z"
    }
   },
   "outputs": [],
   "source": [
    "def lazy_avoid_typing_conn(query: str) -> pd.DataFrame:\n",
    "    return pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a158441f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.797447Z",
     "iopub.status.busy": "2023-03-17T14:56:53.797335Z",
     "iopub.status.idle": "2023-03-17T14:56:53.806967Z",
     "shell.execute_reply": "2023-03-17T14:56:53.806685Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>membership_id</th>\n",
       "      <th>check_in_date</th>\n",
       "      <th>check_in_time</th>\n",
       "      <th>check_out_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NL318</td>\n",
       "      <td>20180212</td>\n",
       "      <td>329</td>\n",
       "      <td>365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NL318</td>\n",
       "      <td>20170811</td>\n",
       "      <td>469</td>\n",
       "      <td>920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NL318</td>\n",
       "      <td>20180429</td>\n",
       "      <td>506</td>\n",
       "      <td>554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NL318</td>\n",
       "      <td>20180128</td>\n",
       "      <td>124</td>\n",
       "      <td>759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NL318</td>\n",
       "      <td>20171027</td>\n",
       "      <td>418</td>\n",
       "      <td>1019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>I0UIP</td>\n",
       "      <td>20170124</td>\n",
       "      <td>687</td>\n",
       "      <td>1011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>I0UIP</td>\n",
       "      <td>20170323</td>\n",
       "      <td>670</td>\n",
       "      <td>1184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>I0UIP</td>\n",
       "      <td>20170326</td>\n",
       "      <td>620</td>\n",
       "      <td>1131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>I0UIP</td>\n",
       "      <td>20171027</td>\n",
       "      <td>121</td>\n",
       "      <td>368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>I0UIP</td>\n",
       "      <td>20170424</td>\n",
       "      <td>1023</td>\n",
       "      <td>1145</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    membership_id  check_in_date  check_in_time  check_out_time\n",
       "0           NL318       20180212            329             365\n",
       "1           NL318       20170811            469             920\n",
       "2           NL318       20180429            506             554\n",
       "3           NL318       20180128            124             759\n",
       "4           NL318       20171027            418            1019\n",
       "..            ...            ...            ...             ...\n",
       "995         I0UIP       20170124            687            1011\n",
       "996         I0UIP       20170323            670            1184\n",
       "997         I0UIP       20170326            620            1131\n",
       "998         I0UIP       20171027            121             368\n",
       "999         I0UIP       20170424           1023            1145\n",
       "\n",
       "[1000 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn(\n",
    "    \"\"\"\n",
    "SELECT \"membership_id\",\n",
    "       \"check_in_date\",\n",
    "       \"check_in_time\",\n",
    "       \"check_out_time\"\n",
    "FROM   \"get_fit_now_check_in\"\n",
    "LIMIT  1000 \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "939a7925",
   "metadata": {},
   "source": [
    "![image.png](https://mystery.knightlab.com/schema.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "31066f47",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.808419Z",
     "iopub.status.busy": "2023-03-17T14:56:53.808297Z",
     "iopub.status.idle": "2023-03-17T14:56:53.811402Z",
     "shell.execute_reply": "2023-03-17T14:56:53.811129Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Security footage shows that there were 2 witnesses. The first witness lives at the last house on \"Northwestern Dr\". The second witness, named Annabel, lives somewhere on \"Franklin Ave\".'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn(\n",
    "    '''SELECT \"description\"\n",
    "FROM   \"crime_scene_report\"\n",
    "WHERE  date = 20180115\n",
    "       AND type = \"murder\"\n",
    "       AND city = \"SQL City\"'''\n",
    ")[\"description\"].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cdc3dd91",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.812731Z",
     "iopub.status.busy": "2023-03-17T14:56:53.812604Z",
     "iopub.status.idle": "2023-03-17T14:56:53.816067Z",
     "shell.execute_reply": "2023-03-17T14:56:53.815766Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "## Clue 2"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "Security footage shows that there were 2 witnesses. The first witness lives at the last house on \"Northwestern Dr\". The second witness, named Annabel, lives somewhere on \"Franklin Ave\"."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(Markdown(\"## Clue 2\"))\n",
    "display(\n",
    "    Markdown(\n",
    "        lazy_avoid_typing_conn(\n",
    "            '''SELECT \"description\"\n",
    "FROM   \"crime_scene_report\"\n",
    "WHERE  date = 20180115\n",
    "       AND type = \"murder\"\n",
    "       AND city = \"SQL City\"'''\n",
    "        )[\"description\"].iloc[0]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "85ffbe5c",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.817408Z",
     "iopub.status.busy": "2023-03-17T14:56:53.817305Z",
     "iopub.status.idle": "2023-03-17T14:56:53.822181Z",
     "shell.execute_reply": "2023-03-17T14:56:53.821912Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>license_id</th>\n",
       "      <th>address_number</th>\n",
       "      <th>address_street_name</th>\n",
       "      <th>ssn</th>\n",
       "      <th>last_house</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14887</td>\n",
       "      <td>Morty Schapiro</td>\n",
       "      <td>118009</td>\n",
       "      <td>4919</td>\n",
       "      <td>Northwestern Dr</td>\n",
       "      <td>111564949</td>\n",
       "      <td>4919</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            name  license_id  address_number address_street_name  \\\n",
       "0  14887  Morty Schapiro      118009            4919     Northwestern Dr   \n",
       "\n",
       "         ssn  last_house  \n",
       "0  111564949        4919  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn(\n",
    "    '''SELECT\n",
    "    \"id\", \n",
    "    \"name\", \n",
    "    \"license_id\", \n",
    "    \"address_number\", \n",
    "    \"address_street_name\", \n",
    "    \"ssn\",\n",
    "    MAX(\"address_number\") AS last_house\n",
    "FROM \"person\"\n",
    "WHERE \"address_street_name\" = \"Northwestern Dr\"'''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "8a8f52aa",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.823465Z",
     "iopub.status.busy": "2023-03-17T14:56:53.823356Z",
     "iopub.status.idle": "2023-03-17T14:56:53.828259Z",
     "shell.execute_reply": "2023-03-17T14:56:53.827992Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>license_id</th>\n",
       "      <th>address_number</th>\n",
       "      <th>address_street_name</th>\n",
       "      <th>ssn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16371</td>\n",
       "      <td>Annabel Miller</td>\n",
       "      <td>490173</td>\n",
       "      <td>103</td>\n",
       "      <td>Franklin Ave</td>\n",
       "      <td>318771143</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id            name  license_id  address_number address_street_name  \\\n",
       "0  16371  Annabel Miller      490173             103        Franklin Ave   \n",
       "\n",
       "         ssn  \n",
       "0  318771143  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn(\n",
    "    '''SELECT\n",
    "    \"id\", \n",
    "    \"name\", \n",
    "    \"license_id\", \n",
    "    \"address_number\", \n",
    "    \"address_street_name\", \n",
    "    \"ssn\"\n",
    "FROM \"person\"\n",
    "WHERE \"address_street_name\" = \"Franklin Ave\" AND \"name\" LIKE \"Annabel%\"'''\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f849817d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.829615Z",
     "iopub.status.busy": "2023-03-17T14:56:53.829509Z",
     "iopub.status.idle": "2023-03-17T14:56:53.834507Z",
     "shell.execute_reply": "2023-03-17T14:56:53.834240Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "## Clue 3"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "I heard a gunshot and then saw a man run out. He had a \"Get Fit Now Gym\" bag. The membership number on the bag started with \"48Z\". Only gold members have those bags. The man got into a car with a plate that included \"H42W\"."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "text/markdown": [
       "I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th."
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "display(Markdown(\"## Clue 3\"))\n",
    "for i in range(2):\n",
    "    display(\n",
    "        Markdown(\n",
    "            lazy_avoid_typing_conn(\n",
    "                \"\"\"SELECT \"person_id\",\n",
    "           \"transcript\"\n",
    "    FROM   \"interview\"\n",
    "    WHERE  person_id = 14887\n",
    "            OR person_id = 16371\"\"\"\n",
    "            )[\"transcript\"].iloc[i]\n",
    "        )\n",
    "    )\n",
    "    print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7ae248ac",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.835663Z",
     "iopub.status.busy": "2023-03-17T14:56:53.835577Z",
     "iopub.status.idle": "2023-03-17T14:56:53.841591Z",
     "shell.execute_reply": "2023-03-17T14:56:53.841336Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>person_id</th>\n",
       "      <th>name</th>\n",
       "      <th>membership_start_date</th>\n",
       "      <th>membership_status</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>license_id</th>\n",
       "      <th>address_number</th>\n",
       "      <th>address_street_name</th>\n",
       "      <th>ssn</th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>eye_color</th>\n",
       "      <th>hair_color</th>\n",
       "      <th>gender</th>\n",
       "      <th>plate_number</th>\n",
       "      <th>car_make</th>\n",
       "      <th>car_model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48Z55</td>\n",
       "      <td>67318</td>\n",
       "      <td>Jeremy Bowers</td>\n",
       "      <td>20160101</td>\n",
       "      <td>gold</td>\n",
       "      <td>67318</td>\n",
       "      <td>Jeremy Bowers</td>\n",
       "      <td>423327</td>\n",
       "      <td>530</td>\n",
       "      <td>Washington Pl, Apt 3A</td>\n",
       "      <td>871539279</td>\n",
       "      <td>423327</td>\n",
       "      <td>30</td>\n",
       "      <td>70</td>\n",
       "      <td>brown</td>\n",
       "      <td>brown</td>\n",
       "      <td>male</td>\n",
       "      <td>0H42W2</td>\n",
       "      <td>Chevrolet</td>\n",
       "      <td>Spark LS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id  person_id           name  membership_start_date membership_status  \\\n",
       "0  48Z55      67318  Jeremy Bowers               20160101              gold   \n",
       "\n",
       "      id           name  license_id  address_number    address_street_name  \\\n",
       "0  67318  Jeremy Bowers      423327             530  Washington Pl, Apt 3A   \n",
       "\n",
       "         ssn      id  age  height eye_color hair_color gender plate_number  \\\n",
       "0  871539279  423327   30      70     brown      brown   male       0H42W2   \n",
       "\n",
       "    car_make car_model  \n",
       "0  Chevrolet  Spark LS  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn(\n",
    "    \"\"\"SELECT *\n",
    "FROM   get_fit_now_member\n",
    "       INNER JOIN person\n",
    "               ON get_fit_now_member.person_id = person.id\n",
    "       INNER JOIN drivers_license\n",
    "               ON person.license_id = drivers_license.id\n",
    "WHERE  get_fit_now_member.membership_status = 'gold'\n",
    "       AND get_fit_now_member.id LIKE '48Z%'\n",
    "       AND drivers_license.plate_number LIKE '%H42W%' \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "98fe382d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.842732Z",
     "iopub.status.busy": "2023-03-17T14:56:53.842644Z",
     "iopub.status.idle": "2023-03-17T14:56:53.845800Z",
     "shell.execute_reply": "2023-03-17T14:56:53.845542Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/markdown": [
       "## Clue 4"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5\" (65\") or 5'7\" (67\"). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.\n"
      ],
      "text/plain": [
       "<IPython.core.display.Markdown object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(Markdown(\"## Clue 4\"))\n",
    "display(\n",
    "    Markdown(\n",
    "        lazy_avoid_typing_conn(\n",
    "            \"\"\"SELECT \"transcript\"\n",
    "FROM   \"interview\"\n",
    "WHERE  \"person_id\" = 67318;  \"\"\"\n",
    "        )[\"transcript\"].iloc[0]\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c156394e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.846995Z",
     "iopub.status.busy": "2023-03-17T14:56:53.846887Z",
     "iopub.status.idle": "2023-03-17T14:56:53.853984Z",
     "shell.execute_reply": "2023-03-17T14:56:53.853721Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>age</th>\n",
       "      <th>height</th>\n",
       "      <th>eye_color</th>\n",
       "      <th>hair_color</th>\n",
       "      <th>gender</th>\n",
       "      <th>plate_number</th>\n",
       "      <th>car_make</th>\n",
       "      <th>car_model</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>license_id</th>\n",
       "      <th>address_number</th>\n",
       "      <th>address_street_name</th>\n",
       "      <th>ssn</th>\n",
       "      <th>person_id</th>\n",
       "      <th>event_id</th>\n",
       "      <th>event_name</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>202298</td>\n",
       "      <td>68</td>\n",
       "      <td>66</td>\n",
       "      <td>green</td>\n",
       "      <td>red</td>\n",
       "      <td>female</td>\n",
       "      <td>500123</td>\n",
       "      <td>Tesla</td>\n",
       "      <td>Model S</td>\n",
       "      <td>99716</td>\n",
       "      <td>Miranda Priestly</td>\n",
       "      <td>202298</td>\n",
       "      <td>1883</td>\n",
       "      <td>Golden Ave</td>\n",
       "      <td>987756388</td>\n",
       "      <td>99716</td>\n",
       "      <td>1143</td>\n",
       "      <td>SQL Symphony Concert</td>\n",
       "      <td>20171206</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id  age  height eye_color hair_color  gender plate_number car_make  \\\n",
       "0  202298   68      66     green        red  female       500123    Tesla   \n",
       "\n",
       "  car_model     id              name  license_id  address_number  \\\n",
       "0   Model S  99716  Miranda Priestly      202298            1883   \n",
       "\n",
       "  address_street_name        ssn  person_id  event_id            event_name  \\\n",
       "0          Golden Ave  987756388      99716      1143  SQL Symphony Concert   \n",
       "\n",
       "       date  \n",
       "0  20171206  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn('''SELECT *\n",
    "FROM \n",
    "  drivers_license\n",
    "INNER JOIN person\n",
    "ON drivers_license.id = person.license_id\n",
    "INNER JOIN facebook_event_checkin\n",
    "ON person.id = facebook_event_checkin.person_id\n",
    "WHERE \n",
    "  drivers_license.height BETWEEN 65 \n",
    "  AND 67 \n",
    "  AND drivers_license.car_make = \"Tesla\" \n",
    "  AND drivers_license.hair_color = \"red\" \n",
    "  AND drivers_license.gender = \"female\"\n",
    "  AND event_name = \"SQL Symphony Concert\"\n",
    "GROUP BY person.id''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "25b00993",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.855101Z",
     "iopub.status.busy": "2023-03-17T14:56:53.855016Z",
     "iopub.status.idle": "2023-03-17T14:56:53.858692Z",
     "shell.execute_reply": "2023-03-17T14:56:53.858456Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>license_id</th>\n",
       "      <th>address_number</th>\n",
       "      <th>address_street_name</th>\n",
       "      <th>ssn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>99716</td>\n",
       "      <td>Miranda Priestly</td>\n",
       "      <td>202298</td>\n",
       "      <td>1883</td>\n",
       "      <td>Golden Ave</td>\n",
       "      <td>987756388</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id              name  license_id  address_number address_street_name  \\\n",
       "0  99716  Miranda Priestly      202298            1883          Golden Ave   \n",
       "\n",
       "         ssn  \n",
       "0  987756388  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lazy_avoid_typing_conn(\"SELECT * FROM person WHERE id = 99716\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "cd02a30e",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-03-17T14:56:53.859786Z",
     "iopub.status.busy": "2023-03-17T14:56:53.859695Z",
     "iopub.status.idle": "2023-03-17T14:56:53.874163Z",
     "shell.execute_reply": "2023-03-17T14:56:53.873870Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "sqlite3 sql-murder-mystery.db \"INSERT INTO solution VALUES (1, 'Miranda Priestly');SELECT value FROM solution;\""
   ]
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  },
  "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}