Lara_Samuelson_ChatData_Project4_1-24.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ChatData\n",
    "\n",
    "## Sqlite\n",
    "We are using a file system called **sqlite**. It looks and acts like a real single user relational database (RDB). sqlite3 comes packaged with python. You do not need to install the library. You simply import it (as below).\n",
    "\n",
    "- We are using Pandas to write and read to sqlite. Pandas will manage a lot of the complexity of dealing with a RDB. There are other ways of reading and writing to a RDB that are VERY common and often used in production systems.  \n",
    "\n",
    "- The most common other way is to read or write to the RDB row by row. As you can see Pandas puts the entire dataframe into the RDB or extracts a new dataframe from the RDB. These are not row by row operations. These are set operations. Set operations ARE more efficient. However, it is common to use row by row operations to avoid needing large memory computers to hold the dataframes. One row only takes a few bytes. An entire dataframe could be many gigabytes and even petabytes of data. Obviously you will end up with problems with your compute resource if your files are this big. This will not happen to you in this activity and may never happen to you while you are a data analyst. Please just be aware of this. \n",
    "\n",
    "- This activity is primarily about querying a RDB, so, we are using a simple way that Pandas provides. If you want to learn the more complex way, just google 'reading and wrting to a relational database using python'. There are many resources to learn from.\n",
    "\n",
    "- The data types that sqlite supports are quite limited. for example, it does not have a DATE type.  This is not a challenge for this project. However, more sophisticated database systems,such as Postgresql, have a large array of data types, such as \"DATE\", etc. that give those systems additional capability. \n",
    "\n",
    "- All of the SQL queries could also be performed on the Pandas DataFrames directly.  You may want to try this yourself for comparison (but make sure you do the SQL queries first, as this is an exercise in using SQL!).\n",
    "\n",
    "\n",
    "## SQL Magic\n",
    "Within the Jupyter notebook we will be using something called **SQL Magic**.  This provides a convenient way to write SQL queries directly into code cells in the notebook and to read the results back into a Pandas DataFrame.  This makes working with SQL much easier!\n",
    "\n",
    "Note that you may need to update your sqlite version using `conda update -c anaconda sqlite` in order for the SQL Magic to work correctly.\n",
    "\n",
    "## The Data Analysis Lifecycle\n",
    "The sections in this notebook follow the stages of the Data Analysis Lifecycle introduced in an earlier activity.  The stages are:\n",
    "\n",
    "- Acquire\n",
    "- Transform\n",
    "- Organise\n",
    "- Analyse\n",
    "- Communicate\n",
    "- Maintain\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preliminary Steps: Create a Database\n",
    "First let's import Sqlite and the other libraries we will need."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:34:57.441084300Z",
     "start_time": "2024-01-14T16:34:56.126027900Z"
    }
   },
   "outputs": [],
   "source": [
    "# Import Libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import sqlite3\n",
    "import requests\n",
    "from io import BytesIO\n",
    "from zipfile import ZipFile"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create the Database\n",
    "Now we will create the Sqlite database.  Here is some code that does this for you.  We use the `%load_ext` magic command to load the SQL Magic extension and then use `%sql` to connect to the database."
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "The requirements document for this project is Template SQL queries.xlsx."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:34:57.459348700Z",
     "start_time": "2024-01-14T16:34:56.988975600Z"
    }
   },
   "outputs": [],
   "source": [
    "# If the db does not exist, sqlite will create it.\n",
    "con = sqlite3.connect('chatdata.db')\n",
    "\n",
    "# loads sql magic\n",
    "%load_ext sql \n",
    "\n",
    "# connects sql magic command to the correct db\n",
    "%sql sqlite:///chatdata.db "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Drop the `queries` table if it already exists\n",
    "The queries table will be our record of the queries created to answer the questions from the requirements spreadsheet.  As we will be running this Jupyter notebook a few times, let's drop (i.e. remove) the queries table so that we start fresh each time.  Here is the code to do this.  We use the `%%sql` magic command to tell Jupyter that we are going to write SQL in the cell.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:34:57.459348700Z",
     "start_time": "2024-01-14T16:34:57.211668200Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS 'queries'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 1: Load the Data\n",
    "Now we can start loading the data.  The tables will be created as we load the data.  \n",
    "\n",
    "Note that some of these files are quite large, so make sure you have plenty of free memory!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lifecycle Stages: Transform and Organise\n",
    "The data has already been processed into 3 clean data files ready for this project:\n",
    "\n",
    "- queries.csv\n",
    "- posts.csv\n",
    "- comments.csv\n",
    "\n",
    "We will load these files into our database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lifecycle Stage: Acquire\n",
    "The data can be found in the OpenClassrooms instructions for this activity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Comments Data into a `comments` table\n",
    "Now we will load the data from the csv files into our sqlite database.\n",
    "\n",
    "First we load the csv file into a Pandas dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [],
   "source": [
    "zip_file_url = \"https://s3.eu-west-1.amazonaws.com/course.oc-static.com/projects/DAN_UK_App_P5/chatdata.zip\"\n",
    "response = requests.get(zip_file_url)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:01.473563400Z",
     "start_time": "2024-01-14T16:34:57.238462Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:01.725132700Z",
     "start_time": "2024-01-14T16:35:01.473563400Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Id  PostId  Score                                               Text  \\\n",
      "0  723182  385124      0  @BenBolker I don't understand. The fit cannot ...   \n",
      "1  723183  385124      3  You can't add *less* than (`-min(y)`), but you...   \n",
      "2  723186  385137      0  nice. If you felt like doing the work it would...   \n",
      "3  723187  385137      0  i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...   \n",
      "4  723188  385134      0  Don't you mean \"so variance should be $\\sigma^...   \n",
      "\n",
      "          CreationDate  UserId  \n",
      "0  2019-01-01 00:06:39   78575  \n",
      "1  2019-01-01 00:09:22    2126  \n",
      "2  2019-01-01 00:32:11    2126  \n",
      "3  2019-01-01 00:40:36    2126  \n",
      "4  2019-01-01 00:41:28  112141  \n"
     ]
    }
   ],
   "source": [
    "if response.status_code == 200:\n",
    "    with ZipFile(BytesIO(response.content)) as z:\n",
    "        comments = pd.read_csv(z.open('comments.csv'))\n",
    "        print(comments.head())\n",
    "else:\n",
    "    print(f\"Failed to fetch the zip file. Status code: {response.status_code}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now take the comments dataframe and push the data into the Sqlite database table called 'comments':"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:02.605524700Z",
     "start_time": "2024-01-14T16:35:01.725132700Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostId  Score                                               Text  \\\n0  723182  385124      0  @BenBolker I don't understand. The fit cannot ...   \n1  723183  385124      3  You can't add *less* than (`-min(y)`), but you...   \n2  723186  385137      0  nice. If you felt like doing the work it would...   \n3  723187  385137      0  i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...   \n4  723188  385134      0  Don't you mean \"so variance should be $\\sigma^...   \n\n          CreationDate  UserId  \n0  2019-01-01 00:06:39   78575  \n1  2019-01-01 00:09:22    2126  \n2  2019-01-01 00:32:11    2126  \n3  2019-01-01 00:40:36    2126  \n4  2019-01-01 00:41:28  112141  ",
      "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>PostId</th>\n      <th>Score</th>\n      <th>Text</th>\n      <th>CreationDate</th>\n      <th>UserId</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>723182</td>\n      <td>385124</td>\n      <td>0</td>\n      <td>@BenBolker I don't understand. The fit cannot ...</td>\n      <td>2019-01-01 00:06:39</td>\n      <td>78575</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>723183</td>\n      <td>385124</td>\n      <td>3</td>\n      <td>You can't add *less* than (`-min(y)`), but you...</td>\n      <td>2019-01-01 00:09:22</td>\n      <td>2126</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>723186</td>\n      <td>385137</td>\n      <td>0</td>\n      <td>nice. If you felt like doing the work it would...</td>\n      <td>2019-01-01 00:32:11</td>\n      <td>2126</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>723187</td>\n      <td>385137</td>\n      <td>0</td>\n      <td>i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...</td>\n      <td>2019-01-01 00:40:36</td>\n      <td>2126</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>723188</td>\n      <td>385134</td>\n      <td>0</td>\n      <td>Don't you mean \"so variance should be $\\sigma^...</td>\n      <td>2019-01-01 00:41:28</td>\n      <td>112141</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load comments into sqlite\n",
    "comments.to_sql('comments', con, if_exists='replace', index=False)\n",
    "\n",
    "# read back in to prove that it worked\n",
    "sql = 'SELECT * FROM comments'\n",
    "comments = pd.read_sql(sql, con)\n",
    "comments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load Other CSVs\n",
    "Now that you have seen the code for loading in the comments.csv now do the same to read and write the posts.csv and users.csv to sqlite. \n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Enter code in the following cells. Insert as many cells as you want to do this.\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:03.939348300Z",
     "start_time": "2024-01-14T16:35:02.605524700Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Id  PostTypeId  AcceptedAnswerId  ParentId         CreationDate  Score  \\\n",
      "0  423497           1            423511         0  2019-08-24 09:39:31      2   \n",
      "1  423498           1                 0         0  2019-08-24 09:47:42      1   \n",
      "2  423499           1                 0         0  2019-08-24 09:48:26      1   \n",
      "3  423500           2                 0    215865  2019-08-24 09:57:01      0   \n",
      "4  423502           2                 0    423286  2019-08-24 10:44:52      3   \n",
      "\n",
      "   ViewCount                                               Body  OwnerUserId  \\\n",
      "0         68  <p>From wikipedia <a href=\"https://en.wikipedi...        64552   \n",
      "1         24  <p>I am currently doing local sensitivity anal...        87231   \n",
      "2         56  <p>I'm an honours student in psychology doing ...       257207   \n",
      "3          0  <p>Maybe you can look this <a href=\"https://me...       106606   \n",
      "4          0  <blockquote>\\n  <p>Q1) Is my approach valid?</...       220643   \n",
      "\n",
      "  OwnerDisplayName  ...  LastEditorDisplayName         LastEditDate  \\\n",
      "0              NaN  ...                    NaN                  NaN   \n",
      "1              NaN  ...                    NaN  2019-09-06 12:52:32   \n",
      "2              NaN  ...                    NaN                  NaN   \n",
      "3              NaN  ...                    NaN                  NaN   \n",
      "4              NaN  ...                    NaN  2019-08-24 12:13:46   \n",
      "\n",
      "      LastActivityDate                                              Title  \\\n",
      "0  2019-08-24 11:38:54  When are biased estimators with lower MSE pref...   \n",
      "1  2019-09-06 12:52:32  How to interpret the result from local sensiti...   \n",
      "2  2019-08-25 08:54:17      Power Analysis in G-Power - Mixed Model Anova   \n",
      "3  2019-08-24 09:57:01                                                NaN   \n",
      "4  2019-08-24 12:13:46                                                NaN   \n",
      "\n",
      "                                 Tags AnswerCount  CommentCount  \\\n",
      "0     <bias><unbiased-estimator><mse>           1             0   \n",
      "1  <sensitivity-analysis><elasticity>           1             0   \n",
      "2                     <anova><gpower>           2             0   \n",
      "3                                 NaN           0             0   \n",
      "4                                 NaN           0             1   \n",
      "\n",
      "   FavoriteCount           ClosedDate CommunityOwnedDate  \n",
      "0              1  2019-08-25 10:25:24                NaN  \n",
      "1              0                  NaN                NaN  \n",
      "2              0                  NaN                NaN  \n",
      "3              0                  NaN                NaN  \n",
      "4              0                  NaN                NaN  \n",
      "\n",
      "[5 rows x 21 columns]\n"
     ]
    }
   ],
   "source": [
    "if response.status_code == 200:\n",
    "    with ZipFile(BytesIO(response.content)) as z:\n",
    "        posts = pd.read_csv(z.open('posts.csv'))\n",
    "        print(posts.head())\n",
    "else:\n",
    "    print(f\"Failed to fetch the zip file. Status code: {response.status_code}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:08.944783600Z",
     "start_time": "2024-01-14T16:35:03.825611300Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostTypeId  AcceptedAnswerId  ParentId         CreationDate  Score  \\\n0  423497           1            423511         0  2019-08-24 09:39:31      2   \n1  423498           1                 0         0  2019-08-24 09:47:42      1   \n2  423499           1                 0         0  2019-08-24 09:48:26      1   \n3  423500           2                 0    215865  2019-08-24 09:57:01      0   \n4  423502           2                 0    423286  2019-08-24 10:44:52      3   \n\n   ViewCount                                               Body  OwnerUserId  \\\n0         68  <p>From wikipedia <a href=\"https://en.wikipedi...        64552   \n1         24  <p>I am currently doing local sensitivity anal...        87231   \n2         56  <p>I'm an honours student in psychology doing ...       257207   \n3          0  <p>Maybe you can look this <a href=\"https://me...       106606   \n4          0  <blockquote>\\n  <p>Q1) Is my approach valid?</...       220643   \n\n  OwnerDisplayName  ...  LastEditorDisplayName         LastEditDate  \\\n0             None  ...                   None                 None   \n1             None  ...                   None  2019-09-06 12:52:32   \n2             None  ...                   None                 None   \n3             None  ...                   None                 None   \n4             None  ...                   None  2019-08-24 12:13:46   \n\n      LastActivityDate                                              Title  \\\n0  2019-08-24 11:38:54  When are biased estimators with lower MSE pref...   \n1  2019-09-06 12:52:32  How to interpret the result from local sensiti...   \n2  2019-08-25 08:54:17      Power Analysis in G-Power - Mixed Model Anova   \n3  2019-08-24 09:57:01                                               None   \n4  2019-08-24 12:13:46                                               None   \n\n                                 Tags AnswerCount  CommentCount  \\\n0     <bias><unbiased-estimator><mse>           1             0   \n1  <sensitivity-analysis><elasticity>           1             0   \n2                     <anova><gpower>           2             0   \n3                                None           0             0   \n4                                None           0             1   \n\n   FavoriteCount           ClosedDate CommunityOwnedDate  \n0              1  2019-08-25 10:25:24               None  \n1              0                 None               None  \n2              0                 None               None  \n3              0                 None               None  \n4              0                 None               None  \n\n[5 rows x 21 columns]",
      "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>PostTypeId</th>\n      <th>AcceptedAnswerId</th>\n      <th>ParentId</th>\n      <th>CreationDate</th>\n      <th>Score</th>\n      <th>ViewCount</th>\n      <th>Body</th>\n      <th>OwnerUserId</th>\n      <th>OwnerDisplayName</th>\n      <th>...</th>\n      <th>LastEditorDisplayName</th>\n      <th>LastEditDate</th>\n      <th>LastActivityDate</th>\n      <th>Title</th>\n      <th>Tags</th>\n      <th>AnswerCount</th>\n      <th>CommentCount</th>\n      <th>FavoriteCount</th>\n      <th>ClosedDate</th>\n      <th>CommunityOwnedDate</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>423497</td>\n      <td>1</td>\n      <td>423511</td>\n      <td>0</td>\n      <td>2019-08-24 09:39:31</td>\n      <td>2</td>\n      <td>68</td>\n      <td>&lt;p&gt;From wikipedia &lt;a href=\"https://en.wikipedi...</td>\n      <td>64552</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2019-08-24 11:38:54</td>\n      <td>When are biased estimators with lower MSE pref...</td>\n      <td>&lt;bias&gt;&lt;unbiased-estimator&gt;&lt;mse&gt;</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>2019-08-25 10:25:24</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>423498</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2019-08-24 09:47:42</td>\n      <td>1</td>\n      <td>24</td>\n      <td>&lt;p&gt;I am currently doing local sensitivity anal...</td>\n      <td>87231</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-09-06 12:52:32</td>\n      <td>2019-09-06 12:52:32</td>\n      <td>How to interpret the result from local sensiti...</td>\n      <td>&lt;sensitivity-analysis&gt;&lt;elasticity&gt;</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>423499</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2019-08-24 09:48:26</td>\n      <td>1</td>\n      <td>56</td>\n      <td>&lt;p&gt;I'm an honours student in psychology doing ...</td>\n      <td>257207</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2019-08-25 08:54:17</td>\n      <td>Power Analysis in G-Power - Mixed Model Anova</td>\n      <td>&lt;anova&gt;&lt;gpower&gt;</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>423500</td>\n      <td>2</td>\n      <td>0</td>\n      <td>215865</td>\n      <td>2019-08-24 09:57:01</td>\n      <td>0</td>\n      <td>0</td>\n      <td>&lt;p&gt;Maybe you can look this &lt;a href=\"https://me...</td>\n      <td>106606</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2019-08-24 09:57:01</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>423502</td>\n      <td>2</td>\n      <td>0</td>\n      <td>423286</td>\n      <td>2019-08-24 10:44:52</td>\n      <td>3</td>\n      <td>0</td>\n      <td>&lt;blockquote&gt;\\n  &lt;p&gt;Q1) Is my approach valid?&lt;/...</td>\n      <td>220643</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-08-24 12:13:46</td>\n      <td>2019-08-24 12:13:46</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 21 columns</p>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load posts into sqlite\n",
    "posts.to_sql('posts', con, if_exists='replace', index=False)\n",
    "\n",
    "# read back in to prove that it worked\n",
    "sql = 'SELECT * FROM posts'\n",
    "posts = pd.read_sql(sql, con)\n",
    "posts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.132593400Z",
     "start_time": "2024-01-14T16:35:08.944783600Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Id  Reputation         CreationDate DisplayName       LastAccessDate  \\\n",
      "0  157607          31  2017-04-17 14:50:42  user157607  2019-07-23 16:44:08   \n",
      "1  157656         101  2017-04-17 20:08:20  user102859  2019-06-26 13:42:13   \n",
      "2  157704         133  2017-04-18 05:10:47      jupiar  2019-11-25 13:32:27   \n",
      "3  157709         155  2017-04-18 06:39:18      farmer  2019-02-17 19:44:24   \n",
      "4  157755         101  2017-04-18 12:56:17      Miki P  2019-08-12 17:02:21   \n",
      "\n",
      "  WebsiteUrl         Location  \\\n",
      "0        NaN              NaN   \n",
      "1        NaN              NaN   \n",
      "2        NaN  Shanghai, China   \n",
      "3        NaN              NaN   \n",
      "4        NaN              NaN   \n",
      "\n",
      "                                             AboutMe  Views  UpVotes  \\\n",
      "0                                                NaN      0        0   \n",
      "1                                                NaN      3        0   \n",
      "2  <p>Originally from the U.K, I have an Undergra...      1        1   \n",
      "3                                                NaN     16        0   \n",
      "4                                                NaN      1        9   \n",
      "\n",
      "   DownVotes                                    ProfileImageUrl  AccountId  \n",
      "0          0  https://www.gravatar.com/avatar/2efb161849efa4...   10705183  \n",
      "1          0      https://i.stack.imgur.com/eY4ka.jpg?s=128&g=1   10567606  \n",
      "2          0  https://www.gravatar.com/avatar/720e20205122c5...    9501631  \n",
      "3          0  https://www.gravatar.com/avatar/0f8c4bde3d8f25...   10709837  \n",
      "4          0  https://www.gravatar.com/avatar/af088558cd81c5...    7969290  \n"
     ]
    }
   ],
   "source": [
    "if response.status_code == 200:\n",
    "    with ZipFile(BytesIO(response.content)) as z:\n",
    "        users = pd.read_csv(z.open('users.csv'))\n",
    "        print(users.head())\n",
    "else:\n",
    "    print(f\"Failed to fetch the zip file. Status code: {response.status_code}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.500329900Z",
     "start_time": "2024-01-14T16:35:09.055456100Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  Reputation         CreationDate DisplayName       LastAccessDate  \\\n0  157607          31  2017-04-17 14:50:42  user157607  2019-07-23 16:44:08   \n1  157656         101  2017-04-17 20:08:20  user102859  2019-06-26 13:42:13   \n2  157704         133  2017-04-18 05:10:47      jupiar  2019-11-25 13:32:27   \n3  157709         155  2017-04-18 06:39:18      farmer  2019-02-17 19:44:24   \n4  157755         101  2017-04-18 12:56:17      Miki P  2019-08-12 17:02:21   \n\n  WebsiteUrl         Location  \\\n0       None             None   \n1       None             None   \n2       None  Shanghai, China   \n3       None             None   \n4       None             None   \n\n                                             AboutMe  Views  UpVotes  \\\n0                                               None      0        0   \n1                                               None      3        0   \n2  <p>Originally from the U.K, I have an Undergra...      1        1   \n3                                               None     16        0   \n4                                               None      1        9   \n\n   DownVotes                                    ProfileImageUrl  AccountId  \n0          0  https://www.gravatar.com/avatar/2efb161849efa4...   10705183  \n1          0      https://i.stack.imgur.com/eY4ka.jpg?s=128&g=1   10567606  \n2          0  https://www.gravatar.com/avatar/720e20205122c5...    9501631  \n3          0  https://www.gravatar.com/avatar/0f8c4bde3d8f25...   10709837  \n4          0  https://www.gravatar.com/avatar/af088558cd81c5...    7969290  ",
      "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>Reputation</th>\n      <th>CreationDate</th>\n      <th>DisplayName</th>\n      <th>LastAccessDate</th>\n      <th>WebsiteUrl</th>\n      <th>Location</th>\n      <th>AboutMe</th>\n      <th>Views</th>\n      <th>UpVotes</th>\n      <th>DownVotes</th>\n      <th>ProfileImageUrl</th>\n      <th>AccountId</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>157607</td>\n      <td>31</td>\n      <td>2017-04-17 14:50:42</td>\n      <td>user157607</td>\n      <td>2019-07-23 16:44:08</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/2efb161849efa4...</td>\n      <td>10705183</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>157656</td>\n      <td>101</td>\n      <td>2017-04-17 20:08:20</td>\n      <td>user102859</td>\n      <td>2019-06-26 13:42:13</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>https://i.stack.imgur.com/eY4ka.jpg?s=128&amp;g=1</td>\n      <td>10567606</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>157704</td>\n      <td>133</td>\n      <td>2017-04-18 05:10:47</td>\n      <td>jupiar</td>\n      <td>2019-11-25 13:32:27</td>\n      <td>None</td>\n      <td>Shanghai, China</td>\n      <td>&lt;p&gt;Originally from the U.K, I have an Undergra...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/720e20205122c5...</td>\n      <td>9501631</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>157709</td>\n      <td>155</td>\n      <td>2017-04-18 06:39:18</td>\n      <td>farmer</td>\n      <td>2019-02-17 19:44:24</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>16</td>\n      <td>0</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/0f8c4bde3d8f25...</td>\n      <td>10709837</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>157755</td>\n      <td>101</td>\n      <td>2017-04-18 12:56:17</td>\n      <td>Miki P</td>\n      <td>2019-08-12 17:02:21</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>1</td>\n      <td>9</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/af088558cd81c5...</td>\n      <td>7969290</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load comments into sqlite\n",
    "users.to_sql('users', con, if_exists='replace', index=False)\n",
    "\n",
    "# read back in to prove that it worked\n",
    "sql = 'SELECT * FROM users'\n",
    "users = pd.read_sql(sql, con)\n",
    "users.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **TODO**: Drop Duplicates\n",
    "Look for and drop any duplicates in all 3 of the tables (if they exist). Use Pandas to do this. If you find duplicates, you will need to rewrite the table.\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Enter code in the following cells. \n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.580018700Z",
     "start_time": "2024-01-14T16:35:09.500329900Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplicate comments rows: Empty DataFrame\n",
      "Columns: [Id, PostId, Score, Text, CreationDate, UserId]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "comments_duplicates = comments[comments.duplicated(\"Id\")]\n",
    "print(f\"Duplicate comments rows: {comments_duplicates}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.675441800Z",
     "start_time": "2024-01-14T16:35:09.522540500Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplicate comments rows: Empty DataFrame\n",
      "Columns: [Id, Reputation, CreationDate, DisplayName, LastAccessDate, WebsiteUrl, Location, AboutMe, Views, UpVotes, DownVotes, ProfileImageUrl, AccountId]\n",
      "Index: []\n"
     ]
    }
   ],
   "source": [
    "users_duplicates = users[users.duplicated(\"Id\")]\n",
    "print(f\"Duplicate comments rows: {users_duplicates}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.691598800Z",
     "start_time": "2024-01-14T16:35:09.538992900Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Duplicate comments rows: Empty DataFrame\n",
      "Columns: [Id, PostTypeId, AcceptedAnswerId, ParentId, CreationDate, Score, ViewCount, Body, OwnerUserId, OwnerDisplayName, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, Title, Tags, AnswerCount, CommentCount, FavoriteCount, ClosedDate, CommunityOwnedDate]\n",
      "Index: []\n",
      "\n",
      "[0 rows x 21 columns]\n"
     ]
    }
   ],
   "source": [
    "posts_duplicates = posts[posts.duplicated(\"Id\")]\n",
    "print(f\"Duplicate comments rows: {posts_duplicates}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Review the Data\n",
    "\n",
    "Spend some time reviewing the data.  Understand what data we have, think about how that data can be used to assist in the initiative of understanding how ChatData is used in the real world.  Is the data organised in a way that would lend itself to being manged in a relational database?  How would the different tables be connected?  What are the primary and foreign keys?  Would this give you a 3NF model?\n",
    "\n",
    "Also think about security and ethics.  Is there personal data in here?  Could individuals be identified through this data?  It it ethical to use the data in this way?  You will be asked to comment on these questions later!\n",
    "\n",
    "Use the code below to help you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.691598800Z",
     "start_time": "2024-01-14T16:35:09.548004600Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "Index(['Id', 'Reputation', 'CreationDate', 'DisplayName', 'LastAccessDate',\n       'WebsiteUrl', 'Location', 'AboutMe', 'Views', 'UpVotes', 'DownVotes',\n       'ProfileImageUrl', 'AccountId'],\n      dtype='object')"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.723873900Z",
     "start_time": "2024-01-14T16:35:09.576504900Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  Reputation         CreationDate DisplayName       LastAccessDate  \\\n0  157607          31  2017-04-17 14:50:42  user157607  2019-07-23 16:44:08   \n1  157656         101  2017-04-17 20:08:20  user102859  2019-06-26 13:42:13   \n2  157704         133  2017-04-18 05:10:47      jupiar  2019-11-25 13:32:27   \n3  157709         155  2017-04-18 06:39:18      farmer  2019-02-17 19:44:24   \n4  157755         101  2017-04-18 12:56:17      Miki P  2019-08-12 17:02:21   \n\n  WebsiteUrl         Location  \\\n0       None             None   \n1       None             None   \n2       None  Shanghai, China   \n3       None             None   \n4       None             None   \n\n                                             AboutMe  Views  UpVotes  \\\n0                                               None      0        0   \n1                                               None      3        0   \n2  <p>Originally from the U.K, I have an Undergra...      1        1   \n3                                               None     16        0   \n4                                               None      1        9   \n\n   DownVotes                                    ProfileImageUrl  AccountId  \n0          0  https://www.gravatar.com/avatar/2efb161849efa4...   10705183  \n1          0      https://i.stack.imgur.com/eY4ka.jpg?s=128&g=1   10567606  \n2          0  https://www.gravatar.com/avatar/720e20205122c5...    9501631  \n3          0  https://www.gravatar.com/avatar/0f8c4bde3d8f25...   10709837  \n4          0  https://www.gravatar.com/avatar/af088558cd81c5...    7969290  ",
      "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>Reputation</th>\n      <th>CreationDate</th>\n      <th>DisplayName</th>\n      <th>LastAccessDate</th>\n      <th>WebsiteUrl</th>\n      <th>Location</th>\n      <th>AboutMe</th>\n      <th>Views</th>\n      <th>UpVotes</th>\n      <th>DownVotes</th>\n      <th>ProfileImageUrl</th>\n      <th>AccountId</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>157607</td>\n      <td>31</td>\n      <td>2017-04-17 14:50:42</td>\n      <td>user157607</td>\n      <td>2019-07-23 16:44:08</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/2efb161849efa4...</td>\n      <td>10705183</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>157656</td>\n      <td>101</td>\n      <td>2017-04-17 20:08:20</td>\n      <td>user102859</td>\n      <td>2019-06-26 13:42:13</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>3</td>\n      <td>0</td>\n      <td>0</td>\n      <td>https://i.stack.imgur.com/eY4ka.jpg?s=128&amp;g=1</td>\n      <td>10567606</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>157704</td>\n      <td>133</td>\n      <td>2017-04-18 05:10:47</td>\n      <td>jupiar</td>\n      <td>2019-11-25 13:32:27</td>\n      <td>None</td>\n      <td>Shanghai, China</td>\n      <td>&lt;p&gt;Originally from the U.K, I have an Undergra...</td>\n      <td>1</td>\n      <td>1</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/720e20205122c5...</td>\n      <td>9501631</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>157709</td>\n      <td>155</td>\n      <td>2017-04-18 06:39:18</td>\n      <td>farmer</td>\n      <td>2019-02-17 19:44:24</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>16</td>\n      <td>0</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/0f8c4bde3d8f25...</td>\n      <td>10709837</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>157755</td>\n      <td>101</td>\n      <td>2017-04-18 12:56:17</td>\n      <td>Miki P</td>\n      <td>2019-08-12 17:02:21</td>\n      <td>None</td>\n      <td>None</td>\n      <td>None</td>\n      <td>1</td>\n      <td>9</td>\n      <td>0</td>\n      <td>https://www.gravatar.com/avatar/af088558cd81c5...</td>\n      <td>7969290</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.883049800Z",
     "start_time": "2024-01-14T16:35:09.602136300Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "Index(['Id', 'PostId', 'Score', 'Text', 'CreationDate', 'UserId'], dtype='object')"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.955531400Z",
     "start_time": "2024-01-14T16:35:09.613904400Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostId  Score                                               Text  \\\n0  723182  385124      0  @BenBolker I don't understand. The fit cannot ...   \n1  723183  385124      3  You can't add *less* than (`-min(y)`), but you...   \n2  723186  385137      0  nice. If you felt like doing the work it would...   \n3  723187  385137      0  i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...   \n4  723188  385134      0  Don't you mean \"so variance should be $\\sigma^...   \n\n          CreationDate  UserId  \n0  2019-01-01 00:06:39   78575  \n1  2019-01-01 00:09:22    2126  \n2  2019-01-01 00:32:11    2126  \n3  2019-01-01 00:40:36    2126  \n4  2019-01-01 00:41:28  112141  ",
      "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>PostId</th>\n      <th>Score</th>\n      <th>Text</th>\n      <th>CreationDate</th>\n      <th>UserId</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>723182</td>\n      <td>385124</td>\n      <td>0</td>\n      <td>@BenBolker I don't understand. The fit cannot ...</td>\n      <td>2019-01-01 00:06:39</td>\n      <td>78575</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>723183</td>\n      <td>385124</td>\n      <td>3</td>\n      <td>You can't add *less* than (`-min(y)`), but you...</td>\n      <td>2019-01-01 00:09:22</td>\n      <td>2126</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>723186</td>\n      <td>385137</td>\n      <td>0</td>\n      <td>nice. If you felt like doing the work it would...</td>\n      <td>2019-01-01 00:32:11</td>\n      <td>2126</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>723187</td>\n      <td>385137</td>\n      <td>0</td>\n      <td>i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...</td>\n      <td>2019-01-01 00:40:36</td>\n      <td>2126</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>723188</td>\n      <td>385134</td>\n      <td>0</td>\n      <td>Don't you mean \"so variance should be $\\sigma^...</td>\n      <td>2019-01-01 00:41:28</td>\n      <td>112141</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.955531400Z",
     "start_time": "2024-01-14T16:35:09.643693Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "Index(['Id', 'PostTypeId', 'AcceptedAnswerId', 'ParentId', 'CreationDate',\n       'Score', 'ViewCount', 'Body', 'OwnerUserId', 'OwnerDisplayName',\n       'LastEditorUserId', 'LastEditorDisplayName', 'LastEditDate',\n       'LastActivityDate', 'Title', 'Tags', 'AnswerCount', 'CommentCount',\n       'FavoriteCount', 'ClosedDate', 'CommunityOwnedDate'],\n      dtype='object')"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "posts.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:09.979443Z",
     "start_time": "2024-01-14T16:35:09.664839800Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostTypeId  AcceptedAnswerId  ParentId         CreationDate  Score  \\\n0  423497           1            423511         0  2019-08-24 09:39:31      2   \n1  423498           1                 0         0  2019-08-24 09:47:42      1   \n2  423499           1                 0         0  2019-08-24 09:48:26      1   \n3  423500           2                 0    215865  2019-08-24 09:57:01      0   \n4  423502           2                 0    423286  2019-08-24 10:44:52      3   \n\n   ViewCount                                               Body  OwnerUserId  \\\n0         68  <p>From wikipedia <a href=\"https://en.wikipedi...        64552   \n1         24  <p>I am currently doing local sensitivity anal...        87231   \n2         56  <p>I'm an honours student in psychology doing ...       257207   \n3          0  <p>Maybe you can look this <a href=\"https://me...       106606   \n4          0  <blockquote>\\n  <p>Q1) Is my approach valid?</...       220643   \n\n  OwnerDisplayName  ...  LastEditorDisplayName         LastEditDate  \\\n0             None  ...                   None                 None   \n1             None  ...                   None  2019-09-06 12:52:32   \n2             None  ...                   None                 None   \n3             None  ...                   None                 None   \n4             None  ...                   None  2019-08-24 12:13:46   \n\n      LastActivityDate                                              Title  \\\n0  2019-08-24 11:38:54  When are biased estimators with lower MSE pref...   \n1  2019-09-06 12:52:32  How to interpret the result from local sensiti...   \n2  2019-08-25 08:54:17      Power Analysis in G-Power - Mixed Model Anova   \n3  2019-08-24 09:57:01                                               None   \n4  2019-08-24 12:13:46                                               None   \n\n                                 Tags AnswerCount  CommentCount  \\\n0     <bias><unbiased-estimator><mse>           1             0   \n1  <sensitivity-analysis><elasticity>           1             0   \n2                     <anova><gpower>           2             0   \n3                                None           0             0   \n4                                None           0             1   \n\n   FavoriteCount           ClosedDate CommunityOwnedDate  \n0              1  2019-08-25 10:25:24               None  \n1              0                 None               None  \n2              0                 None               None  \n3              0                 None               None  \n4              0                 None               None  \n\n[5 rows x 21 columns]",
      "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>PostTypeId</th>\n      <th>AcceptedAnswerId</th>\n      <th>ParentId</th>\n      <th>CreationDate</th>\n      <th>Score</th>\n      <th>ViewCount</th>\n      <th>Body</th>\n      <th>OwnerUserId</th>\n      <th>OwnerDisplayName</th>\n      <th>...</th>\n      <th>LastEditorDisplayName</th>\n      <th>LastEditDate</th>\n      <th>LastActivityDate</th>\n      <th>Title</th>\n      <th>Tags</th>\n      <th>AnswerCount</th>\n      <th>CommentCount</th>\n      <th>FavoriteCount</th>\n      <th>ClosedDate</th>\n      <th>CommunityOwnedDate</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>423497</td>\n      <td>1</td>\n      <td>423511</td>\n      <td>0</td>\n      <td>2019-08-24 09:39:31</td>\n      <td>2</td>\n      <td>68</td>\n      <td>&lt;p&gt;From wikipedia &lt;a href=\"https://en.wikipedi...</td>\n      <td>64552</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2019-08-24 11:38:54</td>\n      <td>When are biased estimators with lower MSE pref...</td>\n      <td>&lt;bias&gt;&lt;unbiased-estimator&gt;&lt;mse&gt;</td>\n      <td>1</td>\n      <td>0</td>\n      <td>1</td>\n      <td>2019-08-25 10:25:24</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>423498</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2019-08-24 09:47:42</td>\n      <td>1</td>\n      <td>24</td>\n      <td>&lt;p&gt;I am currently doing local sensitivity anal...</td>\n      <td>87231</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-09-06 12:52:32</td>\n      <td>2019-09-06 12:52:32</td>\n      <td>How to interpret the result from local sensiti...</td>\n      <td>&lt;sensitivity-analysis&gt;&lt;elasticity&gt;</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>423499</td>\n      <td>1</td>\n      <td>0</td>\n      <td>0</td>\n      <td>2019-08-24 09:48:26</td>\n      <td>1</td>\n      <td>56</td>\n      <td>&lt;p&gt;I'm an honours student in psychology doing ...</td>\n      <td>257207</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2019-08-25 08:54:17</td>\n      <td>Power Analysis in G-Power - Mixed Model Anova</td>\n      <td>&lt;anova&gt;&lt;gpower&gt;</td>\n      <td>2</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>423500</td>\n      <td>2</td>\n      <td>0</td>\n      <td>215865</td>\n      <td>2019-08-24 09:57:01</td>\n      <td>0</td>\n      <td>0</td>\n      <td>&lt;p&gt;Maybe you can look this &lt;a href=\"https://me...</td>\n      <td>106606</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2019-08-24 09:57:01</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>423502</td>\n      <td>2</td>\n      <td>0</td>\n      <td>423286</td>\n      <td>2019-08-24 10:44:52</td>\n      <td>3</td>\n      <td>0</td>\n      <td>&lt;blockquote&gt;\\n  &lt;p&gt;Q1) Is my approach valid?&lt;/...</td>\n      <td>220643</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-08-24 12:13:46</td>\n      <td>2019-08-24 12:13:46</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>1</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 21 columns</p>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "posts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing queries with SQL Magic\n",
    "\n",
    "You will now need to write some queries to get answers to the questions in the requirements.  \n",
    "\n",
    "For single-line queries, start the cell with `%sql` and simply enter your query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.009517900Z",
     "start_time": "2024-01-14T16:35:09.691598800Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[(50000,)]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>COUNT(*)</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>50000</td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is an example\n",
    "%sql SELECT COUNT(*) FROM comments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For multi line sql statements use `%%sql` as follows. This tells Jupyter that *everything* in this cell should be interpreted as sql. So, NO comments other statements are allowed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.041386500Z",
     "start_time": "2024-01-14T16:35:09.723873900Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[(723182, 385124, 0, '@BenBolker I don\\'t understand. The fit cannot be done for the negative $y$. So intuitively I\\'d think that in order to retain the relativity of the  ... (24 characters truncated) ... rror\" or \"flip\" it to the positive axis? Would it be possible to adjust every point individually? Basically e.g. take $abs(y_i)$ instead of minimums.'),\n (723183, 385124, 3, \"You can't add *less* than (`-min(y)`), but you could add *more*. I'm going to stop answering now sorry, because **judging what the 'best' approach is ... (60 characters truncated) ...  of the analysis, why you need to fit an exponential, your level of computational and statistical sophistication and that of your audience, etc. ...)\"),\n (723186, 385137, 0, 'nice. If you felt like doing the work it would be nice to generate an image/contour plot of log-likelihood as a function of (mu, theta) and show the lines corresponding to the two `size` values .'),\n (723187, 385137, 0, 'i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,size=size,log=TRUE)),\\n                 xlim=c(2,5),ylim=c(0.2,0.5),\\n                 varnames=c(\"mu\",\"s ... (57 characters truncated) ... ,col=\"red\")\\nabline(h=fit.what$theta,col=\"blue\")\\n` ... although doesn\\'t look this is actually the answer - mu-hat is independent of theta-hat ... ?'),\n (723188, 385134, 0, 'Don\\'t you mean \"so variance should be $\\\\sigma^2/(n\\\\mu^2)$\"')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>Id</th>\n            <th>PostId</th>\n            <th>Score</th>\n            <th>Text</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>723182</td>\n            <td>385124</td>\n            <td>0</td>\n            <td>@BenBolker I don&#x27;t understand. The fit cannot be done for the negative $y$. So intuitively I&#x27;d think that in order to retain the relativity of the data, one&#x27;d ideally &quot;mirror&quot; or &quot;flip&quot; it to the positive axis? Would it be possible to adjust every point individually? Basically e.g. take $abs(y_i)$ instead of minimums.</td>\n        </tr>\n        <tr>\n            <td>723183</td>\n            <td>385124</td>\n            <td>3</td>\n            <td>You can&#x27;t add *less* than (`-min(y)`), but you could add *more*. I&#x27;m going to stop answering now sorry, because **judging what the &#x27;best&#x27; approach is depends on much more context that we don&#x27;t have** (the goal of the analysis, why you need to fit an exponential, your level of computational and statistical sophistication and that of your audience, etc. ...)</td>\n        </tr>\n        <tr>\n            <td>723186</td>\n            <td>385137</td>\n            <td>0</td>\n            <td>nice. If you felt like doing the work it would be nice to generate an image/contour plot of log-likelihood as a function of (mu, theta) and show the lines corresponding to the two `size` values .</td>\n        </tr>\n        <tr>\n            <td>723187</td>\n            <td>385137</td>\n            <td>0</td>\n            <td>i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,size=size,log=TRUE)),<br>                 xlim=c(2,5),ylim=c(0.2,0.5),<br>                 varnames=c(&quot;mu&quot;,&quot;size&quot;),<br>                 sys3d=&quot;contour&quot;)<br>abline(h=3.2/7,col=&quot;red&quot;)<br>abline(h=fit.what$theta,col=&quot;blue&quot;)<br>` ... although doesn&#x27;t look this is actually the answer - mu-hat is independent of theta-hat ... ?</td>\n        </tr>\n        <tr>\n            <td>723188</td>\n            <td>385134</td>\n            <td>0</td>\n            <td>Don&#x27;t you mean &quot;so variance should be $\\sigma^2/(n\\mu^2)$&quot;</td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT Id, PostId, Score, Text\n",
    "    FROM comments\n",
    "        LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing queries with Pandas\n",
    "Another way to write queries is to use pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.041386500Z",
     "start_time": "2024-01-14T16:35:09.739999Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostId  Score                                               Text\n0  723182  385124      0  @BenBolker I don't understand. The fit cannot ...\n1  723183  385124      3  You can't add *less* than (`-min(y)`), but you...\n2  723186  385137      0  nice. If you felt like doing the work it would...\n3  723187  385137      0  i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...\n4  723188  385134      0  Don't you mean \"so variance should be $\\sigma^...",
      "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>PostId</th>\n      <th>Score</th>\n      <th>Text</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>723182</td>\n      <td>385124</td>\n      <td>0</td>\n      <td>@BenBolker I don't understand. The fit cannot ...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>723183</td>\n      <td>385124</td>\n      <td>3</td>\n      <td>You can't add *less* than (`-min(y)`), but you...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>723186</td>\n      <td>385137</td>\n      <td>0</td>\n      <td>nice. If you felt like doing the work it would...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>723187</td>\n      <td>385137</td>\n      <td>0</td>\n      <td>i.e. `emdbook::curve3d(-sum(dnbinom(y,mu=mu,si...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>723188</td>\n      <td>385134</td>\n      <td>0</td>\n      <td>Don't you mean \"so variance should be $\\sigma^...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "SELECT Id, PostId, Score, Text\n",
    "    FROM comments\n",
    "        LIMIT 5\n",
    "        \"\"\"\n",
    "result = pd.read_sql(sql, con)\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Creating Tables with Referential Integrity"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When we loaded the csv files into Sqlite database tables, Sqlite created the tables for us behind the scenes. Let's inspect this a bit more.\n",
    "\n",
    "We can see how Sqlite created the tables by querying the `sqlite_master` table, which Sqlite uses to keep track of what objects have been created in the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.041386500Z",
     "start_time": "2024-01-14T16:35:09.755615300Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('CREATE TABLE \"comments\" (\\n\"Id\" INTEGER,\\n  \"PostId\" INTEGER,\\n  \"Score\" INTEGER,\\n  \"Text\" TEXT,\\n  \"CreationDate\" TEXT,\\n  \"UserId\" INTEGER\\n)',),\n ('CREATE TABLE \"posts\" (\\n\"Id\" INTEGER,\\n  \"PostTypeId\" INTEGER,\\n  \"AcceptedAnswerId\" INTEGER,\\n  \"ParentId\" INTEGER,\\n  \"CreationDate\" TEXT,\\n  \"Scor ... (240 characters truncated) ...   \"Tags\" TEXT,\\n  \"AnswerCount\" INTEGER,\\n  \"CommentCount\" INTEGER,\\n  \"FavoriteCount\" INTEGER,\\n  \"ClosedDate\" TEXT,\\n  \"CommunityOwnedDate\" TEXT\\n)',),\n ('CREATE TABLE \"users\" (\\n\"Id\" INTEGER,\\n  \"Reputation\" INTEGER,\\n  \"CreationDate\" TEXT,\\n  \"DisplayName\" TEXT,\\n  \"LastAccessDate\" TEXT,\\n  \"WebsiteUr ... (17 characters truncated) ... tion\" TEXT,\\n  \"AboutMe\" TEXT,\\n  \"Views\" INTEGER,\\n  \"UpVotes\" INTEGER,\\n  \"DownVotes\" INTEGER,\\n  \"ProfileImageUrl\" TEXT,\\n  \"AccountId\" INTEGER\\n)',)]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>sql</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>CREATE TABLE &quot;comments&quot; (<br>&quot;Id&quot; INTEGER,<br>  &quot;PostId&quot; INTEGER,<br>  &quot;Score&quot; INTEGER,<br>  &quot;Text&quot; TEXT,<br>  &quot;CreationDate&quot; TEXT,<br>  &quot;UserId&quot; INTEGER<br>)</td>\n        </tr>\n        <tr>\n            <td>CREATE TABLE &quot;posts&quot; (<br>&quot;Id&quot; INTEGER,<br>  &quot;PostTypeId&quot; INTEGER,<br>  &quot;AcceptedAnswerId&quot; INTEGER,<br>  &quot;ParentId&quot; INTEGER,<br>  &quot;CreationDate&quot; TEXT,<br>  &quot;Score&quot; INTEGER,<br>  &quot;ViewCount&quot; INTEGER,<br>  &quot;Body&quot; TEXT,<br>  &quot;OwnerUserId&quot; INTEGER,<br>  &quot;OwnerDisplayName&quot; TEXT,<br>  &quot;LastEditorUserId&quot; INTEGER,<br>  &quot;LastEditorDisplayName&quot; TEXT,<br>  &quot;LastEditDate&quot; TEXT,<br>  &quot;LastActivityDate&quot; TEXT,<br>  &quot;Title&quot; TEXT,<br>  &quot;Tags&quot; TEXT,<br>  &quot;AnswerCount&quot; INTEGER,<br>  &quot;CommentCount&quot; INTEGER,<br>  &quot;FavoriteCount&quot; INTEGER,<br>  &quot;ClosedDate&quot; TEXT,<br>  &quot;CommunityOwnedDate&quot; TEXT<br>)</td>\n        </tr>\n        <tr>\n            <td>CREATE TABLE &quot;users&quot; (<br>&quot;Id&quot; INTEGER,<br>  &quot;Reputation&quot; INTEGER,<br>  &quot;CreationDate&quot; TEXT,<br>  &quot;DisplayName&quot; TEXT,<br>  &quot;LastAccessDate&quot; TEXT,<br>  &quot;WebsiteUrl&quot; TEXT,<br>  &quot;Location&quot; TEXT,<br>  &quot;AboutMe&quot; TEXT,<br>  &quot;Views&quot; INTEGER,<br>  &quot;UpVotes&quot; INTEGER,<br>  &quot;DownVotes&quot; INTEGER,<br>  &quot;ProfileImageUrl&quot; TEXT,<br>  &quot;AccountId&quot; INTEGER<br>)</td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select sql from sqlite_master"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The above results show the `CREATE TABLE` statements that could be used by Sqlite to recreate the tables with the exact same structure.\n",
    "\n",
    "The problem with the CREATE TABLE statements above is that they don't enforce **referential integrity**.  In other words, they don't ensure that every UserId and PostId in the comments table refers to an actual UserId and PostId in the users and posts tables.  At the moment. we can insert any old number here, and even have multiple users with the same Id!  One of the advantages of working with relational databases is that they can enforce the correct uniqueness and relationships in the data, but at the moment we are not using that feature.  So let's fix that...\n",
    "\n",
    "First, let's drop the original tables:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.289032500Z",
     "start_time": "2024-01-14T16:35:09.772320900Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE comments;\n",
    "DROP TABLE users;\n",
    "DROP TABLE posts;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prove that this worked by selecting the names of the tables back.  We should have no tables:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.289032500Z",
     "start_time": "2024-01-14T16:35:09.917603100Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>name</th>\n        </tr>\n    </thead>\n    <tbody>\n    </tbody>\n</table>"
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM sqlite_master WHERE type='table'\n",
    "    ORDER BY name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Sqlite we need to enable the enforcement of foreign key constraints:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.322369900Z",
     "start_time": "2024-01-14T16:35:09.933437200Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "PRAGMA foreign_keys=ON;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now recreate the users table with a **primary key constraint** by copying the CREATE TABLE statement from above and adding the NOT NULL PRIMARY KEY clause to the Id:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.364328100Z",
     "start_time": "2024-01-14T16:35:09.947605400Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE \"users\" (\n",
    "\"Id\" INTEGER NOT NULL PRIMARY KEY,\n",
    "\"Reputation\" INTEGER,\n",
    "\"CreationDate\" TEXT,\n",
    "\"DisplayName\" TEXT,\n",
    "\"LastAccessDate\" TEXT,\n",
    "\"WebsiteUrl\" TEXT,\n",
    "\"Location\" TEXT,\n",
    "\"AboutMe\" TEXT,\n",
    "\"Views\" INTEGER,\n",
    "\"UpVotes\" INTEGER,\n",
    "\"DownVotes\" INTEGER,\n",
    "\"ProfileImageUrl\" TEXT,\n",
    "\"AccountId\" INTEGER\n",
    ");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now do the same for the posts table:\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cell\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [
    {
     "data": {
      "text/plain": "Id                        int64\nPostTypeId                int64\nAcceptedAnswerId          int64\nParentId                  int64\nCreationDate             object\nScore                     int64\nViewCount                 int64\nBody                     object\nOwnerUserId               int64\nOwnerDisplayName         object\nLastEditorUserId          int64\nLastEditorDisplayName    object\nLastEditDate             object\nLastActivityDate         object\nTitle                    object\nTags                     object\nAnswerCount               int64\nCommentCount              int64\nFavoriteCount             int64\nClosedDate               object\nCommunityOwnedDate       object\ndtype: object"
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "posts.dtypes"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.380144100Z",
     "start_time": "2024-01-14T16:35:09.979443Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.405804100Z",
     "start_time": "2024-01-14T16:35:09.997418200Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE \"posts\" (\n",
    "    \"Id\" INTEGER NOT NULL PRIMARY KEY,\n",
    "    \"PostTypeId\" INTEGER,\n",
    "    \"AcceptedAnswerId\" INTEGER,\n",
    "    \"ParentId\" INTEGER,\n",
    "    \"CreationDate\" TEXT,\n",
    "    \"Score\" INTEGER,\n",
    "    \"ViewCount\" INTEGER,\n",
    "    \"Body\" TEXT,\n",
    "    \"OwnerUserId\" INTEGER,\n",
    "    \"OwnerDisplayName\" TEXT,\n",
    "    \"LastEditorUserId\" INTEGER,\n",
    "    \"LastEditorDisplayName\" TEXT,\n",
    "    \"LastEditDate\" TEXT,\n",
    "    \"LastActivityDate\" TEXT,\n",
    "    \"Title\" TEXT,\n",
    "    \"Tags\" TEXT,\n",
    "    \"AnswerCount\" INTEGER,\n",
    "    \"CommentCount\" INTEGER,\n",
    "    \"FavoriteCount\" INTEGER,\n",
    "    \"ClosedDate\" TEXT,\n",
    "    \"CommunityOwnedDate\" TEXT\n",
    "); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now for the comments table.  We need to add the primary key constraint on the id here as we did for users and posts, but we also need to add FOREIGN KEY constraints on the UserId and PostId. Read the documentation here and find our how to do that: https://www.sqlite.org/foreignkeys.html.  Then create the comments table with the correct constraints:\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cell\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "data": {
      "text/plain": "Id               int64\nPostId           int64\nScore            int64\nText            object\nCreationDate    object\nUserId           int64\ndtype: object"
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments.dtypes"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.444284300Z",
     "start_time": "2024-01-14T16:35:10.027273500Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.477234Z",
     "start_time": "2024-01-14T16:35:10.041386500Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "CREATE TABLE \"comments\" (\n",
    "\"Id\" INTEGER NOT NULL PRIMARY KEY,\n",
    "\"PostId\" INTEGER,\n",
    "\"Score\" INTEGER,\n",
    "\"Text\" TEXT,\n",
    "\"CreationDate\" TEXT,\n",
    "\"UserId\" INTEGER\n",
    "); "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can re-insert the data into these constrained tables.  First users:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:10.651737100Z",
     "start_time": "2024-01-14T16:35:10.076701500Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "18412"
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert data into the new users table\n",
    "users.to_sql('users', con, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now posts:\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cell\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.255727300Z",
     "start_time": "2024-01-14T16:35:10.211313600Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "42234"
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert data into the new posts table\n",
    "posts.to_sql('posts', con, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally comments, which references the users and posts tables:\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cell\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.275081700Z",
     "start_time": "2024-01-14T16:35:10.913088500Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "50000"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Insert data into the new comments table\n",
    "comments.to_sql('comments', con, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now check that we have the 3 new table definitions in Sqlite:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.275081700Z",
     "start_time": "2024-01-14T16:35:11.195623Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('comments',), ('posts',), ('users',)]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>name</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>comments</td>\n        </tr>\n        <tr>\n            <td>posts</td>\n        </tr>\n        <tr>\n            <td>users</td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name FROM sqlite_master WHERE type='table'\n",
    "    ORDER BY name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now have all the data in tables in Sqlite and the tables will enforce the referential integrity."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example Query and Pattern for Tasks 2 and 3\n",
    "\n",
    "As you work through the next tasks, you will need to:\n",
    "\n",
    "1. Prepare the Sqlite query to answer the question\n",
    "2. Test it\n",
    "3. Insert it into the `queries` table, so we have a record of it for others.\n",
    "\n",
    "This is the process that we want you to follow for this project while completing it. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's see an example of this by answering the following question:\n",
    "\n",
    "**Which 5 users have viewed the most times and what is the sum of those views per user?**\n",
    "\n",
    "## Prepare the Sqlite query\n",
    "\n",
    "First, let's write the query:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.278625100Z",
     "start_time": "2024-01-14T16:35:11.214989600Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "     Id  TotalViews\n0   919       85180\n1  4253       35119\n2   805       34637\n3  7290       32639\n4  3277       29255",
      "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>TotalViews</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>919</td>\n      <td>85180</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>4253</td>\n      <td>35119</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>805</td>\n      <td>34637</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>7290</td>\n      <td>32639</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>3277</td>\n      <td>29255</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"\"\"\n",
    "SELECT Id, SUM(Views) AS TotalViews\n",
    "    FROM Users\n",
    "        GROUP BY Id\n",
    "            ORDER BY TotalViews DESC\n",
    "                LIMIT 5\n",
    "                \"\"\"\n",
    "result = pd.read_sql(sql, con) # con is the connection to the database\n",
    "result"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test the query\n",
    "You can optionally prove the query worked by performing the same query in Pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.455620300Z",
     "start_time": "2024-01-14T16:35:11.245414600Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "Id\n919     85180\n4253    35119\n805     34637\n7290    32639\n3277    29255\nName: Views, dtype: int64"
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results = users.groupby(['Id']).sum().sort_values('Views', ascending = False)[:5]\n",
    "results['Views']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Insert the query into the `queries` table\n",
    "Now we need to put this query into the `queries` table in sqlite. Remember we want these queries to be accessible to everybody that should have access to them. We do not want people writing and rewriting the same queires over and over again. The easiest thing to do is create a dictionary with the values and insert these into the queries table.  Note that the values are provided as lists as we are inserting a list of values (i.e. a number of rows) into the table.  In this case the number of rows is 1, so we have lists of 1 item.\n",
    "\n",
    "So here, we have a column called 'task' with a list of values, a column called 'action' with a list of values, etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.455620300Z",
     "start_time": "2024-01-14T16:35:11.291265700Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "{'task': ['Single Table Queries'],\n 'action': ['Which 5 users have viewed the most times and what is the sum of those views per user?'],\n 'query': ['\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                ']}"
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_dict = {\n",
    "              'task': ['Single Table Queries'],\n",
    "              'action': ['Which 5 users have viewed the most times and what is the sum of those views per user?'],\n",
    "              'query': [sql]\n",
    "             }\n",
    "query_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that you have the data structure (query_dict) containing the data, create a pandas dataframe that holds those values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.455620300Z",
     "start_time": "2024-01-14T16:35:11.311154400Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                   task                                             action  \\\n0  Single Table Queries  Which 5 users have viewed the most times and w...   \n\n                                               query  \n0  \\nSELECT Id, SUM(Views) AS TotalViews\\n    FRO...  ",
      "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>task</th>\n      <th>action</th>\n      <th>query</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Single Table Queries</td>\n      <td>Which 5 users have viewed the most times and w...</td>\n      <td>\\nSELECT Id, SUM(Views) AS TotalViews\\n    FRO...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "queries = pd.DataFrame(query_dict)\n",
    "queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now load that pandas dataframe (queries) into the sqlite table called queries. In this case, you use append NOT replace. You will be adding to this tables as you go thru this project."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.497318200Z",
     "start_time": "2024-01-14T16:35:11.328542800Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                   task                                             action  \\\n0  Single Table Queries  Which 5 users have viewed the most times and w...   \n\n                                               query  \n0  \\nSELECT Id, SUM(Views) AS TotalViews\\n    FRO...  ",
      "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>task</th>\n      <th>action</th>\n      <th>query</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Single Table Queries</td>\n      <td>Which 5 users have viewed the most times and w...</td>\n      <td>\\nSELECT Id, SUM(Views) AS TotalViews\\n    FRO...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# load query into sqlite\n",
    "queries.to_sql('queries', con, if_exists='append', index=False)\n",
    "\n",
    "# read back in to prove that it worked\n",
    "sql = 'SELECT * FROM queries'\n",
    "queries = pd.read_sql(sql, con)\n",
    "queries.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is likely that as you iterate thru this notebook you will create some duplicate entries in the query table. Not a big deal. Just tell sql or pandas (much easier in pandas) to drop the duplicates! If you drop the duplicates in Pandas you will have to write the entire dataframe back to sqlite. Otherwise it is changed in memory in Pandas but NOT in sqlite on the disk drive (or SDD). Just do this drop at the end of this notebook so that you are not constantly dealing with this. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, to summarise, as you go through the following tasks you need to:\n",
    "\n",
    "- answer the question in sql\n",
    "- prove it in pandas (if you want to)\n",
    "- put the query into the queries table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 1 (continued): Insert the CREATE TABLE Statements into the `queries` Table\n",
    "\n",
    "Now that we understand how to populate the `queries` table, let's insert the CREATE TABLE statements into it.  First let's define a function to help us insert into the queries table:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.504975300Z",
     "start_time": "2024-01-14T16:35:11.359245800Z"
    }
   },
   "outputs": [],
   "source": [
    "# Define a function that will insert into the queries table\n",
    "def store_query(task, action, new_query):\n",
    "    query_dict_chat = {\n",
    "                  'task': [task],\n",
    "                  'action': [action],\n",
    "                  'query': [new_query]\n",
    "                 }\n",
    "\n",
    "    # put query into the query_dict\n",
    "    chat_queries = pd.DataFrame(query_dict_chat)\n",
    "\n",
    "    # load query into sqlite\n",
    "    chat_queries.to_sql('queries', con, if_exists='append', index=False)    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can specify the queries and call the above function to store them.  The first one is done for you."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.507453600Z",
     "start_time": "2024-01-14T16:35:11.372877700Z"
    }
   },
   "outputs": [],
   "source": [
    "sql_query = \"\"\"\n",
    "    CREATE TABLE \"comments\" (\n",
    "    \"Id\" INTEGER,\n",
    "    \"PostId\" INTEGER,\n",
    "    \"Score\" INTEGER,\n",
    "    \"Text\" TEXT,\n",
    "    \"CreationDate\" TEXT,\n",
    "    \"UserId\" INTEGER\n",
    "    )\n",
    "    \"\"\"\n",
    "store_query(\"Task 1\", \"Get the CREATE TABLE statement for Comments\", sql_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's prove it works by selecting back from the queries table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.529112300Z",
     "start_time": "2024-01-14T16:35:11.388773Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    ')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Prove it works\n",
    "%sql SELECT * FROM queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Insert the other CREATE TABLE statements into the `queries` table.\n",
    "Follow the above pattern to complete these code cells:\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cells\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.679193700Z",
     "start_time": "2024-01-14T16:35:11.409646200Z"
    }
   },
   "outputs": [],
   "source": [
    "# Insert the CREATE TABLE for posts into the queries table\n",
    "sql_query = \"\"\"\n",
    "    CREATE TABLE \"posts\" (\n",
    "    \"Id\" INTEGER,\n",
    "    \"PostTypeId\" INTEGER,\n",
    "    \"AcceptedAnswerId\" INTEGER,\n",
    "    \"ParentId\" INTEGER,\n",
    "    \"CreationDate\" TEXT,\n",
    "    \"Score\" INTEGER,\n",
    "    \"ViewCount\" INTEGER,\n",
    "    \"Body\" TEXT,\n",
    "    \"OwnerUserId\" INTEGER,\n",
    "    \"OwnerDisplayName\" TEXT,\n",
    "    \"LastEditorUserId\" INTEGER,\n",
    "    \"LastEditorDisplayName\" TEXT,\n",
    "    \"LastEditDate\" TEXT,\n",
    "    \"LastActivityDate\" TEXT,\n",
    "    \"Title\" TEXT,\n",
    "    \"Tags\" TEXT,\n",
    "    \"AnswerCount\" INTEGER,\n",
    "    \"CommentCount\" INTEGER,\n",
    "    \"FavoriteCount\" INTEGER,\n",
    "    \"ClosedDate\" TEXT,\n",
    "    \"CommunityOwnedDate\" TEXT\n",
    "    )\n",
    "    \"\"\"\n",
    "store_query(\"Task 1\", \"Get the CREATE TABLE statement for Posts\", sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.679193700Z",
     "start_time": "2024-01-14T16:35:11.438815300Z"
    }
   },
   "outputs": [],
   "source": [
    "# Insert the CREATE TABLE for users into the queries table\n",
    "sql_query = \"\"\"\n",
    "    CREATE TABLE \"users\" (\n",
    "    \"Id\" INTEGER,\n",
    "    \"Reputation\" INTEGER,\n",
    "    \"CreationDate\" TEXT,\n",
    "    \"DisplayName\" TEXT,\n",
    "    \"LastAccessDate\" TEXT,\n",
    "    \"WebsiteUrl\" TEXT,\n",
    "    \"Location\" TEXT,\n",
    "    \"AboutMe\" TEXT,\n",
    "    \"Views\" INTEGER,\n",
    "    \"UpVotes\" INTEGER,\n",
    "    \"DownVotes\" INTEGER,\n",
    "    \"ProfileImageUrl\" TEXT,\n",
    "    \"AccountId\" INTEGER\n",
    "    )\n",
    "    \"\"\"\n",
    "store_query(\"Task 1\", \"Get the CREATE TABLE statement for Users\", sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Posts', '\\n    CREATE TABLE \"posts\" (\\n    \"Id\" INTEGER,\\n    \"PostTypeId\" INTEGER,\\n    \"AcceptedAnswerId\" INTEGER,\\n    \"ParentId\" INTEGER,\\n    \"CreationDa ... (300 characters truncated) ... \"AnswerCount\" INTEGER,\\n    \"CommentCount\" INTEGER,\\n    \"FavoriteCount\" INTEGER,\\n    \"ClosedDate\" TEXT,\\n    \"CommunityOwnedDate\" TEXT\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Users', '\\n    CREATE TABLE \"users\" (\\n    \"Id\" INTEGER,\\n    \"Reputation\" INTEGER,\\n    \"CreationDate\" TEXT,\\n    \"DisplayName\" TEXT,\\n    \"LastAccessDate\" T ... (61 characters truncated) ... tMe\" TEXT,\\n    \"Views\" INTEGER,\\n    \"UpVotes\" INTEGER,\\n    \"DownVotes\" INTEGER,\\n    \"ProfileImageUrl\" TEXT,\\n    \"AccountId\" INTEGER\\n    )\\n    ')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Posts</td>\n            <td><br>    CREATE TABLE &quot;posts&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostTypeId&quot; INTEGER,<br>    &quot;AcceptedAnswerId&quot; INTEGER,<br>    &quot;ParentId&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;Score&quot; INTEGER,<br>    &quot;ViewCount&quot; INTEGER,<br>    &quot;Body&quot; TEXT,<br>    &quot;OwnerUserId&quot; INTEGER,<br>    &quot;OwnerDisplayName&quot; TEXT,<br>    &quot;LastEditorUserId&quot; INTEGER,<br>    &quot;LastEditorDisplayName&quot; TEXT,<br>    &quot;LastEditDate&quot; TEXT,<br>    &quot;LastActivityDate&quot; TEXT,<br>    &quot;Title&quot; TEXT,<br>    &quot;Tags&quot; TEXT,<br>    &quot;AnswerCount&quot; INTEGER,<br>    &quot;CommentCount&quot; INTEGER,<br>    &quot;FavoriteCount&quot; INTEGER,<br>    &quot;ClosedDate&quot; TEXT,<br>    &quot;CommunityOwnedDate&quot; TEXT<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Users</td>\n            <td><br>    CREATE TABLE &quot;users&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;Reputation&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;DisplayName&quot; TEXT,<br>    &quot;LastAccessDate&quot; TEXT,<br>    &quot;WebsiteUrl&quot; TEXT,<br>    &quot;Location&quot; TEXT,<br>    &quot;AboutMe&quot; TEXT,<br>    &quot;Views&quot; INTEGER,<br>    &quot;UpVotes&quot; INTEGER,<br>    &quot;DownVotes&quot; INTEGER,<br>    &quot;ProfileImageUrl&quot; TEXT,<br>    &quot;AccountId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM queries"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.742029500Z",
     "start_time": "2024-01-14T16:35:11.455620300Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Count the Number of Rows in Each Table\n",
    "Run some queries to count the number of rows in each of the tables.  Don't forget to insert the query into the queries table.\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cells\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.762404600Z",
     "start_time": "2024-01-14T16:35:11.465638300Z"
    }
   },
   "outputs": [],
   "source": [
    "# Count the number of rows in the comments table\n",
    "sql_query = \"\"\"\n",
    "    SELECT COUNT(*) FROM comments\n",
    "\"\"\"\n",
    "store_query(\"Task 1\", \"Count number of rows in table Comments\", sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.789120100Z",
     "start_time": "2024-01-14T16:35:11.501814600Z"
    }
   },
   "outputs": [],
   "source": [
    "# Count the number of rows in the users table\n",
    "sql_query = \"\"\"\n",
    "    SELECT COUNT(*) FROM users\n",
    "\"\"\"\n",
    "store_query(\"Task 1\", \"Count number of rows in table Users\", sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.789120100Z",
     "start_time": "2024-01-14T16:35:11.513301900Z"
    }
   },
   "outputs": [],
   "source": [
    "# Count the number of rows in the posts table\n",
    "sql_query = \"\"\"\n",
    "    SELECT COUNT(*) FROM posts\n",
    "\"\"\"\n",
    "store_query(\"Task 1\", \"Count number of rows in table Posts\", sql_query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Posts', '\\n    CREATE TABLE \"posts\" (\\n    \"Id\" INTEGER,\\n    \"PostTypeId\" INTEGER,\\n    \"AcceptedAnswerId\" INTEGER,\\n    \"ParentId\" INTEGER,\\n    \"CreationDa ... (300 characters truncated) ... \"AnswerCount\" INTEGER,\\n    \"CommentCount\" INTEGER,\\n    \"FavoriteCount\" INTEGER,\\n    \"ClosedDate\" TEXT,\\n    \"CommunityOwnedDate\" TEXT\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Users', '\\n    CREATE TABLE \"users\" (\\n    \"Id\" INTEGER,\\n    \"Reputation\" INTEGER,\\n    \"CreationDate\" TEXT,\\n    \"DisplayName\" TEXT,\\n    \"LastAccessDate\" T ... (61 characters truncated) ... tMe\" TEXT,\\n    \"Views\" INTEGER,\\n    \"UpVotes\" INTEGER,\\n    \"DownVotes\" INTEGER,\\n    \"ProfileImageUrl\" TEXT,\\n    \"AccountId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Count number of rows in table Comments', '\\n    SELECT COUNT(*) FROM comments\\n'),\n ('Task 1', 'Count number of rows in table Users', '\\n    SELECT COUNT(*) FROM users\\n'),\n ('Task 1', 'Count number of rows in table Posts', '\\n    SELECT COUNT(*) FROM posts\\n')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Posts</td>\n            <td><br>    CREATE TABLE &quot;posts&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostTypeId&quot; INTEGER,<br>    &quot;AcceptedAnswerId&quot; INTEGER,<br>    &quot;ParentId&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;Score&quot; INTEGER,<br>    &quot;ViewCount&quot; INTEGER,<br>    &quot;Body&quot; TEXT,<br>    &quot;OwnerUserId&quot; INTEGER,<br>    &quot;OwnerDisplayName&quot; TEXT,<br>    &quot;LastEditorUserId&quot; INTEGER,<br>    &quot;LastEditorDisplayName&quot; TEXT,<br>    &quot;LastEditDate&quot; TEXT,<br>    &quot;LastActivityDate&quot; TEXT,<br>    &quot;Title&quot; TEXT,<br>    &quot;Tags&quot; TEXT,<br>    &quot;AnswerCount&quot; INTEGER,<br>    &quot;CommentCount&quot; INTEGER,<br>    &quot;FavoriteCount&quot; INTEGER,<br>    &quot;ClosedDate&quot; TEXT,<br>    &quot;CommunityOwnedDate&quot; TEXT<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Users</td>\n            <td><br>    CREATE TABLE &quot;users&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;Reputation&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;DisplayName&quot; TEXT,<br>    &quot;LastAccessDate&quot; TEXT,<br>    &quot;WebsiteUrl&quot; TEXT,<br>    &quot;Location&quot; TEXT,<br>    &quot;AboutMe&quot; TEXT,<br>    &quot;Views&quot; INTEGER,<br>    &quot;UpVotes&quot; INTEGER,<br>    &quot;DownVotes&quot; INTEGER,<br>    &quot;ProfileImageUrl&quot; TEXT,<br>    &quot;AccountId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Comments</td>\n            <td><br>    SELECT COUNT(*) FROM comments<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Users</td>\n            <td><br>    SELECT COUNT(*) FROM users<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Posts</td>\n            <td><br>    SELECT COUNT(*) FROM posts<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check queries\n",
    "%sql SELECT * FROM queries"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.823236600Z",
     "start_time": "2024-01-14T16:35:11.535909700Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Do some Random Checks on the Data\n",
    "Let's write some queries that select 5 random rows from each table.  The queries are provided here:\n",
    "\n",
    "    select * from Comments order by random() limit 5;\n",
    "    select * from Users order by random() limit 5;\n",
    "    select * from Posts order by random() limit 5;\n",
    "\n",
    "Enter the queries into the code cells below and insert the queries into the `queries` table in the same was as you did for the CREATE TABLE statements.\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cells\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Working with Sqlite and SQL Magic\n",
    "In this section let's spend a little time understanding a bit more about how we can work with Sqlite within Jupyter.\n",
    "\n",
    "Let's look at 2 ways to query the sqlite database: using SQL Magic or using Pandas.  Either way is fine for this project."
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Run the query to select 5 random rows from the posts table\n",
    "sql_query = \"\"\"\n",
    "    SELECT * FROM posts order by random() limit 5\n",
    "\"\"\"\n",
    "store_query(\"Task 1\", \"Five random posts\", sql_query)"
   ],
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.823236600Z",
     "start_time": "2024-01-14T16:35:11.545221700Z"
    }
   },
   "execution_count": 51
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Run the query to select 5 random rows from the posts comments\n",
    "sql_query = \"\"\"\n",
    "    SELECT * FROM comments order by random() limit 5\n",
    "\"\"\"\n",
    "store_query(\"Task 1\", \"Five random comments\", sql_query)"
   ],
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.899332100Z",
     "start_time": "2024-01-14T16:35:11.574448Z"
    }
   },
   "execution_count": 52
  },
  {
   "cell_type": "code",
   "outputs": [],
   "source": [
    "# Run the query to select 5 random rows from the users table\n",
    "sql_query = \"\"\"\n",
    "    SELECT * FROM users order by random() limit 5\n",
    "\"\"\"\n",
    "store_query(\"Task 1\", \"Five random users\", sql_query)"
   ],
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.899332100Z",
     "start_time": "2024-01-14T16:35:11.605678400Z"
    }
   },
   "execution_count": 53
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Posts', '\\n    CREATE TABLE \"posts\" (\\n    \"Id\" INTEGER,\\n    \"PostTypeId\" INTEGER,\\n    \"AcceptedAnswerId\" INTEGER,\\n    \"ParentId\" INTEGER,\\n    \"CreationDa ... (300 characters truncated) ... \"AnswerCount\" INTEGER,\\n    \"CommentCount\" INTEGER,\\n    \"FavoriteCount\" INTEGER,\\n    \"ClosedDate\" TEXT,\\n    \"CommunityOwnedDate\" TEXT\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Users', '\\n    CREATE TABLE \"users\" (\\n    \"Id\" INTEGER,\\n    \"Reputation\" INTEGER,\\n    \"CreationDate\" TEXT,\\n    \"DisplayName\" TEXT,\\n    \"LastAccessDate\" T ... (61 characters truncated) ... tMe\" TEXT,\\n    \"Views\" INTEGER,\\n    \"UpVotes\" INTEGER,\\n    \"DownVotes\" INTEGER,\\n    \"ProfileImageUrl\" TEXT,\\n    \"AccountId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Count number of rows in table Comments', '\\n    SELECT COUNT(*) FROM comments\\n'),\n ('Task 1', 'Count number of rows in table Users', '\\n    SELECT COUNT(*) FROM users\\n'),\n ('Task 1', 'Count number of rows in table Posts', '\\n    SELECT COUNT(*) FROM posts\\n'),\n ('Task 1', 'Five random posts', '\\n    SELECT * FROM posts order by random() limit 5\\n'),\n ('Task 1', 'Five random comments', '\\n    SELECT * FROM comments order by random() limit 5\\n'),\n ('Task 1', 'Five random users', '\\n    SELECT * FROM users order by random() limit 5\\n')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Posts</td>\n            <td><br>    CREATE TABLE &quot;posts&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostTypeId&quot; INTEGER,<br>    &quot;AcceptedAnswerId&quot; INTEGER,<br>    &quot;ParentId&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;Score&quot; INTEGER,<br>    &quot;ViewCount&quot; INTEGER,<br>    &quot;Body&quot; TEXT,<br>    &quot;OwnerUserId&quot; INTEGER,<br>    &quot;OwnerDisplayName&quot; TEXT,<br>    &quot;LastEditorUserId&quot; INTEGER,<br>    &quot;LastEditorDisplayName&quot; TEXT,<br>    &quot;LastEditDate&quot; TEXT,<br>    &quot;LastActivityDate&quot; TEXT,<br>    &quot;Title&quot; TEXT,<br>    &quot;Tags&quot; TEXT,<br>    &quot;AnswerCount&quot; INTEGER,<br>    &quot;CommentCount&quot; INTEGER,<br>    &quot;FavoriteCount&quot; INTEGER,<br>    &quot;ClosedDate&quot; TEXT,<br>    &quot;CommunityOwnedDate&quot; TEXT<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Users</td>\n            <td><br>    CREATE TABLE &quot;users&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;Reputation&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;DisplayName&quot; TEXT,<br>    &quot;LastAccessDate&quot; TEXT,<br>    &quot;WebsiteUrl&quot; TEXT,<br>    &quot;Location&quot; TEXT,<br>    &quot;AboutMe&quot; TEXT,<br>    &quot;Views&quot; INTEGER,<br>    &quot;UpVotes&quot; INTEGER,<br>    &quot;DownVotes&quot; INTEGER,<br>    &quot;ProfileImageUrl&quot; TEXT,<br>    &quot;AccountId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Comments</td>\n            <td><br>    SELECT COUNT(*) FROM comments<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Users</td>\n            <td><br>    SELECT COUNT(*) FROM users<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Posts</td>\n            <td><br>    SELECT COUNT(*) FROM posts<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random posts</td>\n            <td><br>    SELECT * FROM posts order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random comments</td>\n            <td><br>    SELECT * FROM comments order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random users</td>\n            <td><br>    SELECT * FROM users order by random() limit 5<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM queries"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.899332100Z",
     "start_time": "2024-01-14T16:35:11.622162Z"
    }
   },
   "execution_count": 54
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Task 2: Create Single Table Queries\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Lifecycle Stage: Analyze\n",
    "We can now start the analysis with our single-table queries.  First we need to create a new computed column to help with one of the queries.  The code below creates a column called LEN_BODY which is the length of the BODY text:\n"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "ALTER TABLE POSTS ADD COLUMN LEN_BODY INT"
   ],
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:11.899332100Z",
     "start_time": "2024-01-14T16:35:11.647832800Z"
    }
   },
   "execution_count": 55
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "42234 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[]"
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "UPDATE POSTS SET LEN_BODY = LENGTH(BODY)"
   ],
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:13.443452500Z",
     "start_time": "2024-01-14T16:35:11.664002Z"
    }
   },
   "execution_count": 56
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Single Table Queries\n",
    "From the Template SQL Queries there are a series of queries requested. You are responsible for coding the result. Use the pattern above to accomplish the coding objective.  Copy and paste the question (column B Action) into a markdown cell and answer the query. Use the pattern that you have been using above. \n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Enter your code below.  Just keep on inserting cells as you need them.\n",
    "    </font></div>"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "How many posts have 0 comments?"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "outputs": [
    {
     "data": {
      "text/plain": "   COUNT(*)\n0     21713",
      "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>COUNT(*)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>21713</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT COUNT(*)\n",
    "FROM posts\n",
    "WHERE CommentCount = 0\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"How many posts have 0 comments?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:13.600840600Z",
     "start_time": "2024-01-14T16:35:13.439097Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:13.787776400Z",
     "start_time": "2024-01-14T16:35:13.583623800Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "   COUNT(*)\n0      6460",
      "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>COUNT(*)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>6460</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT COUNT(*)\n",
    "FROM posts\n",
    "WHERE CommentCount = 1\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"How many posts have 1 comments?\", query)\n",
    "result = pd.read_sql(query, con)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:13.906773200Z",
     "start_time": "2024-01-14T16:35:13.729689Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "   COUNT(*)\n0     14061",
      "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>COUNT(*)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>14061</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT COUNT(*)\n",
    "FROM posts\n",
    "WHERE CommentCount >= 2\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"How many posts have 2 comments or more?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.107387900Z",
     "start_time": "2024-01-14T16:35:13.872375300Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostTypeId  AcceptedAnswerId  ParentId         CreationDate  Score  \\\n0  388566           1            388582         0  2019-01-22 15:16:47     56   \n1  394118           1            394128         0  2019-02-24 14:07:11     64   \n2  431370           1            431397         0  2019-10-14 11:29:21     77   \n3  398646           1            398653         0  2019-03-21 01:19:52     61   \n4  434128           1            434579         0  2019-11-01 13:07:36     73   \n\n   ViewCount                                               Body  OwnerUserId  \\\n0      19542  <ul>\\n<li>Statement One (S1): \"One in 80 death...       228214   \n1      16317  <p>A human child at age 2 needs around 5 insta...       107213   \n2      11723  <p>It seems very counter intuitive to many peo...       262594   \n3       9850  <p>The title of the Comment in Nature <a href=...       163067   \n4       6718  <p>I am designing a one year program in data a...        14188   \n\n  OwnerDisplayName  ...         LastEditDate     LastActivityDate  \\\n0             None  ...  2019-01-22 21:40:39  2019-01-24 17:09:47   \n1             None  ...  2019-02-25 22:40:22  2019-03-03 17:37:05   \n2             None  ...  2019-11-28 01:44:34  2019-11-28 01:44:34   \n3             None  ...  2019-03-22 22:14:04  2019-03-30 19:35:27   \n4             None  ...                 None  2019-11-26 00:59:15   \n\n                                               Title  \\\n0  Is it wrong to rephrase \"1 in 80 deaths is cau...   \n1  Why do neural networks need so many training e...   \n2  Is there a name for the phenomenon of false po...   \n3  What does \"Scientists rise up against statisti...   \n4  Famous statistical wins and horror stories for...   \n\n                                                Tags AnswerCount CommentCount  \\\n0                             <interpretation><risk>           9           15   \n1                    <neural-networks><neuroscience>          12           24   \n2              <probability><terminology><intuition>           8            9   \n3          <statistical-significance><p-value><bias>          10            7   \n4  <mathematical-statistics><data-visualization><...          13            7   \n\n   FavoriteCount  ClosedDate   CommunityOwnedDate LEN_BODY  \n0             15        None                 None     2270  \n1             38        None                 None      512  \n2             18        None                 None      811  \n3             34        None                 None     2148  \n4             70        None  2019-11-01 15:12:41     1172  \n\n[5 rows x 22 columns]",
      "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>PostTypeId</th>\n      <th>AcceptedAnswerId</th>\n      <th>ParentId</th>\n      <th>CreationDate</th>\n      <th>Score</th>\n      <th>ViewCount</th>\n      <th>Body</th>\n      <th>OwnerUserId</th>\n      <th>OwnerDisplayName</th>\n      <th>...</th>\n      <th>LastEditDate</th>\n      <th>LastActivityDate</th>\n      <th>Title</th>\n      <th>Tags</th>\n      <th>AnswerCount</th>\n      <th>CommentCount</th>\n      <th>FavoriteCount</th>\n      <th>ClosedDate</th>\n      <th>CommunityOwnedDate</th>\n      <th>LEN_BODY</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>388566</td>\n      <td>1</td>\n      <td>388582</td>\n      <td>0</td>\n      <td>2019-01-22 15:16:47</td>\n      <td>56</td>\n      <td>19542</td>\n      <td>&lt;ul&gt;\\n&lt;li&gt;Statement One (S1): \"One in 80 death...</td>\n      <td>228214</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-01-22 21:40:39</td>\n      <td>2019-01-24 17:09:47</td>\n      <td>Is it wrong to rephrase \"1 in 80 deaths is cau...</td>\n      <td>&lt;interpretation&gt;&lt;risk&gt;</td>\n      <td>9</td>\n      <td>15</td>\n      <td>15</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2270</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>394118</td>\n      <td>1</td>\n      <td>394128</td>\n      <td>0</td>\n      <td>2019-02-24 14:07:11</td>\n      <td>64</td>\n      <td>16317</td>\n      <td>&lt;p&gt;A human child at age 2 needs around 5 insta...</td>\n      <td>107213</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-02-25 22:40:22</td>\n      <td>2019-03-03 17:37:05</td>\n      <td>Why do neural networks need so many training e...</td>\n      <td>&lt;neural-networks&gt;&lt;neuroscience&gt;</td>\n      <td>12</td>\n      <td>24</td>\n      <td>38</td>\n      <td>None</td>\n      <td>None</td>\n      <td>512</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>431370</td>\n      <td>1</td>\n      <td>431397</td>\n      <td>0</td>\n      <td>2019-10-14 11:29:21</td>\n      <td>77</td>\n      <td>11723</td>\n      <td>&lt;p&gt;It seems very counter intuitive to many peo...</td>\n      <td>262594</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-11-28 01:44:34</td>\n      <td>2019-11-28 01:44:34</td>\n      <td>Is there a name for the phenomenon of false po...</td>\n      <td>&lt;probability&gt;&lt;terminology&gt;&lt;intuition&gt;</td>\n      <td>8</td>\n      <td>9</td>\n      <td>18</td>\n      <td>None</td>\n      <td>None</td>\n      <td>811</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>398646</td>\n      <td>1</td>\n      <td>398653</td>\n      <td>0</td>\n      <td>2019-03-21 01:19:52</td>\n      <td>61</td>\n      <td>9850</td>\n      <td>&lt;p&gt;The title of the Comment in Nature &lt;a href=...</td>\n      <td>163067</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-03-22 22:14:04</td>\n      <td>2019-03-30 19:35:27</td>\n      <td>What does \"Scientists rise up against statisti...</td>\n      <td>&lt;statistical-significance&gt;&lt;p-value&gt;&lt;bias&gt;</td>\n      <td>10</td>\n      <td>7</td>\n      <td>34</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2148</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>434128</td>\n      <td>1</td>\n      <td>434579</td>\n      <td>0</td>\n      <td>2019-11-01 13:07:36</td>\n      <td>73</td>\n      <td>6718</td>\n      <td>&lt;p&gt;I am designing a one year program in data a...</td>\n      <td>14188</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-11-26 00:59:15</td>\n      <td>Famous statistical wins and horror stories for...</td>\n      <td>&lt;mathematical-statistics&gt;&lt;data-visualization&gt;&lt;...</td>\n      <td>13</td>\n      <td>7</td>\n      <td>70</td>\n      <td>None</td>\n      <td>2019-11-01 15:12:41</td>\n      <td>1172</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 22 columns</p>\n</div>"
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT *\n",
    "FROM posts\n",
    "ORDER BY ViewCount DESC\n",
    "    LIMIT 5\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"Find the 5 posts with the highest viewcount\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  PostTypeId  AcceptedAnswerId  ParentId         CreationDate  Score  \\\n0  431397           2                 0    431370  2019-10-14 14:29:36    101   \n1  394128           2                 0    394118  2019-02-24 15:44:44    100   \n2  426878           2                 0    426873  2019-09-11 23:23:31     93   \n3  388578           2                 0    388566  2019-01-22 15:48:47     80   \n4  431370           1            431397         0  2019-10-14 11:29:21     77   \n\n   ViewCount                                               Body  OwnerUserId  \\\n0          0  <p>Yes there is. Generally it is termed <stron...       142976   \n1          0  <p>I caution against expecting strong resembla...        22311   \n2          0  <p><strong>tl;dr</strong> Even though this is ...       119015   \n3          0  <p>To me \"1 in 80 deaths...\" is by far the cle...       227039   \n4      11723  <p>It seems very counter intuitive to many peo...       262594   \n\n  OwnerDisplayName  ...         LastEditDate     LastActivityDate  \\\n0             None  ...                 None  2019-10-14 14:29:36   \n1             None  ...  2019-03-03 17:37:05  2019-03-03 17:37:05   \n2             None  ...  2019-09-13 14:02:28  2019-09-13 14:02:28   \n3             None  ...                 None  2019-01-22 15:48:47   \n4             None  ...  2019-11-28 01:44:34  2019-11-28 01:44:34   \n\n                                               Title  \\\n0                                               None   \n1                                               None   \n2                                               None   \n3                                               None   \n4  Is there a name for the phenomenon of false po...   \n\n                                    Tags AnswerCount CommentCount  \\\n0                                   None           0            0   \n1                                   None           0           15   \n2                                   None           0            6   \n3                                   None           0           11   \n4  <probability><terminology><intuition>           8            9   \n\n   FavoriteCount  ClosedDate  CommunityOwnedDate LEN_BODY  \n0              0        None                None      269  \n1              0        None                None     5829  \n2              0        None                None     4627  \n3              0        None                None      572  \n4             18        None                None      811  \n\n[5 rows x 22 columns]",
      "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>PostTypeId</th>\n      <th>AcceptedAnswerId</th>\n      <th>ParentId</th>\n      <th>CreationDate</th>\n      <th>Score</th>\n      <th>ViewCount</th>\n      <th>Body</th>\n      <th>OwnerUserId</th>\n      <th>OwnerDisplayName</th>\n      <th>...</th>\n      <th>LastEditDate</th>\n      <th>LastActivityDate</th>\n      <th>Title</th>\n      <th>Tags</th>\n      <th>AnswerCount</th>\n      <th>CommentCount</th>\n      <th>FavoriteCount</th>\n      <th>ClosedDate</th>\n      <th>CommunityOwnedDate</th>\n      <th>LEN_BODY</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>431397</td>\n      <td>2</td>\n      <td>0</td>\n      <td>431370</td>\n      <td>2019-10-14 14:29:36</td>\n      <td>101</td>\n      <td>0</td>\n      <td>&lt;p&gt;Yes there is. Generally it is termed &lt;stron...</td>\n      <td>142976</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-10-14 14:29:36</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n      <td>269</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>394128</td>\n      <td>2</td>\n      <td>0</td>\n      <td>394118</td>\n      <td>2019-02-24 15:44:44</td>\n      <td>100</td>\n      <td>0</td>\n      <td>&lt;p&gt;I caution against expecting strong resembla...</td>\n      <td>22311</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-03-03 17:37:05</td>\n      <td>2019-03-03 17:37:05</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>15</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n      <td>5829</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>426878</td>\n      <td>2</td>\n      <td>0</td>\n      <td>426873</td>\n      <td>2019-09-11 23:23:31</td>\n      <td>93</td>\n      <td>0</td>\n      <td>&lt;p&gt;&lt;strong&gt;tl;dr&lt;/strong&gt; Even though this is ...</td>\n      <td>119015</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-09-13 14:02:28</td>\n      <td>2019-09-13 14:02:28</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>6</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n      <td>4627</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>388578</td>\n      <td>2</td>\n      <td>0</td>\n      <td>388566</td>\n      <td>2019-01-22 15:48:47</td>\n      <td>80</td>\n      <td>0</td>\n      <td>&lt;p&gt;To me \"1 in 80 deaths...\" is by far the cle...</td>\n      <td>227039</td>\n      <td>None</td>\n      <td>...</td>\n      <td>None</td>\n      <td>2019-01-22 15:48:47</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>11</td>\n      <td>0</td>\n      <td>None</td>\n      <td>None</td>\n      <td>572</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>431370</td>\n      <td>1</td>\n      <td>431397</td>\n      <td>0</td>\n      <td>2019-10-14 11:29:21</td>\n      <td>77</td>\n      <td>11723</td>\n      <td>&lt;p&gt;It seems very counter intuitive to many peo...</td>\n      <td>262594</td>\n      <td>None</td>\n      <td>...</td>\n      <td>2019-11-28 01:44:34</td>\n      <td>2019-11-28 01:44:34</td>\n      <td>Is there a name for the phenomenon of false po...</td>\n      <td>&lt;probability&gt;&lt;terminology&gt;&lt;intuition&gt;</td>\n      <td>8</td>\n      <td>9</td>\n      <td>18</td>\n      <td>None</td>\n      <td>None</td>\n      <td>811</td>\n    </tr>\n  </tbody>\n</table>\n<p>5 rows × 22 columns</p>\n</div>"
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    *\n",
    "FROM \n",
    "    posts\n",
    "ORDER BY \n",
    "    Score DESC\n",
    "    LIMIT 5\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"Find the top 5 posts with the highest scores\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.289138400Z",
     "start_time": "2024-01-14T16:35:14.033302Z"
    }
   },
   "execution_count": 61
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   Score  Frequency\n0      0      19888\n1      1      11867\n2      2       5094\n3      3       2228\n4      4       1059",
      "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>Score</th>\n      <th>Frequency</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>19888</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>11867</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>5094</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>2228</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>1059</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    Score, COUNT(*) AS Frequency\n",
    "FROM \n",
    "    posts\n",
    "GROUP BY \n",
    "    Score  \n",
    "ORDER BY \n",
    "    Frequency DESC, Score\n",
    "    LIMIT 5\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"What are the 5 most frequent scores on posts?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.394772700Z",
     "start_time": "2024-01-14T16:35:14.189238400Z"
    }
   },
   "execution_count": 62
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   NumPostsWithDataTag\n0                 2242",
      "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>NumPostsWithDataTag</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2242</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    COUNT(*) AS NumPostsWithDataTag\n",
    "FROM \n",
    "    posts\n",
    "WHERE \n",
    "    Tags \n",
    "LIKE \n",
    "    '%data%' \n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"How many posts have the keyword 'data' in their tags?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.728415300Z",
     "start_time": "2024-01-14T16:35:14.322383400Z"
    }
   },
   "execution_count": 63
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   CommentCount  Frequency\n0             0      21713\n1             1       6460\n2             2       4966\n3             3       3063\n4             4       2026",
      "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>CommentCount</th>\n      <th>Frequency</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>21713</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>6460</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>4966</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>3063</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>2026</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    commentcount, COUNT(*) AS Frequency\n",
    "FROM \n",
    "    posts\n",
    "GROUP BY \n",
    "    commentcount  \n",
    "ORDER BY \n",
    "    Frequency DESC, commentcount\n",
    "    LIMIT 5\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"What are the 5 most frequent commentcount for posts?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.808360400Z",
     "start_time": "2024-01-14T16:35:14.463014700Z"
    }
   },
   "execution_count": 64
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   COUNT(*)\n0      5341",
      "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>COUNT(*)</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>5341</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    COUNT(*)\n",
    "FROM \n",
    "    posts\n",
    "WHERE \n",
    "    AcceptedAnswerId >0\n",
    "        \"\"\"\n",
    "store_query(\"Task 2\", \"How many posts have an accepted answer?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.889845900Z",
     "start_time": "2024-01-14T16:35:14.617300200Z"
    }
   },
   "execution_count": 65
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   AverageReputation\n0         312.350912",
      "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>AverageReputation</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>312.350912</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    AVG(Reputation) AS AverageReputation\n",
    "FROM \n",
    "    users\n",
    "\"\"\"\n",
    "store_query(\"Task 2\", \"What is the average reputation of table users?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:14.889845900Z",
     "start_time": "2024-01-14T16:35:14.815020300Z"
    }
   },
   "execution_count": 66
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   MinReputation  MaxReputation\n0              1         228662",
      "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>MinReputation</th>\n      <th>MaxReputation</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1</td>\n      <td>228662</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    MIN(Reputation) AS MinReputation,\n",
    "    MAX(Reputation) AS MaxReputation\n",
    "FROM \n",
    "    users\n",
    "\"\"\"\n",
    "store_query(\"Task 2\", \"What are the min and max reputation of users?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.001769400Z",
     "start_time": "2024-01-14T16:35:14.845380800Z"
    }
   },
   "execution_count": 67
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "       Id  BodyLength\n0  388566        2270\n1  394118         512\n2  431370         811\n3  398646        2148\n4  434128        1172",
      "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>BodyLength</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>388566</td>\n      <td>2270</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>394118</td>\n      <td>512</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>431370</td>\n      <td>811</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>398646</td>\n      <td>2148</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>434128</td>\n      <td>1172</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    Id,\n",
    "    LENGTH(Body) AS BodyLength\n",
    "FROM \n",
    "    posts\n",
    "ORDER BY \n",
    "    ViewCount DESC\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "store_query(\"Task 2\", \"What is the length of the body of 5 most viewed posts?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.139042100Z",
     "start_time": "2024-01-14T16:35:14.873638900Z"
    }
   },
   "execution_count": 68
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   NumDistinctLocations\n0                  1900",
      "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>NumDistinctLocations</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>1900</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    COUNT(DISTINCT Location) AS NumDistinctLocations\n",
    "FROM \n",
    "    users\n",
    "\"\"\"\n",
    "store_query(\"Task 2\", \"How many different locations are there in the users table?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.172262Z",
     "start_time": "2024-01-14T16:35:15.004657700Z"
    }
   },
   "execution_count": 69
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "                 Location  UserCount\n0                 Germany        117\n1                   India        100\n2           United States         69\n3           Paris, France         66\n4  London, United Kingdom         63",
      "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>Location</th>\n      <th>UserCount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Germany</td>\n      <td>117</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>India</td>\n      <td>100</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>United States</td>\n      <td>69</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Paris, France</td>\n      <td>66</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>London, United Kingdom</td>\n      <td>63</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    Location,\n",
    "    COUNT(*) AS UserCount\n",
    "FROM \n",
    "    users\n",
    "WHERE Location IS NOT NULL AND Location != ''\n",
    "GROUP BY \n",
    "    Location\n",
    "ORDER BY \n",
    "    UserCount DESC\n",
    "LIMIT 5\n",
    "\"\"\"\n",
    "store_query(\"Task 2\", \"What are the top 5 locations of users?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.172262Z",
     "start_time": "2024-01-14T16:35:15.040877Z"
    }
   },
   "execution_count": 70
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "data": {
      "text/plain": "   DayOfWeek  TotalViewCount  Percentage\n0   Thursday          331507   16.819476\n1  Wednesday          330506   16.768689\n2    Tuesday          320382   16.255034\n3     Monday          311546   15.806727\n4     Friday          267324   13.563061\n5     Sunday          234459   11.895609\n6   Saturday          175247    8.891404",
      "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>DayOfWeek</th>\n      <th>TotalViewCount</th>\n      <th>Percentage</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Thursday</td>\n      <td>331507</td>\n      <td>16.819476</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Wednesday</td>\n      <td>330506</td>\n      <td>16.768689</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Tuesday</td>\n      <td>320382</td>\n      <td>16.255034</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Monday</td>\n      <td>311546</td>\n      <td>15.806727</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Friday</td>\n      <td>267324</td>\n      <td>13.563061</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>Sunday</td>\n      <td>234459</td>\n      <td>11.895609</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>Saturday</td>\n      <td>175247</td>\n      <td>8.891404</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_query = \"\"\"\n",
    "WITH DayOfWeekViewCounts AS (\n",
    "    SELECT \n",
    "        CASE\n",
    "            WHEN STRFTIME('%w', CreationDate) = '0' THEN 'Sunday'\n",
    "            WHEN STRFTIME('%w', CreationDate) = '1' THEN 'Monday'\n",
    "            WHEN STRFTIME('%w', CreationDate) = '2' THEN 'Tuesday'\n",
    "            WHEN STRFTIME('%w', CreationDate) = '3' THEN 'Wednesday'\n",
    "            WHEN STRFTIME('%w', CreationDate) = '4' THEN 'Thursday'\n",
    "            WHEN STRFTIME('%w', CreationDate) = '5' THEN 'Friday'\n",
    "            WHEN STRFTIME('%w', CreationDate) = '6' THEN 'Saturday'\n",
    "        END AS DayOfWeek,\n",
    "        SUM(ViewCount) AS TotalViewCount\n",
    "    FROM \n",
    "        posts\n",
    "    GROUP BY \n",
    "        DayOfWeek\n",
    ")\n",
    "\n",
    "SELECT \n",
    "    DayOfWeek,\n",
    "    TotalViewCount,\n",
    "    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage\n",
    "FROM \n",
    "    DayOfWeekViewCounts\n",
    "ORDER BY \n",
    "    Percentage DESC\n",
    "\"\"\"\n",
    "store_query(\"Task 2\", \"Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.482986Z",
     "start_time": "2024-01-14T16:35:15.068218100Z"
    }
   },
   "execution_count": 71
  },
  {
   "cell_type": "code",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Posts', '\\n    CREATE TABLE \"posts\" (\\n    \"Id\" INTEGER,\\n    \"PostTypeId\" INTEGER,\\n    \"AcceptedAnswerId\" INTEGER,\\n    \"ParentId\" INTEGER,\\n    \"CreationDa ... (300 characters truncated) ... \"AnswerCount\" INTEGER,\\n    \"CommentCount\" INTEGER,\\n    \"FavoriteCount\" INTEGER,\\n    \"ClosedDate\" TEXT,\\n    \"CommunityOwnedDate\" TEXT\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Users', '\\n    CREATE TABLE \"users\" (\\n    \"Id\" INTEGER,\\n    \"Reputation\" INTEGER,\\n    \"CreationDate\" TEXT,\\n    \"DisplayName\" TEXT,\\n    \"LastAccessDate\" T ... (61 characters truncated) ... tMe\" TEXT,\\n    \"Views\" INTEGER,\\n    \"UpVotes\" INTEGER,\\n    \"DownVotes\" INTEGER,\\n    \"ProfileImageUrl\" TEXT,\\n    \"AccountId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Count number of rows in table Comments', '\\n    SELECT COUNT(*) FROM comments\\n'),\n ('Task 1', 'Count number of rows in table Users', '\\n    SELECT COUNT(*) FROM users\\n'),\n ('Task 1', 'Count number of rows in table Posts', '\\n    SELECT COUNT(*) FROM posts\\n'),\n ('Task 1', 'Five random posts', '\\n    SELECT * FROM posts order by random() limit 5\\n'),\n ('Task 1', 'Five random comments', '\\n    SELECT * FROM comments order by random() limit 5\\n'),\n ('Task 1', 'Five random users', '\\n    SELECT * FROM users order by random() limit 5\\n'),\n ('Task 2', 'How many posts have 0 comments?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount = 0\\n        '),\n ('Task 2', 'How many posts have 1 comments?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount = 1\\n        '),\n ('Task 2', 'How many posts have 2 comments or more?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount >= 2\\n        '),\n ('Task 2', 'Find the 5 posts with the highest viewcount', '\\nSELECT *\\nFROM posts\\nORDER BY ViewCount DESC\\n    LIMIT 5\\n        '),\n ('Task 2', 'Find the top 5 posts with the highest scores', '\\nSELECT \\n    *\\nFROM \\n    posts\\nORDER BY \\n    Score DESC\\n    LIMIT 5\\n        '),\n ('Task 2', 'What are the 5 most frequent scores on posts?', '\\nSELECT \\n    Score, COUNT(*) AS Frequency\\nFROM \\n    posts\\nGROUP BY \\n    Score  \\nORDER BY \\n    Frequency DESC, Score\\n    LIMIT 5\\n        '),\n ('Task 2', \"How many posts have the keyword 'data' in their tags?\", \"\\nSELECT \\n    COUNT(*) AS NumPostsWithDataTag\\nFROM \\n    posts\\nWHERE \\n    Tags \\nLIKE \\n    '%data%' \\n        \"),\n ('Task 2', 'What are the 5 most frequent commentcount for posts?', '\\nSELECT \\n    commentcount, COUNT(*) AS Frequency\\nFROM \\n    posts\\nGROUP BY \\n    commentcount  \\nORDER BY \\n    Frequency DESC, commentcount\\n    LIMIT 5\\n        '),\n ('Task 2', 'How many posts have an accepted answer?', '\\nSELECT \\n    COUNT(*)\\nFROM \\n    posts\\nWHERE \\n    AcceptedAnswerId >0\\n        '),\n ('Task 2', 'What is the average reputation of table users?', '\\nSELECT \\n    AVG(Reputation) AS AverageReputation\\nFROM \\n    users\\n'),\n ('Task 2', 'What are the min and max reputation of users?', '\\nSELECT \\n    MIN(Reputation) AS MinReputation,\\n    MAX(Reputation) AS MaxReputation\\nFROM \\n    users\\n'),\n ('Task 2', 'What is the length of the body of 5 most viewed posts?', '\\nSELECT \\n    Id,\\n    LENGTH(Body) AS BodyLength\\nFROM \\n    posts\\nORDER BY \\n    ViewCount DESC\\nLIMIT 5\\n'),\n ('Task 2', 'How many different locations are there in the users table?', '\\nSELECT \\n    COUNT(DISTINCT Location) AS NumDistinctLocations\\nFROM \\n    users\\n'),\n ('Task 2', 'What are the top 5 locations of users?', \"\\nSELECT \\n    Location,\\n    COUNT(*) AS UserCount\\nFROM \\n    users\\nWHERE Location IS NOT NULL AND Location != ''\\nGROUP BY \\n    Location\\nORDER BY \\n    UserCount DESC\\nLIMIT 5\\n\"),\n ('Task 2', 'Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.', \"\\nWITH DayOfWeekViewCounts AS (\\n    SELECT \\n        CASE\\n            WHEN STRFTIME('%w', CreationDate) = '0' THEN 'Sunday'\\n            WHEN STRFT ... (557 characters truncated) ... iewCount,\\n    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage\\nFROM \\n    DayOfWeekViewCounts\\nORDER BY \\n    Percentage DESC\\n\")]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Posts</td>\n            <td><br>    CREATE TABLE &quot;posts&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostTypeId&quot; INTEGER,<br>    &quot;AcceptedAnswerId&quot; INTEGER,<br>    &quot;ParentId&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;Score&quot; INTEGER,<br>    &quot;ViewCount&quot; INTEGER,<br>    &quot;Body&quot; TEXT,<br>    &quot;OwnerUserId&quot; INTEGER,<br>    &quot;OwnerDisplayName&quot; TEXT,<br>    &quot;LastEditorUserId&quot; INTEGER,<br>    &quot;LastEditorDisplayName&quot; TEXT,<br>    &quot;LastEditDate&quot; TEXT,<br>    &quot;LastActivityDate&quot; TEXT,<br>    &quot;Title&quot; TEXT,<br>    &quot;Tags&quot; TEXT,<br>    &quot;AnswerCount&quot; INTEGER,<br>    &quot;CommentCount&quot; INTEGER,<br>    &quot;FavoriteCount&quot; INTEGER,<br>    &quot;ClosedDate&quot; TEXT,<br>    &quot;CommunityOwnedDate&quot; TEXT<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Users</td>\n            <td><br>    CREATE TABLE &quot;users&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;Reputation&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;DisplayName&quot; TEXT,<br>    &quot;LastAccessDate&quot; TEXT,<br>    &quot;WebsiteUrl&quot; TEXT,<br>    &quot;Location&quot; TEXT,<br>    &quot;AboutMe&quot; TEXT,<br>    &quot;Views&quot; INTEGER,<br>    &quot;UpVotes&quot; INTEGER,<br>    &quot;DownVotes&quot; INTEGER,<br>    &quot;ProfileImageUrl&quot; TEXT,<br>    &quot;AccountId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Comments</td>\n            <td><br>    SELECT COUNT(*) FROM comments<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Users</td>\n            <td><br>    SELECT COUNT(*) FROM users<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Posts</td>\n            <td><br>    SELECT COUNT(*) FROM posts<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random posts</td>\n            <td><br>    SELECT * FROM posts order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random comments</td>\n            <td><br>    SELECT * FROM comments order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random users</td>\n            <td><br>    SELECT * FROM users order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 0 comments?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount = 0<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 1 comments?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount = 1<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 2 comments or more?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount &gt;= 2<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Find the 5 posts with the highest viewcount</td>\n            <td><br>SELECT *<br>FROM posts<br>ORDER BY ViewCount DESC<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Find the top 5 posts with the highest scores</td>\n            <td><br>SELECT <br>    *<br>FROM <br>    posts<br>ORDER BY <br>    Score DESC<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the 5 most frequent scores on posts?</td>\n            <td><br>SELECT <br>    Score, COUNT(*) AS Frequency<br>FROM <br>    posts<br>GROUP BY <br>    Score  <br>ORDER BY <br>    Frequency DESC, Score<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have the keyword &#x27;data&#x27; in their tags?</td>\n            <td><br>SELECT <br>    COUNT(*) AS NumPostsWithDataTag<br>FROM <br>    posts<br>WHERE <br>    Tags <br>LIKE <br>    &#x27;%data%&#x27; <br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the 5 most frequent commentcount for posts?</td>\n            <td><br>SELECT <br>    commentcount, COUNT(*) AS Frequency<br>FROM <br>    posts<br>GROUP BY <br>    commentcount  <br>ORDER BY <br>    Frequency DESC, commentcount<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have an accepted answer?</td>\n            <td><br>SELECT <br>    COUNT(*)<br>FROM <br>    posts<br>WHERE <br>    AcceptedAnswerId &gt;0<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What is the average reputation of table users?</td>\n            <td><br>SELECT <br>    AVG(Reputation) AS AverageReputation<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the min and max reputation of users?</td>\n            <td><br>SELECT <br>    MIN(Reputation) AS MinReputation,<br>    MAX(Reputation) AS MaxReputation<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What is the length of the body of 5 most viewed posts?</td>\n            <td><br>SELECT <br>    Id,<br>    LENGTH(Body) AS BodyLength<br>FROM <br>    posts<br>ORDER BY <br>    ViewCount DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many different locations are there in the users table?</td>\n            <td><br>SELECT <br>    COUNT(DISTINCT Location) AS NumDistinctLocations<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the top 5 locations of users?</td>\n            <td><br>SELECT <br>    Location,<br>    COUNT(*) AS UserCount<br>FROM <br>    users<br>WHERE Location IS NOT NULL AND Location != &#x27;&#x27;<br>GROUP BY <br>    Location<br>ORDER BY <br>    UserCount DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.</td>\n            <td><br>WITH DayOfWeekViewCounts AS (<br>    SELECT <br>        CASE<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;0&#x27; THEN &#x27;Sunday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;1&#x27; THEN &#x27;Monday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;2&#x27; THEN &#x27;Tuesday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;3&#x27; THEN &#x27;Wednesday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;4&#x27; THEN &#x27;Thursday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;5&#x27; THEN &#x27;Friday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;6&#x27; THEN &#x27;Saturday&#x27;<br>        END AS DayOfWeek,<br>        SUM(ViewCount) AS TotalViewCount<br>    FROM <br>        posts<br>    GROUP BY <br>        DayOfWeek<br>)<br><br>SELECT <br>    DayOfWeek,<br>    TotalViewCount,<br>    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage<br>FROM <br>    DayOfWeekViewCounts<br>ORDER BY <br>    Percentage DESC<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check queries\n",
    "%sql SELECT * FROM queries\n"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.489051300Z",
     "start_time": "2024-01-14T16:35:15.307420600Z"
    }
   },
   "execution_count": 72
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 3: Cross Table Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lifecycle Stage: Analyze\n",
    "Let's continue the analysis with our multi-table queries.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cross Table Queries\n",
    "\n",
    "From the Template SQL Queries there are a series of queries requested. You are responsible for coding the result. Use the pattern above to accomplish the coding objective.  Copy and paste the question (column B Action) into a markdown cell and answer the query. Use the pattern that you have been using above. \n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Enter your code below.  Just keep on inserting cells as you need them.\n",
    "    </font></div>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "outputs": [
    {
     "data": {
      "text/plain": "   NumPosts\n0     17189",
      "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>NumPosts</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>17189</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO How many posts have been created by a user that has a filled out the \"AboutMe\" section?\n",
    "sql_query = \"\"\"\n",
    "SELECT COUNT(*) AS NumPosts\n",
    "FROM posts p\n",
    "JOIN users u ON p.OwnerUserId = u.Id\n",
    "WHERE u.AboutMe IS NOT NULL AND u.AboutMe != '';\n",
    "\n",
    "\"\"\"\n",
    "store_query(\"Task 3\", \"How many posts have been created by a user that has a filled out the 'AboutMe' section?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.775477400Z",
     "start_time": "2024-01-14T16:35:15.325617400Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "outputs": [
    {
     "data": {
      "text/plain": "   AvgNumPosts\n0     4.274807",
      "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>AvgNumPosts</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>4.274807</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO Considering only the users with an \"AboutMe,\" how many posts are there per user?\n",
    "sql_query = \"\"\"\n",
    "SELECT \n",
    "    COUNT(*) / (\n",
    "        SELECT CAST(COUNT(*) AS FLOAT)\n",
    "        FROM users\n",
    "        WHERE AboutMe != ''\n",
    "    ) AS AvgNumPosts\n",
    "FROM \n",
    "    posts p\n",
    "JOIN \n",
    "    users u ON u.Id = p.OwnerUserId\n",
    "WHERE \n",
    "    u.AboutMe IS NOT NULL AND TRIM(u.AboutMe) != '';\n",
    "\"\"\"\n",
    "store_query(\"Task 3\", \"Considering only the users with an 'AboutMe,' how many posts are there per user?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:15.981179Z",
     "start_time": "2024-01-14T16:35:15.583169300Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.124501800Z",
     "start_time": "2024-01-14T16:35:15.855438400Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "   PostId  PostScore  CCount\n0  386853          1      66\n1  386556          4      34\n2  395232         -1      31\n3  418910         35      31\n4  402987          1      27\n5  386075         10      26\n6  394118         64      24\n7  398828          6      23\n8  402950          1      23\n9  396111          1      22",
      "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>PostId</th>\n      <th>PostScore</th>\n      <th>CCount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>386853</td>\n      <td>1</td>\n      <td>66</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>386556</td>\n      <td>4</td>\n      <td>34</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>395232</td>\n      <td>-1</td>\n      <td>31</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>418910</td>\n      <td>35</td>\n      <td>31</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>402987</td>\n      <td>1</td>\n      <td>27</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>386075</td>\n      <td>10</td>\n      <td>26</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>394118</td>\n      <td>64</td>\n      <td>24</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>398828</td>\n      <td>6</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>402950</td>\n      <td>1</td>\n      <td>23</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>396111</td>\n      <td>1</td>\n      <td>22</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO Not taking into account the CommentCount field in the table posts, what are the Top 10 posts in terms of number of comments?\n",
    "sql_query = \"\"\"\n",
    "SELECT\n",
    "    p.Id AS PostId,\n",
    "    p.Score AS PostScore,\n",
    "    COUNT(*) AS CCount\n",
    "FROM\n",
    "    posts p\n",
    "LEFT JOIN\n",
    "    comments c ON p.Id = c.PostId\n",
    "GROUP BY\n",
    "    p.Id\n",
    "ORDER BY\n",
    "    CCount DESC\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "store_query(\"Task 3\", \"Not taking into account the CommentCount field in the table posts, what are the Top 10 posts in terms of number of comments?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "outputs": [
    {
     "data": {
      "text/plain": "   PostId  PostScore  CommentScore  CumulativeScore  CommentCount\n0  394118         64           242             1778            24\n1  394128        100            69             1569            15\n2  418910         35             9             1094            31\n3  398653         65            46             1021            15\n4  388578         80            61              941            11\n5  388566         56            45              885            15\n6  396818         44            16              808            18\n7  418814         51             9              621            12\n8  394258         39            24              570            14\n9  394439         35             6              566            16",
      "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>PostId</th>\n      <th>PostScore</th>\n      <th>CommentScore</th>\n      <th>CumulativeScore</th>\n      <th>CommentCount</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>394118</td>\n      <td>64</td>\n      <td>242</td>\n      <td>1778</td>\n      <td>24</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>394128</td>\n      <td>100</td>\n      <td>69</td>\n      <td>1569</td>\n      <td>15</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>418910</td>\n      <td>35</td>\n      <td>9</td>\n      <td>1094</td>\n      <td>31</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>398653</td>\n      <td>65</td>\n      <td>46</td>\n      <td>1021</td>\n      <td>15</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>388578</td>\n      <td>80</td>\n      <td>61</td>\n      <td>941</td>\n      <td>11</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>388566</td>\n      <td>56</td>\n      <td>45</td>\n      <td>885</td>\n      <td>15</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>396818</td>\n      <td>44</td>\n      <td>16</td>\n      <td>808</td>\n      <td>18</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>418814</td>\n      <td>51</td>\n      <td>9</td>\n      <td>621</td>\n      <td>12</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>394258</td>\n      <td>39</td>\n      <td>24</td>\n      <td>570</td>\n      <td>14</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>394439</td>\n      <td>35</td>\n      <td>6</td>\n      <td>566</td>\n      <td>16</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO What are the Top 10 posts which have the highest cumulative (post score + comment score) score? \n",
    "sql_query = \"\"\"\n",
    "SELECT\n",
    "    p.Id AS PostId,\n",
    "    p.Score AS PostScore,\n",
    "    SUM(c.Score) AS CommentScore,\n",
    "    SUM(p.Score) + SUM(c.Score) AS CumulativeScore,\n",
    "    COUNT(c.Id) AS CommentCount\n",
    "FROM\n",
    "    posts p\n",
    "LEFT JOIN\n",
    "    comments c ON p.Id = c.PostId\n",
    "GROUP BY\n",
    "    p.Id, p.Score\n",
    "ORDER BY\n",
    "    CumulativeScore DESC\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "store_query(\"Task 3\", \"What are the Top 10 posts which have the highest cumulative (post score + comment score) score?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.392124Z",
     "start_time": "2024-01-14T16:35:16.090961400Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "outputs": [
    {
     "data": {
      "text/plain": "   UserId  Frequency\n0     919       3301\n1     805       1153\n2  143489       1024\n3   11887        805\n4   85665        691\n5  164061        540\n6   22047        536\n7  158565        504\n8    7962        492\n9   35989        470",
      "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>UserId</th>\n      <th>Frequency</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>919</td>\n      <td>3301</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>805</td>\n      <td>1153</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>143489</td>\n      <td>1024</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>11887</td>\n      <td>805</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>85665</td>\n      <td>691</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>164061</td>\n      <td>540</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>22047</td>\n      <td>536</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>158565</td>\n      <td>504</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>7962</td>\n      <td>492</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>35989</td>\n      <td>470</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO Who are the top 10 users who comment the most? \n",
    "sql_query = \"\"\"\n",
    "SELECT\n",
    "    u.Id AS UserId,\n",
    "    COUNT(*) AS Frequency\n",
    "FROM\n",
    "    comments c\n",
    "JOIN\n",
    "    users u ON c.UserId = u.Id\n",
    "GROUP BY\n",
    "    u.Id\n",
    "ORDER BY\n",
    "    Frequency DESC\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "store_query(\"Task 3\", \"Who are the top 10 users who comment the most?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.555586100Z",
     "start_time": "2024-01-14T16:35:16.363420900Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "outputs": [
    {
     "data": {
      "text/plain": "   OwnerUserId  Frequency\n0       204068        637\n1        85665        545\n2       173082        435\n3        11887        435\n4          686        386\n5         1352        285\n6         3382        274\n7         7224        233\n8        35989        230\n9          805        230",
      "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>OwnerUserId</th>\n      <th>Frequency</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>204068</td>\n      <td>637</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>85665</td>\n      <td>545</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>173082</td>\n      <td>435</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>11887</td>\n      <td>435</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>686</td>\n      <td>386</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>1352</td>\n      <td>285</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>3382</td>\n      <td>274</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>7224</td>\n      <td>233</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>35989</td>\n      <td>230</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>805</td>\n      <td>230</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TODO Who are the top 10 users who post the most? \n",
    "sql_query = \"\"\"\n",
    "SELECT\n",
    "    u.Id AS OwnerUserId,\n",
    "    COUNT(*) AS Frequency\n",
    "FROM\n",
    "    posts p\n",
    "JOIN\n",
    "    users u ON p.OwnerUserId = u.Id\n",
    "GROUP BY\n",
    "    u.Id\n",
    "ORDER BY\n",
    "    Frequency DESC\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "store_query(\"Task 3\", \"Who are the top 10 users who post the most?\", sql_query)\n",
    "result = pd.read_sql(sql_query, con)\n",
    "result"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.772114Z",
     "start_time": "2024-01-14T16:35:16.491502800Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Task 4: Check the Queries Table\n",
    "\n",
    "Now let's tidy up and check the queries table.\n",
    "\n",
    "First let's check it's contents:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.812798700Z",
     "start_time": "2024-01-14T16:35:16.759168500Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Posts', '\\n    CREATE TABLE \"posts\" (\\n    \"Id\" INTEGER,\\n    \"PostTypeId\" INTEGER,\\n    \"AcceptedAnswerId\" INTEGER,\\n    \"ParentId\" INTEGER,\\n    \"CreationDa ... (300 characters truncated) ... \"AnswerCount\" INTEGER,\\n    \"CommentCount\" INTEGER,\\n    \"FavoriteCount\" INTEGER,\\n    \"ClosedDate\" TEXT,\\n    \"CommunityOwnedDate\" TEXT\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Users', '\\n    CREATE TABLE \"users\" (\\n    \"Id\" INTEGER,\\n    \"Reputation\" INTEGER,\\n    \"CreationDate\" TEXT,\\n    \"DisplayName\" TEXT,\\n    \"LastAccessDate\" T ... (61 characters truncated) ... tMe\" TEXT,\\n    \"Views\" INTEGER,\\n    \"UpVotes\" INTEGER,\\n    \"DownVotes\" INTEGER,\\n    \"ProfileImageUrl\" TEXT,\\n    \"AccountId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Count number of rows in table Comments', '\\n    SELECT COUNT(*) FROM comments\\n'),\n ('Task 1', 'Count number of rows in table Users', '\\n    SELECT COUNT(*) FROM users\\n'),\n ('Task 1', 'Count number of rows in table Posts', '\\n    SELECT COUNT(*) FROM posts\\n'),\n ('Task 1', 'Five random posts', '\\n    SELECT * FROM posts order by random() limit 5\\n'),\n ('Task 1', 'Five random comments', '\\n    SELECT * FROM comments order by random() limit 5\\n'),\n ('Task 1', 'Five random users', '\\n    SELECT * FROM users order by random() limit 5\\n'),\n ('Task 2', 'How many posts have 0 comments?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount = 0\\n        '),\n ('Task 2', 'How many posts have 1 comments?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount = 1\\n        '),\n ('Task 2', 'How many posts have 2 comments or more?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount >= 2\\n        '),\n ('Task 2', 'Find the 5 posts with the highest viewcount', '\\nSELECT *\\nFROM posts\\nORDER BY ViewCount DESC\\n    LIMIT 5\\n        '),\n ('Task 2', 'Find the top 5 posts with the highest scores', '\\nSELECT \\n    *\\nFROM \\n    posts\\nORDER BY \\n    Score DESC\\n    LIMIT 5\\n        '),\n ('Task 2', 'What are the 5 most frequent scores on posts?', '\\nSELECT \\n    Score, COUNT(*) AS Frequency\\nFROM \\n    posts\\nGROUP BY \\n    Score  \\nORDER BY \\n    Frequency DESC, Score\\n    LIMIT 5\\n        '),\n ('Task 2', \"How many posts have the keyword 'data' in their tags?\", \"\\nSELECT \\n    COUNT(*) AS NumPostsWithDataTag\\nFROM \\n    posts\\nWHERE \\n    Tags \\nLIKE \\n    '%data%' \\n        \"),\n ('Task 2', 'What are the 5 most frequent commentcount for posts?', '\\nSELECT \\n    commentcount, COUNT(*) AS Frequency\\nFROM \\n    posts\\nGROUP BY \\n    commentcount  \\nORDER BY \\n    Frequency DESC, commentcount\\n    LIMIT 5\\n        '),\n ('Task 2', 'How many posts have an accepted answer?', '\\nSELECT \\n    COUNT(*)\\nFROM \\n    posts\\nWHERE \\n    AcceptedAnswerId >0\\n        '),\n ('Task 2', 'What is the average reputation of table users?', '\\nSELECT \\n    AVG(Reputation) AS AverageReputation\\nFROM \\n    users\\n'),\n ('Task 2', 'What are the min and max reputation of users?', '\\nSELECT \\n    MIN(Reputation) AS MinReputation,\\n    MAX(Reputation) AS MaxReputation\\nFROM \\n    users\\n'),\n ('Task 2', 'What is the length of the body of 5 most viewed posts?', '\\nSELECT \\n    Id,\\n    LENGTH(Body) AS BodyLength\\nFROM \\n    posts\\nORDER BY \\n    ViewCount DESC\\nLIMIT 5\\n'),\n ('Task 2', 'How many different locations are there in the users table?', '\\nSELECT \\n    COUNT(DISTINCT Location) AS NumDistinctLocations\\nFROM \\n    users\\n'),\n ('Task 2', 'What are the top 5 locations of users?', \"\\nSELECT \\n    Location,\\n    COUNT(*) AS UserCount\\nFROM \\n    users\\nWHERE Location IS NOT NULL AND Location != ''\\nGROUP BY \\n    Location\\nORDER BY \\n    UserCount DESC\\nLIMIT 5\\n\"),\n ('Task 2', 'Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.', \"\\nWITH DayOfWeekViewCounts AS (\\n    SELECT \\n        CASE\\n            WHEN STRFTIME('%w', CreationDate) = '0' THEN 'Sunday'\\n            WHEN STRFT ... (557 characters truncated) ... iewCount,\\n    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage\\nFROM \\n    DayOfWeekViewCounts\\nORDER BY \\n    Percentage DESC\\n\"),\n ('Task 3', \"How many posts have been created by a user that has a filled out the 'AboutMe' section?\", \"\\nSELECT COUNT(*) AS NumPosts\\nFROM posts p\\nJOIN users u ON p.OwnerUserId = u.Id\\nWHERE u.AboutMe IS NOT NULL AND u.AboutMe != '';\\n\\n\"),\n ('Task 3', \"Considering only the users with an 'AboutMe,' how many posts are there per user?\", \"\\nSELECT \\n    COUNT(*) / (\\n        SELECT CAST(COUNT(*) AS FLOAT)\\n        FROM users\\n        WHERE AboutMe != ''\\n    ) AS AvgNumPosts\\nFROM \\n    posts p\\nJOIN \\n    users u ON u.Id = p.OwnerUserId\\nWHERE \\n    u.AboutMe IS NOT NULL AND TRIM(u.AboutMe) != '';\\n\"),\n ('Task 3', 'Not taking into account the CommentCount field in the table posts, what are the Top 10 posts in terms of number of comments?', '\\nSELECT\\n    p.Id AS PostId,\\n    p.Score AS PostScore,\\n    COUNT(*) AS CCount\\nFROM\\n    posts p\\nLEFT JOIN\\n    comments c ON p.Id = c.PostId\\nGROUP BY\\n    p.Id\\nORDER BY\\n    CCount DESC\\nLIMIT 10;\\n'),\n ('Task 3', 'What are the Top 10 posts which have the highest cumulative (post score + comment score) score?', '\\nSELECT\\n    p.Id AS PostId,\\n    p.Score AS PostScore,\\n    SUM(c.Score) AS CommentScore,\\n    SUM(p.Score) + SUM(c.Score) AS CumulativeScore,\\n    ... (22 characters truncated) ... tCount\\nFROM\\n    posts p\\nLEFT JOIN\\n    comments c ON p.Id = c.PostId\\nGROUP BY\\n    p.Id, p.Score\\nORDER BY\\n    CumulativeScore DESC\\nLIMIT 10;\\n'),\n ('Task 3', 'Who are the top 10 users who comment the most?', '\\nSELECT\\n    u.Id AS UserId,\\n    COUNT(*) AS Frequency\\nFROM\\n    comments c\\nJOIN\\n    users u ON c.UserId = u.Id\\nGROUP BY\\n    u.Id\\nORDER BY\\n    Frequency DESC\\nLIMIT 10;\\n'),\n ('Task 3', 'Who are the top 10 users who post the most?', '\\nSELECT\\n    u.Id AS OwnerUserId,\\n    COUNT(*) AS Frequency\\nFROM\\n    posts p\\nJOIN\\n    users u ON p.OwnerUserId = u.Id\\nGROUP BY\\n    u.Id\\nORDER BY\\n    Frequency DESC\\nLIMIT 10;\\n')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Posts</td>\n            <td><br>    CREATE TABLE &quot;posts&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostTypeId&quot; INTEGER,<br>    &quot;AcceptedAnswerId&quot; INTEGER,<br>    &quot;ParentId&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;Score&quot; INTEGER,<br>    &quot;ViewCount&quot; INTEGER,<br>    &quot;Body&quot; TEXT,<br>    &quot;OwnerUserId&quot; INTEGER,<br>    &quot;OwnerDisplayName&quot; TEXT,<br>    &quot;LastEditorUserId&quot; INTEGER,<br>    &quot;LastEditorDisplayName&quot; TEXT,<br>    &quot;LastEditDate&quot; TEXT,<br>    &quot;LastActivityDate&quot; TEXT,<br>    &quot;Title&quot; TEXT,<br>    &quot;Tags&quot; TEXT,<br>    &quot;AnswerCount&quot; INTEGER,<br>    &quot;CommentCount&quot; INTEGER,<br>    &quot;FavoriteCount&quot; INTEGER,<br>    &quot;ClosedDate&quot; TEXT,<br>    &quot;CommunityOwnedDate&quot; TEXT<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Users</td>\n            <td><br>    CREATE TABLE &quot;users&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;Reputation&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;DisplayName&quot; TEXT,<br>    &quot;LastAccessDate&quot; TEXT,<br>    &quot;WebsiteUrl&quot; TEXT,<br>    &quot;Location&quot; TEXT,<br>    &quot;AboutMe&quot; TEXT,<br>    &quot;Views&quot; INTEGER,<br>    &quot;UpVotes&quot; INTEGER,<br>    &quot;DownVotes&quot; INTEGER,<br>    &quot;ProfileImageUrl&quot; TEXT,<br>    &quot;AccountId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Comments</td>\n            <td><br>    SELECT COUNT(*) FROM comments<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Users</td>\n            <td><br>    SELECT COUNT(*) FROM users<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Posts</td>\n            <td><br>    SELECT COUNT(*) FROM posts<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random posts</td>\n            <td><br>    SELECT * FROM posts order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random comments</td>\n            <td><br>    SELECT * FROM comments order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random users</td>\n            <td><br>    SELECT * FROM users order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 0 comments?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount = 0<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 1 comments?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount = 1<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 2 comments or more?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount &gt;= 2<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Find the 5 posts with the highest viewcount</td>\n            <td><br>SELECT *<br>FROM posts<br>ORDER BY ViewCount DESC<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Find the top 5 posts with the highest scores</td>\n            <td><br>SELECT <br>    *<br>FROM <br>    posts<br>ORDER BY <br>    Score DESC<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the 5 most frequent scores on posts?</td>\n            <td><br>SELECT <br>    Score, COUNT(*) AS Frequency<br>FROM <br>    posts<br>GROUP BY <br>    Score  <br>ORDER BY <br>    Frequency DESC, Score<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have the keyword &#x27;data&#x27; in their tags?</td>\n            <td><br>SELECT <br>    COUNT(*) AS NumPostsWithDataTag<br>FROM <br>    posts<br>WHERE <br>    Tags <br>LIKE <br>    &#x27;%data%&#x27; <br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the 5 most frequent commentcount for posts?</td>\n            <td><br>SELECT <br>    commentcount, COUNT(*) AS Frequency<br>FROM <br>    posts<br>GROUP BY <br>    commentcount  <br>ORDER BY <br>    Frequency DESC, commentcount<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have an accepted answer?</td>\n            <td><br>SELECT <br>    COUNT(*)<br>FROM <br>    posts<br>WHERE <br>    AcceptedAnswerId &gt;0<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What is the average reputation of table users?</td>\n            <td><br>SELECT <br>    AVG(Reputation) AS AverageReputation<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the min and max reputation of users?</td>\n            <td><br>SELECT <br>    MIN(Reputation) AS MinReputation,<br>    MAX(Reputation) AS MaxReputation<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What is the length of the body of 5 most viewed posts?</td>\n            <td><br>SELECT <br>    Id,<br>    LENGTH(Body) AS BodyLength<br>FROM <br>    posts<br>ORDER BY <br>    ViewCount DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many different locations are there in the users table?</td>\n            <td><br>SELECT <br>    COUNT(DISTINCT Location) AS NumDistinctLocations<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the top 5 locations of users?</td>\n            <td><br>SELECT <br>    Location,<br>    COUNT(*) AS UserCount<br>FROM <br>    users<br>WHERE Location IS NOT NULL AND Location != &#x27;&#x27;<br>GROUP BY <br>    Location<br>ORDER BY <br>    UserCount DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.</td>\n            <td><br>WITH DayOfWeekViewCounts AS (<br>    SELECT <br>        CASE<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;0&#x27; THEN &#x27;Sunday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;1&#x27; THEN &#x27;Monday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;2&#x27; THEN &#x27;Tuesday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;3&#x27; THEN &#x27;Wednesday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;4&#x27; THEN &#x27;Thursday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;5&#x27; THEN &#x27;Friday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;6&#x27; THEN &#x27;Saturday&#x27;<br>        END AS DayOfWeek,<br>        SUM(ViewCount) AS TotalViewCount<br>    FROM <br>        posts<br>    GROUP BY <br>        DayOfWeek<br>)<br><br>SELECT <br>    DayOfWeek,<br>    TotalViewCount,<br>    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage<br>FROM <br>    DayOfWeekViewCounts<br>ORDER BY <br>    Percentage DESC<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>How many posts have been created by a user that has a filled out the &#x27;AboutMe&#x27; section?</td>\n            <td><br>SELECT COUNT(*) AS NumPosts<br>FROM posts p<br>JOIN users u ON p.OwnerUserId = u.Id<br>WHERE u.AboutMe IS NOT NULL AND u.AboutMe != &#x27;&#x27;;<br><br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Considering only the users with an &#x27;AboutMe,&#x27; how many posts are there per user?</td>\n            <td><br>SELECT <br>    COUNT(*) / (<br>        SELECT CAST(COUNT(*) AS FLOAT)<br>        FROM users<br>        WHERE AboutMe != &#x27;&#x27;<br>    ) AS AvgNumPosts<br>FROM <br>    posts p<br>JOIN <br>    users u ON u.Id = p.OwnerUserId<br>WHERE <br>    u.AboutMe IS NOT NULL AND TRIM(u.AboutMe) != &#x27;&#x27;;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Not taking into account the CommentCount field in the table posts, what are the Top 10 posts in terms of number of comments?</td>\n            <td><br>SELECT<br>    p.Id AS PostId,<br>    p.Score AS PostScore,<br>    COUNT(*) AS CCount<br>FROM<br>    posts p<br>LEFT JOIN<br>    comments c ON p.Id = c.PostId<br>GROUP BY<br>    p.Id<br>ORDER BY<br>    CCount DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>What are the Top 10 posts which have the highest cumulative (post score + comment score) score?</td>\n            <td><br>SELECT<br>    p.Id AS PostId,<br>    p.Score AS PostScore,<br>    SUM(c.Score) AS CommentScore,<br>    SUM(p.Score) + SUM(c.Score) AS CumulativeScore,<br>    COUNT(c.Id) AS CommentCount<br>FROM<br>    posts p<br>LEFT JOIN<br>    comments c ON p.Id = c.PostId<br>GROUP BY<br>    p.Id, p.Score<br>ORDER BY<br>    CumulativeScore DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Who are the top 10 users who comment the most?</td>\n            <td><br>SELECT<br>    u.Id AS UserId,<br>    COUNT(*) AS Frequency<br>FROM<br>    comments c<br>JOIN<br>    users u ON c.UserId = u.Id<br>GROUP BY<br>    u.Id<br>ORDER BY<br>    Frequency DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Who are the top 10 users who post the most?</td>\n            <td><br>SELECT<br>    u.Id AS OwnerUserId,<br>    COUNT(*) AS Frequency<br>FROM<br>    posts p<br>JOIN<br>    users u ON p.OwnerUserId = u.Id<br>GROUP BY<br>    u.Id<br>ORDER BY<br>    Frequency DESC<br>LIMIT 10;<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Drop Duplicates\n",
    "\n",
    "You likely have some duplicates. Lets drop them. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.857704800Z",
     "start_time": "2024-01-14T16:35:16.775230400Z"
    }
   },
   "outputs": [],
   "source": [
    "# Read the queries table into pandas\n",
    "sql = 'SELECT * FROM queries'\n",
    "queries = pd.read_sql(sql, con)\n",
    "\n",
    "# Drop duplicates\n",
    "queries.drop_duplicates(inplace = True) # drop duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('Single Table Queries', 'Which 5 users have viewed the most times and what is the sum of those views per user?', '\\nSELECT Id, SUM(Views) AS TotalViews\\n    FROM Users\\n        GROUP BY Id\\n            ORDER BY TotalViews DESC\\n                LIMIT 5\\n                '),\n ('Task 1', 'Get the CREATE TABLE statement for Comments', '\\n    CREATE TABLE \"comments\" (\\n    \"Id\" INTEGER,\\n    \"PostId\" INTEGER,\\n    \"Score\" INTEGER,\\n    \"Text\" TEXT,\\n    \"CreationDate\" TEXT,\\n    \"UserId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Posts', '\\n    CREATE TABLE \"posts\" (\\n    \"Id\" INTEGER,\\n    \"PostTypeId\" INTEGER,\\n    \"AcceptedAnswerId\" INTEGER,\\n    \"ParentId\" INTEGER,\\n    \"CreationDa ... (300 characters truncated) ... \"AnswerCount\" INTEGER,\\n    \"CommentCount\" INTEGER,\\n    \"FavoriteCount\" INTEGER,\\n    \"ClosedDate\" TEXT,\\n    \"CommunityOwnedDate\" TEXT\\n    )\\n    '),\n ('Task 1', 'Get the CREATE TABLE statement for Users', '\\n    CREATE TABLE \"users\" (\\n    \"Id\" INTEGER,\\n    \"Reputation\" INTEGER,\\n    \"CreationDate\" TEXT,\\n    \"DisplayName\" TEXT,\\n    \"LastAccessDate\" T ... (61 characters truncated) ... tMe\" TEXT,\\n    \"Views\" INTEGER,\\n    \"UpVotes\" INTEGER,\\n    \"DownVotes\" INTEGER,\\n    \"ProfileImageUrl\" TEXT,\\n    \"AccountId\" INTEGER\\n    )\\n    '),\n ('Task 1', 'Count number of rows in table Comments', '\\n    SELECT COUNT(*) FROM comments\\n'),\n ('Task 1', 'Count number of rows in table Users', '\\n    SELECT COUNT(*) FROM users\\n'),\n ('Task 1', 'Count number of rows in table Posts', '\\n    SELECT COUNT(*) FROM posts\\n'),\n ('Task 1', 'Five random posts', '\\n    SELECT * FROM posts order by random() limit 5\\n'),\n ('Task 1', 'Five random comments', '\\n    SELECT * FROM comments order by random() limit 5\\n'),\n ('Task 1', 'Five random users', '\\n    SELECT * FROM users order by random() limit 5\\n'),\n ('Task 2', 'How many posts have 0 comments?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount = 0\\n        '),\n ('Task 2', 'How many posts have 1 comments?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount = 1\\n        '),\n ('Task 2', 'How many posts have 2 comments or more?', '\\nSELECT COUNT(*)\\nFROM posts\\nWHERE CommentCount >= 2\\n        '),\n ('Task 2', 'Find the 5 posts with the highest viewcount', '\\nSELECT *\\nFROM posts\\nORDER BY ViewCount DESC\\n    LIMIT 5\\n        '),\n ('Task 2', 'Find the top 5 posts with the highest scores', '\\nSELECT \\n    *\\nFROM \\n    posts\\nORDER BY \\n    Score DESC\\n    LIMIT 5\\n        '),\n ('Task 2', 'What are the 5 most frequent scores on posts?', '\\nSELECT \\n    Score, COUNT(*) AS Frequency\\nFROM \\n    posts\\nGROUP BY \\n    Score  \\nORDER BY \\n    Frequency DESC, Score\\n    LIMIT 5\\n        '),\n ('Task 2', \"How many posts have the keyword 'data' in their tags?\", \"\\nSELECT \\n    COUNT(*) AS NumPostsWithDataTag\\nFROM \\n    posts\\nWHERE \\n    Tags \\nLIKE \\n    '%data%' \\n        \"),\n ('Task 2', 'What are the 5 most frequent commentcount for posts?', '\\nSELECT \\n    commentcount, COUNT(*) AS Frequency\\nFROM \\n    posts\\nGROUP BY \\n    commentcount  \\nORDER BY \\n    Frequency DESC, commentcount\\n    LIMIT 5\\n        '),\n ('Task 2', 'How many posts have an accepted answer?', '\\nSELECT \\n    COUNT(*)\\nFROM \\n    posts\\nWHERE \\n    AcceptedAnswerId >0\\n        '),\n ('Task 2', 'What is the average reputation of table users?', '\\nSELECT \\n    AVG(Reputation) AS AverageReputation\\nFROM \\n    users\\n'),\n ('Task 2', 'What are the min and max reputation of users?', '\\nSELECT \\n    MIN(Reputation) AS MinReputation,\\n    MAX(Reputation) AS MaxReputation\\nFROM \\n    users\\n'),\n ('Task 2', 'What is the length of the body of 5 most viewed posts?', '\\nSELECT \\n    Id,\\n    LENGTH(Body) AS BodyLength\\nFROM \\n    posts\\nORDER BY \\n    ViewCount DESC\\nLIMIT 5\\n'),\n ('Task 2', 'How many different locations are there in the users table?', '\\nSELECT \\n    COUNT(DISTINCT Location) AS NumDistinctLocations\\nFROM \\n    users\\n'),\n ('Task 2', 'What are the top 5 locations of users?', \"\\nSELECT \\n    Location,\\n    COUNT(*) AS UserCount\\nFROM \\n    users\\nWHERE Location IS NOT NULL AND Location != ''\\nGROUP BY \\n    Location\\nORDER BY \\n    UserCount DESC\\nLIMIT 5\\n\"),\n ('Task 2', 'Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.', \"\\nWITH DayOfWeekViewCounts AS (\\n    SELECT \\n        CASE\\n            WHEN STRFTIME('%w', CreationDate) = '0' THEN 'Sunday'\\n            WHEN STRFT ... (557 characters truncated) ... iewCount,\\n    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage\\nFROM \\n    DayOfWeekViewCounts\\nORDER BY \\n    Percentage DESC\\n\"),\n ('Task 3', \"How many posts have been created by a user that has a filled out the 'AboutMe' section?\", \"\\nSELECT COUNT(*) AS NumPosts\\nFROM posts p\\nJOIN users u ON p.OwnerUserId = u.Id\\nWHERE u.AboutMe IS NOT NULL AND u.AboutMe != '';\\n\\n\"),\n ('Task 3', \"Considering only the users with an 'AboutMe,' how many posts are there per user?\", \"\\nSELECT \\n    COUNT(*) / (\\n        SELECT CAST(COUNT(*) AS FLOAT)\\n        FROM users\\n        WHERE AboutMe != ''\\n    ) AS AvgNumPosts\\nFROM \\n    posts p\\nJOIN \\n    users u ON u.Id = p.OwnerUserId\\nWHERE \\n    u.AboutMe IS NOT NULL AND TRIM(u.AboutMe) != '';\\n\"),\n ('Task 3', 'Not taking into account the CommentCount field in the table posts, what are the Top 10 posts in terms of number of comments?', '\\nSELECT\\n    p.Id AS PostId,\\n    p.Score AS PostScore,\\n    COUNT(*) AS CCount\\nFROM\\n    posts p\\nLEFT JOIN\\n    comments c ON p.Id = c.PostId\\nGROUP BY\\n    p.Id\\nORDER BY\\n    CCount DESC\\nLIMIT 10;\\n'),\n ('Task 3', 'What are the Top 10 posts which have the highest cumulative (post score + comment score) score?', '\\nSELECT\\n    p.Id AS PostId,\\n    p.Score AS PostScore,\\n    SUM(c.Score) AS CommentScore,\\n    SUM(p.Score) + SUM(c.Score) AS CumulativeScore,\\n    ... (22 characters truncated) ... tCount\\nFROM\\n    posts p\\nLEFT JOIN\\n    comments c ON p.Id = c.PostId\\nGROUP BY\\n    p.Id, p.Score\\nORDER BY\\n    CumulativeScore DESC\\nLIMIT 10;\\n'),\n ('Task 3', 'Who are the top 10 users who comment the most?', '\\nSELECT\\n    u.Id AS UserId,\\n    COUNT(*) AS Frequency\\nFROM\\n    comments c\\nJOIN\\n    users u ON c.UserId = u.Id\\nGROUP BY\\n    u.Id\\nORDER BY\\n    Frequency DESC\\nLIMIT 10;\\n'),\n ('Task 3', 'Who are the top 10 users who post the most?', '\\nSELECT\\n    u.Id AS OwnerUserId,\\n    COUNT(*) AS Frequency\\nFROM\\n    posts p\\nJOIN\\n    users u ON p.OwnerUserId = u.Id\\nGROUP BY\\n    u.Id\\nORDER BY\\n    Frequency DESC\\nLIMIT 10;\\n')]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>task</th>\n            <th>action</th>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>Single Table Queries</td>\n            <td>Which 5 users have viewed the most times and what is the sum of those views per user?</td>\n            <td><br>SELECT Id, SUM(Views) AS TotalViews<br>    FROM Users<br>        GROUP BY Id<br>            ORDER BY TotalViews DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Comments</td>\n            <td><br>    CREATE TABLE &quot;comments&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostId&quot; INTEGER,<br>    &quot;Score&quot; INTEGER,<br>    &quot;Text&quot; TEXT,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;UserId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Posts</td>\n            <td><br>    CREATE TABLE &quot;posts&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;PostTypeId&quot; INTEGER,<br>    &quot;AcceptedAnswerId&quot; INTEGER,<br>    &quot;ParentId&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;Score&quot; INTEGER,<br>    &quot;ViewCount&quot; INTEGER,<br>    &quot;Body&quot; TEXT,<br>    &quot;OwnerUserId&quot; INTEGER,<br>    &quot;OwnerDisplayName&quot; TEXT,<br>    &quot;LastEditorUserId&quot; INTEGER,<br>    &quot;LastEditorDisplayName&quot; TEXT,<br>    &quot;LastEditDate&quot; TEXT,<br>    &quot;LastActivityDate&quot; TEXT,<br>    &quot;Title&quot; TEXT,<br>    &quot;Tags&quot; TEXT,<br>    &quot;AnswerCount&quot; INTEGER,<br>    &quot;CommentCount&quot; INTEGER,<br>    &quot;FavoriteCount&quot; INTEGER,<br>    &quot;ClosedDate&quot; TEXT,<br>    &quot;CommunityOwnedDate&quot; TEXT<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Get the CREATE TABLE statement for Users</td>\n            <td><br>    CREATE TABLE &quot;users&quot; (<br>    &quot;Id&quot; INTEGER,<br>    &quot;Reputation&quot; INTEGER,<br>    &quot;CreationDate&quot; TEXT,<br>    &quot;DisplayName&quot; TEXT,<br>    &quot;LastAccessDate&quot; TEXT,<br>    &quot;WebsiteUrl&quot; TEXT,<br>    &quot;Location&quot; TEXT,<br>    &quot;AboutMe&quot; TEXT,<br>    &quot;Views&quot; INTEGER,<br>    &quot;UpVotes&quot; INTEGER,<br>    &quot;DownVotes&quot; INTEGER,<br>    &quot;ProfileImageUrl&quot; TEXT,<br>    &quot;AccountId&quot; INTEGER<br>    )<br>    </td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Comments</td>\n            <td><br>    SELECT COUNT(*) FROM comments<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Users</td>\n            <td><br>    SELECT COUNT(*) FROM users<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Count number of rows in table Posts</td>\n            <td><br>    SELECT COUNT(*) FROM posts<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random posts</td>\n            <td><br>    SELECT * FROM posts order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random comments</td>\n            <td><br>    SELECT * FROM comments order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 1</td>\n            <td>Five random users</td>\n            <td><br>    SELECT * FROM users order by random() limit 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 0 comments?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount = 0<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 1 comments?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount = 1<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have 2 comments or more?</td>\n            <td><br>SELECT COUNT(*)<br>FROM posts<br>WHERE CommentCount &gt;= 2<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Find the 5 posts with the highest viewcount</td>\n            <td><br>SELECT *<br>FROM posts<br>ORDER BY ViewCount DESC<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Find the top 5 posts with the highest scores</td>\n            <td><br>SELECT <br>    *<br>FROM <br>    posts<br>ORDER BY <br>    Score DESC<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the 5 most frequent scores on posts?</td>\n            <td><br>SELECT <br>    Score, COUNT(*) AS Frequency<br>FROM <br>    posts<br>GROUP BY <br>    Score  <br>ORDER BY <br>    Frequency DESC, Score<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have the keyword &#x27;data&#x27; in their tags?</td>\n            <td><br>SELECT <br>    COUNT(*) AS NumPostsWithDataTag<br>FROM <br>    posts<br>WHERE <br>    Tags <br>LIKE <br>    &#x27;%data%&#x27; <br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the 5 most frequent commentcount for posts?</td>\n            <td><br>SELECT <br>    commentcount, COUNT(*) AS Frequency<br>FROM <br>    posts<br>GROUP BY <br>    commentcount  <br>ORDER BY <br>    Frequency DESC, commentcount<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many posts have an accepted answer?</td>\n            <td><br>SELECT <br>    COUNT(*)<br>FROM <br>    posts<br>WHERE <br>    AcceptedAnswerId &gt;0<br>        </td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What is the average reputation of table users?</td>\n            <td><br>SELECT <br>    AVG(Reputation) AS AverageReputation<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the min and max reputation of users?</td>\n            <td><br>SELECT <br>    MIN(Reputation) AS MinReputation,<br>    MAX(Reputation) AS MaxReputation<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What is the length of the body of 5 most viewed posts?</td>\n            <td><br>SELECT <br>    Id,<br>    LENGTH(Body) AS BodyLength<br>FROM <br>    posts<br>ORDER BY <br>    ViewCount DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>How many different locations are there in the users table?</td>\n            <td><br>SELECT <br>    COUNT(DISTINCT Location) AS NumDistinctLocations<br>FROM <br>    users<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>What are the top 5 locations of users?</td>\n            <td><br>SELECT <br>    Location,<br>    COUNT(*) AS UserCount<br>FROM <br>    users<br>WHERE Location IS NOT NULL AND Location != &#x27;&#x27;<br>GROUP BY <br>    Location<br>ORDER BY <br>    UserCount DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td>Task 2</td>\n            <td>Rank the days of the week from highest to lowest in terms of the volume of ViewCount as a percentage.</td>\n            <td><br>WITH DayOfWeekViewCounts AS (<br>    SELECT <br>        CASE<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;0&#x27; THEN &#x27;Sunday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;1&#x27; THEN &#x27;Monday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;2&#x27; THEN &#x27;Tuesday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;3&#x27; THEN &#x27;Wednesday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;4&#x27; THEN &#x27;Thursday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;5&#x27; THEN &#x27;Friday&#x27;<br>            WHEN STRFTIME(&#x27;%w&#x27;, CreationDate) = &#x27;6&#x27; THEN &#x27;Saturday&#x27;<br>        END AS DayOfWeek,<br>        SUM(ViewCount) AS TotalViewCount<br>    FROM <br>        posts<br>    GROUP BY <br>        DayOfWeek<br>)<br><br>SELECT <br>    DayOfWeek,<br>    TotalViewCount,<br>    (TotalViewCount * 100.0 / SUM(TotalViewCount) OVER ()) AS Percentage<br>FROM <br>    DayOfWeekViewCounts<br>ORDER BY <br>    Percentage DESC<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>How many posts have been created by a user that has a filled out the &#x27;AboutMe&#x27; section?</td>\n            <td><br>SELECT COUNT(*) AS NumPosts<br>FROM posts p<br>JOIN users u ON p.OwnerUserId = u.Id<br>WHERE u.AboutMe IS NOT NULL AND u.AboutMe != &#x27;&#x27;;<br><br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Considering only the users with an &#x27;AboutMe,&#x27; how many posts are there per user?</td>\n            <td><br>SELECT <br>    COUNT(*) / (<br>        SELECT CAST(COUNT(*) AS FLOAT)<br>        FROM users<br>        WHERE AboutMe != &#x27;&#x27;<br>    ) AS AvgNumPosts<br>FROM <br>    posts p<br>JOIN <br>    users u ON u.Id = p.OwnerUserId<br>WHERE <br>    u.AboutMe IS NOT NULL AND TRIM(u.AboutMe) != &#x27;&#x27;;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Not taking into account the CommentCount field in the table posts, what are the Top 10 posts in terms of number of comments?</td>\n            <td><br>SELECT<br>    p.Id AS PostId,<br>    p.Score AS PostScore,<br>    COUNT(*) AS CCount<br>FROM<br>    posts p<br>LEFT JOIN<br>    comments c ON p.Id = c.PostId<br>GROUP BY<br>    p.Id<br>ORDER BY<br>    CCount DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>What are the Top 10 posts which have the highest cumulative (post score + comment score) score?</td>\n            <td><br>SELECT<br>    p.Id AS PostId,<br>    p.Score AS PostScore,<br>    SUM(c.Score) AS CommentScore,<br>    SUM(p.Score) + SUM(c.Score) AS CumulativeScore,<br>    COUNT(c.Id) AS CommentCount<br>FROM<br>    posts p<br>LEFT JOIN<br>    comments c ON p.Id = c.PostId<br>GROUP BY<br>    p.Id, p.Score<br>ORDER BY<br>    CumulativeScore DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Who are the top 10 users who comment the most?</td>\n            <td><br>SELECT<br>    u.Id AS UserId,<br>    COUNT(*) AS Frequency<br>FROM<br>    comments c<br>JOIN<br>    users u ON c.UserId = u.Id<br>GROUP BY<br>    u.Id<br>ORDER BY<br>    Frequency DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td>Task 3</td>\n            <td>Who are the top 10 users who post the most?</td>\n            <td><br>SELECT<br>    u.Id AS OwnerUserId,<br>    COUNT(*) AS Frequency<br>FROM<br>    posts p<br>JOIN<br>    users u ON p.OwnerUserId = u.Id<br>GROUP BY<br>    u.Id<br>ORDER BY<br>    Frequency DESC<br>LIMIT 10;<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT * FROM queries"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.892263100Z",
     "start_time": "2024-01-14T16:35:16.796942700Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[(31,)]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>count(*)</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td>31</td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql SELECT count(*) FROM Queries"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.950760900Z",
     "start_time": "2024-01-14T16:35:16.812798700Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Case Issues"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember, SQL is case insensitive. Pandas IS case sensitive. Lets deal with this now by making all of the text uppercase. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:16.985143800Z",
     "start_time": "2024-01-14T16:35:16.833228700Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "                    task                                             action  \\\n0   SINGLE TABLE QUERIES  WHICH 5 USERS HAVE VIEWED THE MOST TIMES AND W...   \n1                 TASK 1        GET THE CREATE TABLE STATEMENT FOR COMMENTS   \n2                 TASK 1           GET THE CREATE TABLE STATEMENT FOR POSTS   \n3                 TASK 1           GET THE CREATE TABLE STATEMENT FOR USERS   \n4                 TASK 1             COUNT NUMBER OF ROWS IN TABLE COMMENTS   \n5                 TASK 1                COUNT NUMBER OF ROWS IN TABLE USERS   \n6                 TASK 1                COUNT NUMBER OF ROWS IN TABLE POSTS   \n7                 TASK 1                                  FIVE RANDOM POSTS   \n8                 TASK 1                               FIVE RANDOM COMMENTS   \n9                 TASK 1                                  FIVE RANDOM USERS   \n10                TASK 2                    HOW MANY POSTS HAVE 0 COMMENTS?   \n11                TASK 2                    HOW MANY POSTS HAVE 1 COMMENTS?   \n12                TASK 2            HOW MANY POSTS HAVE 2 COMMENTS OR MORE?   \n13                TASK 2        FIND THE 5 POSTS WITH THE HIGHEST VIEWCOUNT   \n14                TASK 2       FIND THE TOP 5 POSTS WITH THE HIGHEST SCORES   \n15                TASK 2      WHAT ARE THE 5 MOST FREQUENT SCORES ON POSTS?   \n16                TASK 2  HOW MANY POSTS HAVE THE KEYWORD 'DATA' IN THEI...   \n17                TASK 2  WHAT ARE THE 5 MOST FREQUENT COMMENTCOUNT FOR ...   \n18                TASK 2            HOW MANY POSTS HAVE AN ACCEPTED ANSWER?   \n19                TASK 2     WHAT IS THE AVERAGE REPUTATION OF TABLE USERS?   \n20                TASK 2      WHAT ARE THE MIN AND MAX REPUTATION OF USERS?   \n21                TASK 2  WHAT IS THE LENGTH OF THE BODY OF 5 MOST VIEWE...   \n22                TASK 2  HOW MANY DIFFERENT LOCATIONS ARE THERE IN THE ...   \n23                TASK 2             WHAT ARE THE TOP 5 LOCATIONS OF USERS?   \n24                TASK 2  RANK THE DAYS OF THE WEEK FROM HIGHEST TO LOWE...   \n25                TASK 3  HOW MANY POSTS HAVE BEEN CREATED BY A USER THA...   \n26                TASK 3  CONSIDERING ONLY THE USERS WITH AN 'ABOUTME,' ...   \n27                TASK 3  NOT TAKING INTO ACCOUNT THE COMMENTCOUNT FIELD...   \n28                TASK 3  WHAT ARE THE TOP 10 POSTS WHICH HAVE THE HIGHE...   \n29                TASK 3     WHO ARE THE TOP 10 USERS WHO COMMENT THE MOST?   \n30                TASK 3        WHO ARE THE TOP 10 USERS WHO POST THE MOST?   \n\n                                                query  \n0   \\nSELECT ID, SUM(VIEWS) AS TOTALVIEWS\\n    FRO...  \n1   \\n    CREATE TABLE \"COMMENTS\" (\\n    \"ID\" INTE...  \n2   \\n    CREATE TABLE \"POSTS\" (\\n    \"ID\" INTEGER...  \n3   \\n    CREATE TABLE \"USERS\" (\\n    \"ID\" INTEGER...  \n4               \\n    SELECT COUNT(*) FROM COMMENTS\\n  \n5                  \\n    SELECT COUNT(*) FROM USERS\\n  \n6                  \\n    SELECT COUNT(*) FROM POSTS\\n  \n7   \\n    SELECT * FROM POSTS ORDER BY RANDOM() LI...  \n8   \\n    SELECT * FROM COMMENTS ORDER BY RANDOM()...  \n9   \\n    SELECT * FROM USERS ORDER BY RANDOM() LI...  \n10  \\nSELECT COUNT(*)\\nFROM POSTS\\nWHERE COMMENTCO...  \n11  \\nSELECT COUNT(*)\\nFROM POSTS\\nWHERE COMMENTCO...  \n12  \\nSELECT COUNT(*)\\nFROM POSTS\\nWHERE COMMENTCO...  \n13  \\nSELECT *\\nFROM POSTS\\nORDER BY VIEWCOUNT DES...  \n14  \\nSELECT \\n    *\\nFROM \\n    POSTS\\nORDER BY \\...  \n15  \\nSELECT \\n    SCORE, COUNT(*) AS FREQUENCY\\nF...  \n16  \\nSELECT \\n    COUNT(*) AS NUMPOSTSWITHDATATAG...  \n17  \\nSELECT \\n    COMMENTCOUNT, COUNT(*) AS FREQU...  \n18  \\nSELECT \\n    COUNT(*)\\nFROM \\n    POSTS\\nWHE...  \n19  \\nSELECT \\n    AVG(REPUTATION) AS AVERAGEREPUT...  \n20  \\nSELECT \\n    MIN(REPUTATION) AS MINREPUTATIO...  \n21  \\nSELECT \\n    ID,\\n    LENGTH(BODY) AS BODYLE...  \n22  \\nSELECT \\n    COUNT(DISTINCT LOCATION) AS NUM...  \n23  \\nSELECT \\n    LOCATION,\\n    COUNT(*) AS USER...  \n24  \\nWITH DAYOFWEEKVIEWCOUNTS AS (\\n    SELECT \\n...  \n25  \\nSELECT COUNT(*) AS NUMPOSTS\\nFROM POSTS P\\nJ...  \n26  \\nSELECT \\n    COUNT(*) / (\\n        SELECT CA...  \n27  \\nSELECT\\n    P.ID AS POSTID,\\n    P.SCORE AS ...  \n28  \\nSELECT\\n    P.ID AS POSTID,\\n    P.SCORE AS ...  \n29  \\nSELECT\\n    U.ID AS USERID,\\n    COUNT(*) AS...  \n30  \\nSELECT\\n    U.ID AS OWNERUSERID,\\n    COUNT(...  ",
      "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>task</th>\n      <th>action</th>\n      <th>query</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>SINGLE TABLE QUERIES</td>\n      <td>WHICH 5 USERS HAVE VIEWED THE MOST TIMES AND W...</td>\n      <td>\\nSELECT ID, SUM(VIEWS) AS TOTALVIEWS\\n    FRO...</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>TASK 1</td>\n      <td>GET THE CREATE TABLE STATEMENT FOR COMMENTS</td>\n      <td>\\n    CREATE TABLE \"COMMENTS\" (\\n    \"ID\" INTE...</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>TASK 1</td>\n      <td>GET THE CREATE TABLE STATEMENT FOR POSTS</td>\n      <td>\\n    CREATE TABLE \"POSTS\" (\\n    \"ID\" INTEGER...</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>TASK 1</td>\n      <td>GET THE CREATE TABLE STATEMENT FOR USERS</td>\n      <td>\\n    CREATE TABLE \"USERS\" (\\n    \"ID\" INTEGER...</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>TASK 1</td>\n      <td>COUNT NUMBER OF ROWS IN TABLE COMMENTS</td>\n      <td>\\n    SELECT COUNT(*) FROM COMMENTS\\n</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>TASK 1</td>\n      <td>COUNT NUMBER OF ROWS IN TABLE USERS</td>\n      <td>\\n    SELECT COUNT(*) FROM USERS\\n</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>TASK 1</td>\n      <td>COUNT NUMBER OF ROWS IN TABLE POSTS</td>\n      <td>\\n    SELECT COUNT(*) FROM POSTS\\n</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>TASK 1</td>\n      <td>FIVE RANDOM POSTS</td>\n      <td>\\n    SELECT * FROM POSTS ORDER BY RANDOM() LI...</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>TASK 1</td>\n      <td>FIVE RANDOM COMMENTS</td>\n      <td>\\n    SELECT * FROM COMMENTS ORDER BY RANDOM()...</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>TASK 1</td>\n      <td>FIVE RANDOM USERS</td>\n      <td>\\n    SELECT * FROM USERS ORDER BY RANDOM() LI...</td>\n    </tr>\n    <tr>\n      <th>10</th>\n      <td>TASK 2</td>\n      <td>HOW MANY POSTS HAVE 0 COMMENTS?</td>\n      <td>\\nSELECT COUNT(*)\\nFROM POSTS\\nWHERE COMMENTCO...</td>\n    </tr>\n    <tr>\n      <th>11</th>\n      <td>TASK 2</td>\n      <td>HOW MANY POSTS HAVE 1 COMMENTS?</td>\n      <td>\\nSELECT COUNT(*)\\nFROM POSTS\\nWHERE COMMENTCO...</td>\n    </tr>\n    <tr>\n      <th>12</th>\n      <td>TASK 2</td>\n      <td>HOW MANY POSTS HAVE 2 COMMENTS OR MORE?</td>\n      <td>\\nSELECT COUNT(*)\\nFROM POSTS\\nWHERE COMMENTCO...</td>\n    </tr>\n    <tr>\n      <th>13</th>\n      <td>TASK 2</td>\n      <td>FIND THE 5 POSTS WITH THE HIGHEST VIEWCOUNT</td>\n      <td>\\nSELECT *\\nFROM POSTS\\nORDER BY VIEWCOUNT DES...</td>\n    </tr>\n    <tr>\n      <th>14</th>\n      <td>TASK 2</td>\n      <td>FIND THE TOP 5 POSTS WITH THE HIGHEST SCORES</td>\n      <td>\\nSELECT \\n    *\\nFROM \\n    POSTS\\nORDER BY \\...</td>\n    </tr>\n    <tr>\n      <th>15</th>\n      <td>TASK 2</td>\n      <td>WHAT ARE THE 5 MOST FREQUENT SCORES ON POSTS?</td>\n      <td>\\nSELECT \\n    SCORE, COUNT(*) AS FREQUENCY\\nF...</td>\n    </tr>\n    <tr>\n      <th>16</th>\n      <td>TASK 2</td>\n      <td>HOW MANY POSTS HAVE THE KEYWORD 'DATA' IN THEI...</td>\n      <td>\\nSELECT \\n    COUNT(*) AS NUMPOSTSWITHDATATAG...</td>\n    </tr>\n    <tr>\n      <th>17</th>\n      <td>TASK 2</td>\n      <td>WHAT ARE THE 5 MOST FREQUENT COMMENTCOUNT FOR ...</td>\n      <td>\\nSELECT \\n    COMMENTCOUNT, COUNT(*) AS FREQU...</td>\n    </tr>\n    <tr>\n      <th>18</th>\n      <td>TASK 2</td>\n      <td>HOW MANY POSTS HAVE AN ACCEPTED ANSWER?</td>\n      <td>\\nSELECT \\n    COUNT(*)\\nFROM \\n    POSTS\\nWHE...</td>\n    </tr>\n    <tr>\n      <th>19</th>\n      <td>TASK 2</td>\n      <td>WHAT IS THE AVERAGE REPUTATION OF TABLE USERS?</td>\n      <td>\\nSELECT \\n    AVG(REPUTATION) AS AVERAGEREPUT...</td>\n    </tr>\n    <tr>\n      <th>20</th>\n      <td>TASK 2</td>\n      <td>WHAT ARE THE MIN AND MAX REPUTATION OF USERS?</td>\n      <td>\\nSELECT \\n    MIN(REPUTATION) AS MINREPUTATIO...</td>\n    </tr>\n    <tr>\n      <th>21</th>\n      <td>TASK 2</td>\n      <td>WHAT IS THE LENGTH OF THE BODY OF 5 MOST VIEWE...</td>\n      <td>\\nSELECT \\n    ID,\\n    LENGTH(BODY) AS BODYLE...</td>\n    </tr>\n    <tr>\n      <th>22</th>\n      <td>TASK 2</td>\n      <td>HOW MANY DIFFERENT LOCATIONS ARE THERE IN THE ...</td>\n      <td>\\nSELECT \\n    COUNT(DISTINCT LOCATION) AS NUM...</td>\n    </tr>\n    <tr>\n      <th>23</th>\n      <td>TASK 2</td>\n      <td>WHAT ARE THE TOP 5 LOCATIONS OF USERS?</td>\n      <td>\\nSELECT \\n    LOCATION,\\n    COUNT(*) AS USER...</td>\n    </tr>\n    <tr>\n      <th>24</th>\n      <td>TASK 2</td>\n      <td>RANK THE DAYS OF THE WEEK FROM HIGHEST TO LOWE...</td>\n      <td>\\nWITH DAYOFWEEKVIEWCOUNTS AS (\\n    SELECT \\n...</td>\n    </tr>\n    <tr>\n      <th>25</th>\n      <td>TASK 3</td>\n      <td>HOW MANY POSTS HAVE BEEN CREATED BY A USER THA...</td>\n      <td>\\nSELECT COUNT(*) AS NUMPOSTS\\nFROM POSTS P\\nJ...</td>\n    </tr>\n    <tr>\n      <th>26</th>\n      <td>TASK 3</td>\n      <td>CONSIDERING ONLY THE USERS WITH AN 'ABOUTME,' ...</td>\n      <td>\\nSELECT \\n    COUNT(*) / (\\n        SELECT CA...</td>\n    </tr>\n    <tr>\n      <th>27</th>\n      <td>TASK 3</td>\n      <td>NOT TAKING INTO ACCOUNT THE COMMENTCOUNT FIELD...</td>\n      <td>\\nSELECT\\n    P.ID AS POSTID,\\n    P.SCORE AS ...</td>\n    </tr>\n    <tr>\n      <th>28</th>\n      <td>TASK 3</td>\n      <td>WHAT ARE THE TOP 10 POSTS WHICH HAVE THE HIGHE...</td>\n      <td>\\nSELECT\\n    P.ID AS POSTID,\\n    P.SCORE AS ...</td>\n    </tr>\n    <tr>\n      <th>29</th>\n      <td>TASK 3</td>\n      <td>WHO ARE THE TOP 10 USERS WHO COMMENT THE MOST?</td>\n      <td>\\nSELECT\\n    U.ID AS USERID,\\n    COUNT(*) AS...</td>\n    </tr>\n    <tr>\n      <th>30</th>\n      <td>TASK 3</td>\n      <td>WHO ARE THE TOP 10 USERS WHO POST THE MOST?</td>\n      <td>\\nSELECT\\n    U.ID AS OWNERUSERID,\\n    COUNT(...</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for col in queries.columns:\n",
    "    queries[col] = queries[col].str.upper()\n",
    "    \n",
    "queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:17.078788900Z",
     "start_time": "2024-01-14T16:35:16.857704800Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "31"
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write the now deduped uppercase dataframe back to sqlite and replace the table\n",
    "queries.to_sql('queries', con, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Use Case\n",
    "\n",
    "Now that we have this queries table, lets give you some ideas about how you would use it.\n",
    "\n",
    "Suppose you wanted to find all of the queries where you did a GROUP BY:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:17.172203400Z",
     "start_time": "2024-01-14T16:35:16.892263100Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('\\nSELECT ID, SUM(VIEWS) AS TOTALVIEWS\\n    FROM USERS\\n        GROUP BY ID\\n            ORDER BY TOTALVIEWS DESC\\n                LIMIT 5\\n                ',),\n ('\\nSELECT \\n    SCORE, COUNT(*) AS FREQUENCY\\nFROM \\n    POSTS\\nGROUP BY \\n    SCORE  \\nORDER BY \\n    FREQUENCY DESC, SCORE\\n    LIMIT 5\\n        ',),\n ('\\nSELECT \\n    COMMENTCOUNT, COUNT(*) AS FREQUENCY\\nFROM \\n    POSTS\\nGROUP BY \\n    COMMENTCOUNT  \\nORDER BY \\n    FREQUENCY DESC, COMMENTCOUNT\\n    LIMIT 5\\n        ',),\n (\"\\nSELECT \\n    LOCATION,\\n    COUNT(*) AS USERCOUNT\\nFROM \\n    USERS\\nWHERE LOCATION IS NOT NULL AND LOCATION != ''\\nGROUP BY \\n    LOCATION\\nORDER BY \\n    USERCOUNT DESC\\nLIMIT 5\\n\",),\n (\"\\nWITH DAYOFWEEKVIEWCOUNTS AS (\\n    SELECT \\n        CASE\\n            WHEN STRFTIME('%W', CREATIONDATE) = '0' THEN 'SUNDAY'\\n            WHEN STRFT ... (557 characters truncated) ... IEWCOUNT,\\n    (TOTALVIEWCOUNT * 100.0 / SUM(TOTALVIEWCOUNT) OVER ()) AS PERCENTAGE\\nFROM \\n    DAYOFWEEKVIEWCOUNTS\\nORDER BY \\n    PERCENTAGE DESC\\n\",),\n ('\\nSELECT\\n    P.ID AS POSTID,\\n    P.SCORE AS POSTSCORE,\\n    COUNT(*) AS CCOUNT\\nFROM\\n    POSTS P\\nLEFT JOIN\\n    COMMENTS C ON P.ID = C.POSTID\\nGROUP BY\\n    P.ID\\nORDER BY\\n    CCOUNT DESC\\nLIMIT 10;\\n',),\n ('\\nSELECT\\n    P.ID AS POSTID,\\n    P.SCORE AS POSTSCORE,\\n    SUM(C.SCORE) AS COMMENTSCORE,\\n    SUM(P.SCORE) + SUM(C.SCORE) AS CUMULATIVESCORE,\\n    ... (22 characters truncated) ... TCOUNT\\nFROM\\n    POSTS P\\nLEFT JOIN\\n    COMMENTS C ON P.ID = C.POSTID\\nGROUP BY\\n    P.ID, P.SCORE\\nORDER BY\\n    CUMULATIVESCORE DESC\\nLIMIT 10;\\n',),\n ('\\nSELECT\\n    U.ID AS USERID,\\n    COUNT(*) AS FREQUENCY\\nFROM\\n    COMMENTS C\\nJOIN\\n    USERS U ON C.USERID = U.ID\\nGROUP BY\\n    U.ID\\nORDER BY\\n    FREQUENCY DESC\\nLIMIT 10;\\n',),\n ('\\nSELECT\\n    U.ID AS OWNERUSERID,\\n    COUNT(*) AS FREQUENCY\\nFROM\\n    POSTS P\\nJOIN\\n    USERS U ON P.OWNERUSERID = U.ID\\nGROUP BY\\n    U.ID\\nORDER BY\\n    FREQUENCY DESC\\nLIMIT 10;\\n',)]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td><br>SELECT ID, SUM(VIEWS) AS TOTALVIEWS<br>    FROM USERS<br>        GROUP BY ID<br>            ORDER BY TOTALVIEWS DESC<br>                LIMIT 5<br>                </td>\n        </tr>\n        <tr>\n            <td><br>SELECT <br>    SCORE, COUNT(*) AS FREQUENCY<br>FROM <br>    POSTS<br>GROUP BY <br>    SCORE  <br>ORDER BY <br>    FREQUENCY DESC, SCORE<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td><br>SELECT <br>    COMMENTCOUNT, COUNT(*) AS FREQUENCY<br>FROM <br>    POSTS<br>GROUP BY <br>    COMMENTCOUNT  <br>ORDER BY <br>    FREQUENCY DESC, COMMENTCOUNT<br>    LIMIT 5<br>        </td>\n        </tr>\n        <tr>\n            <td><br>SELECT <br>    LOCATION,<br>    COUNT(*) AS USERCOUNT<br>FROM <br>    USERS<br>WHERE LOCATION IS NOT NULL AND LOCATION != &#x27;&#x27;<br>GROUP BY <br>    LOCATION<br>ORDER BY <br>    USERCOUNT DESC<br>LIMIT 5<br></td>\n        </tr>\n        <tr>\n            <td><br>WITH DAYOFWEEKVIEWCOUNTS AS (<br>    SELECT <br>        CASE<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;0&#x27; THEN &#x27;SUNDAY&#x27;<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;1&#x27; THEN &#x27;MONDAY&#x27;<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;2&#x27; THEN &#x27;TUESDAY&#x27;<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;3&#x27; THEN &#x27;WEDNESDAY&#x27;<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;4&#x27; THEN &#x27;THURSDAY&#x27;<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;5&#x27; THEN &#x27;FRIDAY&#x27;<br>            WHEN STRFTIME(&#x27;%W&#x27;, CREATIONDATE) = &#x27;6&#x27; THEN &#x27;SATURDAY&#x27;<br>        END AS DAYOFWEEK,<br>        SUM(VIEWCOUNT) AS TOTALVIEWCOUNT<br>    FROM <br>        POSTS<br>    GROUP BY <br>        DAYOFWEEK<br>)<br><br>SELECT <br>    DAYOFWEEK,<br>    TOTALVIEWCOUNT,<br>    (TOTALVIEWCOUNT * 100.0 / SUM(TOTALVIEWCOUNT) OVER ()) AS PERCENTAGE<br>FROM <br>    DAYOFWEEKVIEWCOUNTS<br>ORDER BY <br>    PERCENTAGE DESC<br></td>\n        </tr>\n        <tr>\n            <td><br>SELECT<br>    P.ID AS POSTID,<br>    P.SCORE AS POSTSCORE,<br>    COUNT(*) AS CCOUNT<br>FROM<br>    POSTS P<br>LEFT JOIN<br>    COMMENTS C ON P.ID = C.POSTID<br>GROUP BY<br>    P.ID<br>ORDER BY<br>    CCOUNT DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td><br>SELECT<br>    P.ID AS POSTID,<br>    P.SCORE AS POSTSCORE,<br>    SUM(C.SCORE) AS COMMENTSCORE,<br>    SUM(P.SCORE) + SUM(C.SCORE) AS CUMULATIVESCORE,<br>    COUNT(C.ID) AS COMMENTCOUNT<br>FROM<br>    POSTS P<br>LEFT JOIN<br>    COMMENTS C ON P.ID = C.POSTID<br>GROUP BY<br>    P.ID, P.SCORE<br>ORDER BY<br>    CUMULATIVESCORE DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td><br>SELECT<br>    U.ID AS USERID,<br>    COUNT(*) AS FREQUENCY<br>FROM<br>    COMMENTS C<br>JOIN<br>    USERS U ON C.USERID = U.ID<br>GROUP BY<br>    U.ID<br>ORDER BY<br>    FREQUENCY DESC<br>LIMIT 10;<br></td>\n        </tr>\n        <tr>\n            <td><br>SELECT<br>    U.ID AS OWNERUSERID,<br>    COUNT(*) AS FREQUENCY<br>FROM<br>    POSTS P<br>JOIN<br>    USERS U ON P.OWNERUSERID = U.ID<br>GROUP BY<br>    U.ID<br>ORDER BY<br>    FREQUENCY DESC<br>LIMIT 10;<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT query\n",
    "    FROM queries\n",
    "        WHERE query LIKE '%GROUP BY%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Now Your Turn\n",
    "Find the queries that have 'DISTINCT' in them. You can do it with the %sql command or with Pandas and sql.\n",
    "\n",
    "<div style=\"background-color:lemonchiffon; border:1px solid; padding:5px; margin:5px; font-weight:bold\"><font color=\"crimson\">\n",
    "TODO: Complete the following code cell\n",
    "    </font></div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:17.190870600Z",
     "start_time": "2024-01-14T16:35:16.905742600Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///chatdata.db\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": "[('\\nSELECT \\n    COUNT(DISTINCT LOCATION) AS NUMDISTINCTLOCATIONS\\nFROM \\n    USERS\\n',)]",
      "text/html": "<table>\n    <thead>\n        <tr>\n            <th>query</th>\n        </tr>\n    </thead>\n    <tbody>\n        <tr>\n            <td><br>SELECT <br>    COUNT(DISTINCT LOCATION) AS NUMDISTINCTLOCATIONS<br>FROM <br>    USERS<br></td>\n        </tr>\n    </tbody>\n</table>"
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT query\n",
    "    FROM queries\n",
    "        WHERE query LIKE '%DISTINCT%'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Close SQLite\n",
    "\n",
    "It is good practise to close all relational databases as soon as you are finished updating them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-01-14T16:35:17.190870600Z",
     "start_time": "2024-01-14T16:35:16.922311600Z"
    }
   },
   "outputs": [],
   "source": [
    "# con.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# All Done!\n",
    "\n",
    "Great job. You now have a good idea for how to use sql and pandas with sql. You can create your own databases from csv files and you can do extensive querying using sql. These are valuable skills that will take you a long ways in todays technological world."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "293.993px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}