exercise-joins-and-unions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3abd8402",
   "metadata": {
    "papermill": {
     "duration": 0.006986,
     "end_time": "2023-04-05T14:43:15.759938",
     "exception": false,
     "start_time": "2023-04-05T14:43:15.752952",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "**This notebook is an exercise in the [Advanced SQL](https://www.kaggle.com/learn/advanced-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/alexisbcook/joins-and-unions).**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5644d942",
   "metadata": {
    "papermill": {
     "duration": 0.005265,
     "end_time": "2023-04-05T14:43:15.770988",
     "exception": false,
     "start_time": "2023-04-05T14:43:15.765723",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Introduction\n",
    "\n",
    "Here, you'll use different types of SQL **JOINs** to answer questions about the [Stack Overflow](https://www.kaggle.com/stackoverflow/stackoverflow) dataset.\n",
    "\n",
    "Before you get started, run the following cell to set everything up."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6b60bb11",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:43:15.784326Z",
     "iopub.status.busy": "2023-04-05T14:43:15.783912Z",
     "iopub.status.idle": "2023-04-05T14:43:38.741667Z",
     "shell.execute_reply": "2023-04-05T14:43:38.740488Z"
    },
    "papermill": {
     "duration": 22.967651,
     "end_time": "2023-04-05T14:43:38.744252",
     "exception": false,
     "start_time": "2023-04-05T14:43:15.776601",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Setup Complete\n"
     ]
    }
   ],
   "source": [
    "# Set up feedback system\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.sql_advanced.ex1 import *\n",
    "print(\"Setup Complete\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1fce1aa5",
   "metadata": {
    "papermill": {
     "duration": 0.005471,
     "end_time": "2023-04-05T14:43:38.755791",
     "exception": false,
     "start_time": "2023-04-05T14:43:38.750320",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "The code cell below fetches the `posts_questions` table from the `stackoverflow` dataset.  We also preview the first five rows of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "17c374ff",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:43:38.770356Z",
     "iopub.status.busy": "2023-04-05T14:43:38.768971Z",
     "iopub.status.idle": "2023-04-05T14:43:40.338946Z",
     "shell.execute_reply": "2023-04-05T14:43:40.337585Z"
    },
    "papermill": {
     "duration": 1.580304,
     "end_time": "2023-04-05T14:43:40.341824",
     "exception": false,
     "start_time": "2023-04-05T14:43:38.761520",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:19: UserWarning: Cannot use bqstorage_client if max_results is set, reverting to fetching data with the tabledata.list endpoint.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>accepted_answer_id</th>\n",
       "      <th>answer_count</th>\n",
       "      <th>comment_count</th>\n",
       "      <th>community_owned_date</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>last_activity_date</th>\n",
       "      <th>last_edit_date</th>\n",
       "      <th>last_editor_display_name</th>\n",
       "      <th>last_editor_user_id</th>\n",
       "      <th>owner_display_name</th>\n",
       "      <th>owner_user_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>post_type_id</th>\n",
       "      <th>score</th>\n",
       "      <th>tags</th>\n",
       "      <th>view_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>320268</td>\n",
       "      <td>Html.ActionLink doesn’t render # properly</td>\n",
       "      <td>&lt;p&gt;When using Html.ActionLink passing a string...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-11-26 10:42:37.477000+00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-02-06 20:13:54.370000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Paulo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>asp.net-mvc</td>\n",
       "      <td>390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>324003</td>\n",
       "      <td>Primitive recursion</td>\n",
       "      <td>&lt;p&gt;how  will i define the function 'simplify' ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-11-27 15:12:37.497000+00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-09-25 19:54:40.597000+00:00</td>\n",
       "      <td>2012-09-25 19:54:40.597000+00:00</td>\n",
       "      <td>Marcin</td>\n",
       "      <td>1288.0</td>\n",
       "      <td>None</td>\n",
       "      <td>41000.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>haskell|lambda|functional-programming|lambda-c...</td>\n",
       "      <td>497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>390605</td>\n",
       "      <td>While vs. Do While</td>\n",
       "      <td>&lt;p&gt;I've seen both the blocks of code in use se...</td>\n",
       "      <td>390608.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-12-24 01:49:54.230000+00:00</td>\n",
       "      <td>2</td>\n",
       "      <td>2008-12-24 03:08:55.897000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Unkwntech</td>\n",
       "      <td>115.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>language-agnostic|loops</td>\n",
       "      <td>11262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>413246</td>\n",
       "      <td>Protect ASP.NET Source code</td>\n",
       "      <td>&lt;p&gt;Im currently doing some research in how to ...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2009-01-05 14:23:51.040000+00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-03-24 21:30:22.370000+00:00</td>\n",
       "      <td>2009-01-05 14:42:28.257000+00:00</td>\n",
       "      <td>Tom Anderson</td>\n",
       "      <td>13502.0</td>\n",
       "      <td>Velnias</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>asp.net|deployment|obfuscation</td>\n",
       "      <td>4823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>454921</td>\n",
       "      <td>Difference between \"int[] myArray\" and \"int my...</td>\n",
       "      <td>&lt;blockquote&gt;\\n  &lt;p&gt;&lt;strong&gt;Possible Duplicate:...</td>\n",
       "      <td>454928.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2009-01-18 10:22:52.177000+00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>2009-01-18 10:30:50.930000+00:00</td>\n",
       "      <td>2017-05-23 11:49:26.567000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>Evan Fosmark</td>\n",
       "      <td>49701.0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>java|arrays</td>\n",
       "      <td>798</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       id                                              title  \\\n",
       "0  320268          Html.ActionLink doesn’t render # properly   \n",
       "1  324003                                Primitive recursion   \n",
       "2  390605                                 While vs. Do While   \n",
       "3  413246                        Protect ASP.NET Source code   \n",
       "4  454921  Difference between \"int[] myArray\" and \"int my...   \n",
       "\n",
       "                                                body  accepted_answer_id  \\\n",
       "0  <p>When using Html.ActionLink passing a string...                 NaN   \n",
       "1  <p>how  will i define the function 'simplify' ...                 NaN   \n",
       "2  <p>I've seen both the blocks of code in use se...            390608.0   \n",
       "3  <p>Im currently doing some research in how to ...                 NaN   \n",
       "4  <blockquote>\\n  <p><strong>Possible Duplicate:...            454928.0   \n",
       "\n",
       "   answer_count  comment_count community_owned_date  \\\n",
       "0             0              0                  NaT   \n",
       "1             0              0                  NaT   \n",
       "2             0              0                  NaT   \n",
       "3             0              0                  NaT   \n",
       "4             0              0                  NaT   \n",
       "\n",
       "                     creation_date  favorite_count  \\\n",
       "0 2008-11-26 10:42:37.477000+00:00               0   \n",
       "1 2008-11-27 15:12:37.497000+00:00               0   \n",
       "2 2008-12-24 01:49:54.230000+00:00               2   \n",
       "3 2009-01-05 14:23:51.040000+00:00               0   \n",
       "4 2009-01-18 10:22:52.177000+00:00               0   \n",
       "\n",
       "                last_activity_date                   last_edit_date  \\\n",
       "0 2009-02-06 20:13:54.370000+00:00                              NaT   \n",
       "1 2012-09-25 19:54:40.597000+00:00 2012-09-25 19:54:40.597000+00:00   \n",
       "2 2008-12-24 03:08:55.897000+00:00                              NaT   \n",
       "3 2009-03-24 21:30:22.370000+00:00 2009-01-05 14:42:28.257000+00:00   \n",
       "4 2009-01-18 10:30:50.930000+00:00 2017-05-23 11:49:26.567000+00:00   \n",
       "\n",
       "  last_editor_display_name  last_editor_user_id owner_display_name  \\\n",
       "0                     None                  NaN              Paulo   \n",
       "1                   Marcin               1288.0               None   \n",
       "2                     None                  NaN          Unkwntech   \n",
       "3             Tom Anderson              13502.0            Velnias   \n",
       "4                     None                 -1.0       Evan Fosmark   \n",
       "\n",
       "   owner_user_id parent_id  post_type_id  score  \\\n",
       "0            NaN      None             1      0   \n",
       "1        41000.0      None             1      0   \n",
       "2          115.0      None             1      0   \n",
       "3            NaN      None             1      0   \n",
       "4        49701.0      None             1      0   \n",
       "\n",
       "                                                tags  view_count  \n",
       "0                                        asp.net-mvc         390  \n",
       "1  haskell|lambda|functional-programming|lambda-c...         497  \n",
       "2                            language-agnostic|loops       11262  \n",
       "3                     asp.net|deployment|obfuscation        4823  \n",
       "4                                        java|arrays         798  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"stackoverflow\" dataset\n",
    "dataset_ref = client.dataset(\"stackoverflow\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)\n",
    "\n",
    "# Construct a reference to the \"posts_questions\" table\n",
    "table_ref = dataset_ref.table(\"posts_questions\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b671bc32",
   "metadata": {
    "papermill": {
     "duration": 0.005983,
     "end_time": "2023-04-05T14:43:40.354325",
     "exception": false,
     "start_time": "2023-04-05T14:43:40.348342",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "We also take a look at the `posts_answers` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f8ea6948",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:43:40.369379Z",
     "iopub.status.busy": "2023-04-05T14:43:40.368613Z",
     "iopub.status.idle": "2023-04-05T14:43:41.347970Z",
     "shell.execute_reply": "2023-04-05T14:43:41.346741Z"
    },
    "papermill": {
     "duration": 0.989847,
     "end_time": "2023-04-05T14:43:41.350492",
     "exception": false,
     "start_time": "2023-04-05T14:43:40.360645",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:8: UserWarning: Cannot use bqstorage_client if max_results is set, reverting to fetching data with the tabledata.list endpoint.\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>body</th>\n",
       "      <th>accepted_answer_id</th>\n",
       "      <th>answer_count</th>\n",
       "      <th>comment_count</th>\n",
       "      <th>community_owned_date</th>\n",
       "      <th>creation_date</th>\n",
       "      <th>favorite_count</th>\n",
       "      <th>last_activity_date</th>\n",
       "      <th>last_edit_date</th>\n",
       "      <th>last_editor_display_name</th>\n",
       "      <th>last_editor_user_id</th>\n",
       "      <th>owner_display_name</th>\n",
       "      <th>owner_user_id</th>\n",
       "      <th>parent_id</th>\n",
       "      <th>post_type_id</th>\n",
       "      <th>score</th>\n",
       "      <th>tags</th>\n",
       "      <th>view_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>18</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;For a table like this:&lt;/p&gt;\\n\\n&lt;pre&gt;&lt;code&gt;CR...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-08-01 05:12:44.193000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-06-02 05:56:26.060000+00:00</td>\n",
       "      <td>2016-06-02 05:56:26.060000+00:00</td>\n",
       "      <td>Jeff Atwood</td>\n",
       "      <td>126039</td>\n",
       "      <td>phpguy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17</td>\n",
       "      <td>2</td>\n",
       "      <td>59</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>165</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;You can use a &lt;a href=\"http://sharpdevelop....</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-08-01 18:04:25.023000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-04-06 14:03:51.080000+00:00</td>\n",
       "      <td>2019-04-06 14:03:51.080000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>1721793</td>\n",
       "      <td>user2189331</td>\n",
       "      <td>NaN</td>\n",
       "      <td>145</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1028</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;The VB code looks something like this:&lt;/p&gt;\\...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-08-04 04:58:40.300000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-02-07 13:22:14.680000+00:00</td>\n",
       "      <td>2013-02-07 13:22:14.680000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>395659</td>\n",
       "      <td>user2189331</td>\n",
       "      <td>NaN</td>\n",
       "      <td>947</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1073</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;My first choice would be a dedicated heap t...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-08-04 07:51:02.997000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2015-09-01 17:32:32.120000+00:00</td>\n",
       "      <td>2015-09-01 17:32:32.120000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>45459</td>\n",
       "      <td>user2189331</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1069</td>\n",
       "      <td>2</td>\n",
       "      <td>29</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1260</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;I found the answer. all you have to do is a...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2008-08-04 14:06:02.863000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-12-20 08:38:48.867000+00:00</td>\n",
       "      <td>2016-12-20 08:38:48.867000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>1221571</td>\n",
       "      <td>Jin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1229</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id title                                               body  \\\n",
       "0    18  None  <p>For a table like this:</p>\\n\\n<pre><code>CR...   \n",
       "1   165  None  <p>You can use a <a href=\"http://sharpdevelop....   \n",
       "2  1028  None  <p>The VB code looks something like this:</p>\\...   \n",
       "3  1073  None  <p>My first choice would be a dedicated heap t...   \n",
       "4  1260  None  <p>I found the answer. all you have to do is a...   \n",
       "\n",
       "  accepted_answer_id answer_count  comment_count community_owned_date  \\\n",
       "0               None         None              2                  NaT   \n",
       "1               None         None              0                  NaT   \n",
       "2               None         None              0                  NaT   \n",
       "3               None         None              0                  NaT   \n",
       "4               None         None              0                  NaT   \n",
       "\n",
       "                     creation_date favorite_count  \\\n",
       "0 2008-08-01 05:12:44.193000+00:00           None   \n",
       "1 2008-08-01 18:04:25.023000+00:00           None   \n",
       "2 2008-08-04 04:58:40.300000+00:00           None   \n",
       "3 2008-08-04 07:51:02.997000+00:00           None   \n",
       "4 2008-08-04 14:06:02.863000+00:00           None   \n",
       "\n",
       "                last_activity_date                   last_edit_date  \\\n",
       "0 2016-06-02 05:56:26.060000+00:00 2016-06-02 05:56:26.060000+00:00   \n",
       "1 2019-04-06 14:03:51.080000+00:00 2019-04-06 14:03:51.080000+00:00   \n",
       "2 2013-02-07 13:22:14.680000+00:00 2013-02-07 13:22:14.680000+00:00   \n",
       "3 2015-09-01 17:32:32.120000+00:00 2015-09-01 17:32:32.120000+00:00   \n",
       "4 2016-12-20 08:38:48.867000+00:00 2016-12-20 08:38:48.867000+00:00   \n",
       "\n",
       "  last_editor_display_name  last_editor_user_id owner_display_name  \\\n",
       "0              Jeff Atwood               126039             phpguy   \n",
       "1                     None              1721793        user2189331   \n",
       "2                     None               395659        user2189331   \n",
       "3                     None                45459        user2189331   \n",
       "4                     None              1221571                Jin   \n",
       "\n",
       "   owner_user_id  parent_id  post_type_id  score  tags view_count  \n",
       "0            NaN         17             2     59  None       None  \n",
       "1            NaN        145             2     10  None       None  \n",
       "2            NaN        947             2      8  None       None  \n",
       "3            NaN       1069             2     29  None       None  \n",
       "4            NaN       1229             2      1  None       None  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Construct a reference to the \"posts_answers\" table\n",
    "table_ref = dataset_ref.table(\"posts_answers\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c2c1ac0",
   "metadata": {
    "papermill": {
     "duration": 0.006657,
     "end_time": "2023-04-05T14:43:41.364178",
     "exception": false,
     "start_time": "2023-04-05T14:43:41.357521",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "You will work with both of these tables to answer the questions below.\n",
    "\n",
    "# Exercises\n",
    "\n",
    "### 1) How long does it take for questions to receive answers?\n",
    "\n",
    "You're interested in exploring the data to have a better understanding of how long it generally takes for questions to receive answers.  Armed with this knowledge, you plan to use this information to better design the order in which questions are presented to Stack Overflow users.\n",
    "\n",
    "With this goal in mind, you write the query below, which focuses on questions asked in January 2018.  It returns a table with two columns:\n",
    "- `q_id` - the ID of the question\n",
    "- `time_to_answer` - how long it took (in seconds) for the question to receive an answer\n",
    "\n",
    "Run the query below (without changes), and take a look at the output."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "dc8a1c24",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:43:41.380000Z",
     "iopub.status.busy": "2023-04-05T14:43:41.379079Z",
     "iopub.status.idle": "2023-04-05T14:43:47.989661Z",
     "shell.execute_reply": "2023-04-05T14:43:47.988592Z"
    },
    "papermill": {
     "duration": 6.621072,
     "end_time": "2023-04-05T14:43:47.992061",
     "exception": false,
     "start_time": "2023-04-05T14:43:41.370989",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of answered questions: 100.0%\n",
      "Number of questions: 134719\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>q_id</th>\n",
       "      <th>time_to_answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48382183</td>\n",
       "      <td>-132444692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48174391</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48375126</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48092100</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48102324</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       q_id  time_to_answer\n",
       "0  48382183      -132444692\n",
       "1  48174391               0\n",
       "2  48375126               0\n",
       "3  48092100               0\n",
       "4  48102324               0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_query = \"\"\"\n",
    "              SELECT q.id AS q_id,\n",
    "                  MIN(TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND)) as time_to_answer\n",
    "              FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
    "                  INNER JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
    "              ON q.id = a.parent_id\n",
    "              WHERE q.creation_date >= '2018-01-01' and q.creation_date < '2018-02-01'\n",
    "              GROUP BY q_id\n",
    "              ORDER BY time_to_answer\n",
    "              \"\"\"\n",
    "\n",
    "first_result = client.query(first_query).result().to_dataframe()\n",
    "print(\"Percentage of answered questions: %s%%\" % \\\n",
    "      (sum(first_result[\"time_to_answer\"].notnull()) / len(first_result) * 100))\n",
    "print(\"Number of questions:\", len(first_result))\n",
    "first_result.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec1da292",
   "metadata": {
    "papermill": {
     "duration": 0.006745,
     "end_time": "2023-04-05T14:43:48.005973",
     "exception": false,
     "start_time": "2023-04-05T14:43:47.999228",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "You're surprised at the results and strongly suspect that something is wrong with your query.  In particular,\n",
    "- According to the query, 100% of the questions from January 2018 received an answer.  But, you know that ~80% of the questions on the site usually receive an answer.\n",
    "- The total number of questions is surprisingly low.  You expected to see at least 150,000 questions represented in the table.\n",
    "\n",
    "Given these observations, you think that the type of **JOIN** you have chosen has inadvertently excluded unanswered questions.  Using the code cell below, can you figure out what type of **JOIN** to use to fix the problem so that the table includes unanswered questions?\n",
    "\n",
    "**Note**: You need only amend the type of **JOIN** (i.e., **INNER**, **LEFT**, **RIGHT**, or **FULL**) to answer the question successfully."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d297a00d",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:43:48.024587Z",
     "iopub.status.busy": "2023-04-05T14:43:48.022622Z",
     "iopub.status.idle": "2023-04-05T14:44:01.213343Z",
     "shell.execute_reply": "2023-04-05T14:44:01.211879Z"
    },
    "papermill": {
     "duration": 13.203202,
     "end_time": "2023-04-05T14:44:01.216142",
     "exception": false,
     "start_time": "2023-04-05T14:43:48.012940",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>q_id</th>\n",
       "      <th>time_to_answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48217554</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48362643</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48184774</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48187041</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48342999</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       q_id  time_to_answer\n",
       "0  48217554             NaN\n",
       "1  48362643             NaN\n",
       "2  48184774             NaN\n",
       "3  48187041             NaN\n",
       "4  48342999             NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"1_CorrectQuery\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of answered questions: 83.3368387192557%\n",
      "Number of questions: 161656\n"
     ]
    }
   ],
   "source": [
    "# Your code here\n",
    "correct_query = \"\"\"\n",
    "                SELECT q.id AS q_id,\n",
    "                    MIN(TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND)) as time_to_answer\n",
    "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
    "                    LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
    "                ON q.id = a.parent_id\n",
    "                WHERE q.creation_date >= '2018-01-01' and q.creation_date < '2018-02-01'\n",
    "                GROUP BY q_id\n",
    "                ORDER BY time_to_answer\n",
    "                \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()\n",
    "\n",
    "# Run the query, and return a pandas DataFrame\n",
    "correct_result = client.query(correct_query).result().to_dataframe()\n",
    "print(\"Percentage of answered questions: %s%%\" % \\\n",
    "      (sum(correct_result[\"time_to_answer\"].notnull()) / len(correct_result) * 100))\n",
    "print(\"Number of questions:\", len(correct_result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "fa4cc0f6",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:01.233251Z",
     "iopub.status.busy": "2023-04-05T14:44:01.232815Z",
     "iopub.status.idle": "2023-04-05T14:44:01.247570Z",
     "shell.execute_reply": "2023-04-05T14:44:01.246561Z"
    },
    "papermill": {
     "duration": 0.025946,
     "end_time": "2023-04-05T14:44:01.249790",
     "exception": false,
     "start_time": "2023-04-05T14:44:01.223844",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"1_CorrectQuery\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> Is the `posts_questions` table the **LEFT** or **RIGHT** table?  You want to keep all of the rows from this table, regardless of whether or not there is a match in the `posts_answers` table."
      ],
      "text/plain": [
       "Hint: Is the `posts_questions` table the **LEFT** or **RIGHT** table?  You want to keep all of the rows from this table, regardless of whether or not there is a match in the `posts_answers` table."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"1_CorrectQuery\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "\n",
       "correct_query = \"\"\"\n",
       "                SELECT q.id AS q_id,\n",
       "                    MIN(TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND)) as time_to_answer\n",
       "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                    LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                ON q.id = a.parent_id\n",
       "                WHERE q.creation_date >= '2018-01-01' and q.creation_date < '2018-02-01'\n",
       "                GROUP BY q_id\n",
       "                ORDER BY time_to_answer\n",
       "                \"\"\"\n",
       "\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "\n",
       "correct_query = \"\"\"\n",
       "                SELECT q.id AS q_id,\n",
       "                    MIN(TIMESTAMP_DIFF(a.creation_date, q.creation_date, SECOND)) as time_to_answer\n",
       "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                    LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                ON q.id = a.parent_id\n",
       "                WHERE q.creation_date >= '2018-01-01' and q.creation_date < '2018-02-01'\n",
       "                GROUP BY q_id\n",
       "                ORDER BY time_to_answer\n",
       "                \"\"\"\n",
       "\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "q_1.hint()\n",
    "q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9107171d",
   "metadata": {
    "papermill": {
     "duration": 0.007628,
     "end_time": "2023-04-05T14:44:01.265570",
     "exception": false,
     "start_time": "2023-04-05T14:44:01.257942",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### 2) Initial questions and answers, Part 1\n",
    "\n",
    "You're interested in understanding the initial experiences that users typically have with the Stack Overflow website.  Is it more common for users to first ask questions or provide answers?  After signing up, how long does it take for users to first interact with the website?  To explore this further, you draft the (partial) query in the code cell below.\n",
    "\n",
    "The query returns a table with three columns:\n",
    "- `owner_user_id` - the user ID\n",
    "- `q_creation_date` - the first time the user asked a question \n",
    "- `a_creation_date` - the first time the user contributed an answer \n",
    "\n",
    "You want to keep track of users who have asked questions, but have yet to provide answers.  And, your table should also include users who have answered questions, but have yet to pose their own questions.  \n",
    "\n",
    "With this in mind, please fill in the appropriate **JOIN** (i.e., **INNER**, **LEFT**, **RIGHT**, or **FULL**) to return the correct information.  \n",
    "\n",
    "**Note**: You need only fill in the appropriate **JOIN**.  All other parts of the query should be left as-is.  (You also don't need to write any additional code to run the query, since the `check()` method will take care of this for you.)\n",
    "\n",
    "To avoid returning too much data, we'll restrict our attention to questions and answers posed in January 2019.  We'll amend the timeframe in Part 2 of this question to be more realistic!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "78fdd437",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:01.283093Z",
     "iopub.status.busy": "2023-04-05T14:44:01.282677Z",
     "iopub.status.idle": "2023-04-05T14:44:04.349737Z",
     "shell.execute_reply": "2023-04-05T14:44:04.348253Z"
    },
    "papermill": {
     "duration": 3.079134,
     "end_time": "2023-04-05T14:44:04.352580",
     "exception": false,
     "start_time": "2023-04-05T14:44:01.273446",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>owner_user_id</th>\n",
       "      <th>q_creation_date</th>\n",
       "      <th>a_creation_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>802365</td>\n",
       "      <td>2019-01-22 08:53:29.047000+00:00</td>\n",
       "      <td>2019-01-08 11:35:38.670000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10946476</td>\n",
       "      <td>2019-01-22 22:35:42.887000+00:00</td>\n",
       "      <td>2019-01-23 00:25:41.097000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>265791</td>\n",
       "      <td>2019-01-25 00:25:11.883000+00:00</td>\n",
       "      <td>2019-01-30 15:54:41.510000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6748184</td>\n",
       "      <td>2019-01-29 08:04:20.633000+00:00</td>\n",
       "      <td>2019-01-29 08:04:20.633000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5705247</td>\n",
       "      <td>2019-01-05 07:33:26.467000+00:00</td>\n",
       "      <td>2019-01-06 08:30:18.300000+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   owner_user_id                  q_creation_date  \\\n",
       "0         802365 2019-01-22 08:53:29.047000+00:00   \n",
       "1       10946476 2019-01-22 22:35:42.887000+00:00   \n",
       "2         265791 2019-01-25 00:25:11.883000+00:00   \n",
       "3        6748184 2019-01-29 08:04:20.633000+00:00   \n",
       "4        5705247 2019-01-05 07:33:26.467000+00:00   \n",
       "\n",
       "                   a_creation_date  \n",
       "0 2019-01-08 11:35:38.670000+00:00  \n",
       "1 2019-01-23 00:25:41.097000+00:00  \n",
       "2 2019-01-30 15:54:41.510000+00:00  \n",
       "3 2019-01-29 08:04:20.633000+00:00  \n",
       "4 2019-01-06 08:30:18.300000+00:00  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"2_QA_One\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Your code here\n",
    "q_and_a_query = \"\"\"\n",
    "                SELECT q.owner_user_id AS owner_user_id,\n",
    "                    MIN(q.creation_date) AS q_creation_date,\n",
    "                    MIN(a.creation_date) AS a_creation_date\n",
    "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
    "                    FULL JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
    "                ON q.owner_user_id = a.owner_user_id \n",
    "                WHERE q.creation_date >= '2019-01-01' AND q.creation_date < '2019-02-01' \n",
    "                    AND a.creation_date >= '2019-01-01' AND a.creation_date < '2019-02-01'\n",
    "                GROUP BY owner_user_id\n",
    "                \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_2.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "3d5359eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:04.372326Z",
     "iopub.status.busy": "2023-04-05T14:44:04.371791Z",
     "iopub.status.idle": "2023-04-05T14:44:04.387787Z",
     "shell.execute_reply": "2023-04-05T14:44:04.386458Z"
    },
    "papermill": {
     "duration": 0.029275,
     "end_time": "2023-04-05T14:44:04.390533",
     "exception": false,
     "start_time": "2023-04-05T14:44:04.361258",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"2_QA_One\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> Which type of JOIN always returns all rows from both tables?"
      ],
      "text/plain": [
       "Hint: Which type of JOIN always returns all rows from both tables?"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"2_QA_One\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "\n",
       "q_and_a_query = \"\"\"\n",
       "                SELECT q.owner_user_id AS owner_user_id,\n",
       "                    MIN(q.creation_date) AS q_creation_date,\n",
       "                    MIN(a.creation_date) AS a_creation_date\n",
       "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                    FULL JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                ON q.owner_user_id = a.owner_user_id \n",
       "                WHERE q.creation_date >= '2019-01-01' AND q.creation_date < '2019-02-01' \n",
       "                    AND a.creation_date >= '2019-01-01' AND a.creation_date < '2019-02-01'\n",
       "                GROUP BY owner_user_id\n",
       "                \"\"\"\n",
       "\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "\n",
       "q_and_a_query = \"\"\"\n",
       "                SELECT q.owner_user_id AS owner_user_id,\n",
       "                    MIN(q.creation_date) AS q_creation_date,\n",
       "                    MIN(a.creation_date) AS a_creation_date\n",
       "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                    FULL JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                ON q.owner_user_id = a.owner_user_id \n",
       "                WHERE q.creation_date >= '2019-01-01' AND q.creation_date < '2019-02-01' \n",
       "                    AND a.creation_date >= '2019-01-01' AND a.creation_date < '2019-02-01'\n",
       "                GROUP BY owner_user_id\n",
       "                \"\"\"\n",
       "\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "q_2.hint()\n",
    "q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a725fb89",
   "metadata": {
    "papermill": {
     "duration": 0.0093,
     "end_time": "2023-04-05T14:44:04.409315",
     "exception": false,
     "start_time": "2023-04-05T14:44:04.400015",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### 3) Initial questions and answers, Part 2\n",
    "\n",
    "Now you'll address a more realistic (and complex!) scenario.  To answer this question, you'll need to pull information from *three* different tables!  This syntax very similar to the case when we have to join only two tables.  For instance, consider the three tables below.\n",
    "\n",
    "![three tables](https://i.imgur.com/OyhYtD1.png)\n",
    "\n",
    "We can use two different **JOINs** to link together information from all three tables, in a single query.\n",
    "\n",
    "![double join](https://i.imgur.com/G6buS7P.png)\n",
    "\n",
    "With this in mind, say you're interested in understanding users who joined the site in January 2019.  You want to track their activity on the site: when did they post their first questions and answers, if ever?\n",
    "\n",
    "Write a query that returns the following columns:\n",
    "- `id` - the IDs of all users who created Stack Overflow accounts in January 2019 (January 1, 2019, to January 31, 2019, inclusive)\n",
    "- `q_creation_date` - the first time the user posted a question on the site; if the user has never posted a question, the value should be null\n",
    "- `a_creation_date` - the first time the user posted a question on the site; if the user has never posted a question, the value should be null\n",
    "\n",
    "Note that questions and answers posted after January 31, 2019, should still be included in the results.  And, all users who joined the site in January 2019 should be included (even if they have never posted a question or provided an answer).\n",
    "\n",
    "The query from the previous question should be a nice starting point to answering this question!  You'll need to use the `posts_answers` and `posts_questions` tables.  You'll also need to use the `users` table from the Stack Overflow dataset.  The relevant columns from the `users` table are `id` (the ID of each user) and `creation_date` (when the user joined the Stack Overflow site, in DATETIME format)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "09c20522",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:04.431299Z",
     "iopub.status.busy": "2023-04-05T14:44:04.430782Z",
     "iopub.status.idle": "2023-04-05T14:44:12.010213Z",
     "shell.execute_reply": "2023-04-05T14:44:12.009254Z"
    },
    "papermill": {
     "duration": 7.593189,
     "end_time": "2023-04-05T14:44:12.012350",
     "exception": false,
     "start_time": "2023-04-05T14:44:04.419161",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>q_creation_date</th>\n",
       "      <th>a_creation_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10908175</td>\n",
       "      <td>2019-01-13 15:04:29.303000+00:00</td>\n",
       "      <td>2020-07-26 15:10:14.297000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10923839</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10977188</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10965151</td>\n",
       "      <td>NaT</td>\n",
       "      <td>2021-03-20 16:26:05.930000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10908670</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                  q_creation_date                  a_creation_date\n",
       "0  10908175 2019-01-13 15:04:29.303000+00:00 2020-07-26 15:10:14.297000+00:00\n",
       "1  10923839                              NaT                              NaT\n",
       "2  10977188                              NaT                              NaT\n",
       "3  10965151                              NaT 2021-03-20 16:26:05.930000+00:00\n",
       "4  10908670                              NaT                              NaT"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_QA_Two\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Your code here\n",
    "three_tables_query = \"\"\"\n",
    "                     SELECT u.id AS id,\n",
    "                         MIN(q.creation_date) AS q_creation_date,\n",
    "                         MIN(a.creation_date) AS a_creation_date\n",
    "                     FROM `bigquery-public-data.stackoverflow.users` AS u\n",
    "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
    "                             ON u.id = a.owner_user_id\n",
    "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
    "                             ON q.owner_user_id = u.id\n",
    "                     WHERE u.creation_date >= '2019-01-01' and u.creation_date < '2019-02-01'\n",
    "                     GROUP BY id\n",
    "                    \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_3.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1d0f5baf",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:12.034354Z",
     "iopub.status.busy": "2023-04-05T14:44:12.033952Z",
     "iopub.status.idle": "2023-04-05T14:44:12.049155Z",
     "shell.execute_reply": "2023-04-05T14:44:12.047965Z"
    },
    "papermill": {
     "duration": 0.029034,
     "end_time": "2023-04-05T14:44:12.051601",
     "exception": false,
     "start_time": "2023-04-05T14:44:12.022567",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"3_QA_Two\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> \n",
       "Here's a partially completed query.  You need only fill in the appropriate JOINs!\n",
       "```\n",
       "three_tables_query = \"\"\"\n",
       "                     SELECT u.id AS id,\n",
       "                         MIN(q.creation_date) AS q_creation_date,\n",
       "                         MIN(a.creation_date) AS a_creation_date\n",
       "                     FROM `bigquery-public-data.stackoverflow.users` AS u\n",
       "                         ____ `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                             ON u.id = a.owner_user_id\n",
       "                         ____ `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                             ON q.owner_user_id = u.id\n",
       "                     WHERE u.creation_date >= '2019-01-01' and u.creation_date < '2019-02-01'\n",
       "                     GROUP BY id\n",
       "                    \"\"\"\n",
       "```\n"
      ],
      "text/plain": [
       "Hint: \n",
       "Here's a partially completed query.  You need only fill in the appropriate JOINs!\n",
       "```\n",
       "three_tables_query = \"\"\"\n",
       "                     SELECT u.id AS id,\n",
       "                         MIN(q.creation_date) AS q_creation_date,\n",
       "                         MIN(a.creation_date) AS a_creation_date\n",
       "                     FROM `bigquery-public-data.stackoverflow.users` AS u\n",
       "                         ____ `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                             ON u.id = a.owner_user_id\n",
       "                         ____ `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                             ON q.owner_user_id = u.id\n",
       "                     WHERE u.creation_date >= '2019-01-01' and u.creation_date < '2019-02-01'\n",
       "                     GROUP BY id\n",
       "                    \"\"\"\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"3_QA_Two\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "\n",
       "three_tables_query = \"\"\"\n",
       "                     SELECT u.id AS id,\n",
       "                         MIN(q.creation_date) AS q_creation_date,\n",
       "                         MIN(a.creation_date) AS a_creation_date\n",
       "                     FROM `bigquery-public-data.stackoverflow.users` AS u\n",
       "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                             ON u.id = a.owner_user_id\n",
       "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                             ON q.owner_user_id = u.id\n",
       "                     WHERE u.creation_date >= '2019-01-01' and u.creation_date < '2019-02-01'\n",
       "                     GROUP BY id\n",
       "                    \"\"\"\n",
       "\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "\n",
       "three_tables_query = \"\"\"\n",
       "                     SELECT u.id AS id,\n",
       "                         MIN(q.creation_date) AS q_creation_date,\n",
       "                         MIN(a.creation_date) AS a_creation_date\n",
       "                     FROM `bigquery-public-data.stackoverflow.users` AS u\n",
       "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                             ON u.id = a.owner_user_id\n",
       "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                             ON q.owner_user_id = u.id\n",
       "                     WHERE u.creation_date >= '2019-01-01' and u.creation_date < '2019-02-01'\n",
       "                     GROUP BY id\n",
       "                    \"\"\"\n",
       "\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "q_3.hint()\n",
    "q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e3119d9",
   "metadata": {
    "papermill": {
     "duration": 0.009841,
     "end_time": "2023-04-05T14:44:12.071675",
     "exception": false,
     "start_time": "2023-04-05T14:44:12.061834",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### 4) How many distinct users posted on January 1, 2019?\n",
    "\n",
    "In the code cell below, write a query that returns a table with a single column:\n",
    "- `owner_user_id` - the IDs of all users who posted at least one question or answer on January 1, 2019.  Each user ID should appear at most once.\n",
    "\n",
    "In the `posts_questions` (and `posts_answers`) tables, you can get the ID of the original poster from the `owner_user_id` column.  Likewise, the date of the original posting can be found in the `creation_date` column.  \n",
    "\n",
    "In order for your answer to be marked correct, your query must use a **UNION**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1beb9609",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:12.093715Z",
     "iopub.status.busy": "2023-04-05T14:44:12.093293Z",
     "iopub.status.idle": "2023-04-05T14:44:13.743953Z",
     "shell.execute_reply": "2023-04-05T14:44:13.742822Z"
    },
    "papermill": {
     "duration": 1.6646,
     "end_time": "2023-04-05T14:44:13.746366",
     "exception": false,
     "start_time": "2023-04-05T14:44:12.081766",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/client.py:440: UserWarning: Cannot create BigQuery Storage client, the dependency google-cloud-bigquery-storage is not installed.\n",
      "  \"Cannot create BigQuery Storage client, the dependency \"\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>owner_user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4934409.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4984963.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10443903.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10855350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10854542.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   owner_user_id\n",
       "0      4934409.0\n",
       "1      4984963.0\n",
       "2     10443903.0\n",
       "3     10855350.0\n",
       "4     10854542.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"4_AllUsers\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Your code here\n",
    "all_users_query = \"\"\"\n",
    "                  SELECT q.owner_user_id \n",
    "                  FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
    "                  WHERE EXTRACT(DATE FROM q.creation_date) = '2019-01-01'\n",
    "                  UNION DISTINCT\n",
    "                  SELECT a.owner_user_id\n",
    "                  FROM `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
    "                  WHERE EXTRACT(DATE FROM a.creation_date) = '2019-01-01'\n",
    "                  \"\"\"\n",
    "\n",
    "# Check your answer\n",
    "q_4.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "82594796",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2023-04-05T14:44:13.769995Z",
     "iopub.status.busy": "2023-04-05T14:44:13.769481Z",
     "iopub.status.idle": "2023-04-05T14:44:13.784218Z",
     "shell.execute_reply": "2023-04-05T14:44:13.782982Z"
    },
    "papermill": {
     "duration": 0.029512,
     "end_time": "2023-04-05T14:44:13.786793",
     "exception": false,
     "start_time": "2023-04-05T14:44:13.757281",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"4_AllUsers\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#3366cc\">Hint:</span> Begin by writing two separate queries: one to get the user IDs corresponding to questions, and another corresponding to answers.  Then, take the union of the two columns."
      ],
      "text/plain": [
       "Hint: Begin by writing two separate queries: one to get the user IDs corresponding to questions, and another corresponding to answers.  Then, take the union of the two columns."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"4_AllUsers\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc99\">Solution:</span> \n",
       "```python\n",
       "\n",
       "all_users_query = \"\"\"\n",
       "                  SELECT q.owner_user_id \n",
       "                  FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                  WHERE EXTRACT(DATE FROM q.creation_date) = '2019-01-01'\n",
       "                  UNION DISTINCT\n",
       "                  SELECT a.owner_user_id\n",
       "                  FROM `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                  WHERE EXTRACT(DATE FROM a.creation_date) = '2019-01-01'\n",
       "                  \"\"\"\n",
       "\n",
       "```"
      ],
      "text/plain": [
       "Solution: \n",
       "```python\n",
       "\n",
       "all_users_query = \"\"\"\n",
       "                  SELECT q.owner_user_id \n",
       "                  FROM `bigquery-public-data.stackoverflow.posts_questions` AS q\n",
       "                  WHERE EXTRACT(DATE FROM q.creation_date) = '2019-01-01'\n",
       "                  UNION DISTINCT\n",
       "                  SELECT a.owner_user_id\n",
       "                  FROM `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
       "                  WHERE EXTRACT(DATE FROM a.creation_date) = '2019-01-01'\n",
       "                  \"\"\"\n",
       "\n",
       "```"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "q_4.hint()\n",
    "q_4.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7694dad0",
   "metadata": {
    "papermill": {
     "duration": 0.010733,
     "end_time": "2023-04-05T14:44:13.808668",
     "exception": false,
     "start_time": "2023-04-05T14:44:13.797935",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "# Keep going\n",
    "\n",
    "Learn how to use **[analytic functions](https://www.kaggle.com/alexisbcook/analytic-functions)** to perform complex calculations with minimal SQL code."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f135971",
   "metadata": {
    "papermill": {
     "duration": 0.010748,
     "end_time": "2023-04-05T14:44:13.830482",
     "exception": false,
     "start_time": "2023-04-05T14:44:13.819734",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/advanced-sql/discussion) to chat with other learners.*"
   ]
  }
 ],
 "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.7.12"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 69.069577,
   "end_time": "2023-04-05T14:44:14.465120",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2023-04-05T14:43:05.395543",
   "version": "2.4.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}