advanced_sql/01-joins-and-unions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[Advanced SQL Home Page](https://www.kaggle.com/learn/advanced-sql)**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n",
      "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",
   "metadata": {},
   "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,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\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>59030907</td>\n",
       "      <td>NLP Transformers: Best way to get a fixed sent...</td>\n",
       "      <td>&lt;p&gt;I'm loading a language model from torch hub...</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-25 11:36:12.360000+00:00</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2019-11-26 16:49:27.200000+00:00</td>\n",
       "      <td>2019-11-26 16:49:27.200000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>3192021.0</td>\n",
       "      <td>None</td>\n",
       "      <td>3192021</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>machine-learning|deep-learning|nlp|pytorch|wor...</td>\n",
       "      <td>256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59123008</td>\n",
       "      <td>How to detect keypress in C++ on MacOS?</td>\n",
       "      <td>&lt;p&gt;I'm writing an assignment that needs to sho...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-12-01 06:13:36.580000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-12-01 06:13:36.580000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>10166333</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>c++|events|sdl|keypress|keyboard-events</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58858824</td>\n",
       "      <td>stabilizing Android command line app benchmark...</td>\n",
       "      <td>&lt;p&gt;I'm writing C++ module which later will be ...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-14 14:14:51.043000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-11-14 14:14:51.043000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>5827390</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>android|performance-testing|benchmarking</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>58947708</td>\n",
       "      <td>How to find pixel/dp distance between 2 points...</td>\n",
       "      <td>&lt;p&gt;I need to draw limit lines on both x and y-...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-20 06:01:55.060000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-11-20 11:39:16.020000+00:00</td>\n",
       "      <td>2019-11-20 11:39:16.020000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>1631967.0</td>\n",
       "      <td>None</td>\n",
       "      <td>7637587</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>-1</td>\n",
       "      <td>mpandroidchart</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>58968440</td>\n",
       "      <td>will mobile browser download sourcemap file?</td>\n",
       "      <td>&lt;p&gt;I've read some article or question said \"so...</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-11-21 06:19:37.400000+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-11-21 06:19:37.400000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>3983650</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>source-maps</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                                              title  \\\n",
       "0  59030907  NLP Transformers: Best way to get a fixed sent...   \n",
       "1  59123008            How to detect keypress in C++ on MacOS?   \n",
       "2  58858824  stabilizing Android command line app benchmark...   \n",
       "3  58947708  How to find pixel/dp distance between 2 points...   \n",
       "4  58968440       will mobile browser download sourcemap file?   \n",
       "\n",
       "                                                body accepted_answer_id  \\\n",
       "0  <p>I'm loading a language model from torch hub...               None   \n",
       "1  <p>I'm writing an assignment that needs to sho...               None   \n",
       "2  <p>I'm writing C++ module which later will be ...               None   \n",
       "3  <p>I need to draw limit lines on both x and y-...               None   \n",
       "4  <p>I've read some article or question said \"so...               None   \n",
       "\n",
       "   answer_count  comment_count community_owned_date  \\\n",
       "0             2              0                 None   \n",
       "1             0              0                 None   \n",
       "2             0              0                 None   \n",
       "3             0              0                 None   \n",
       "4             0              0                 None   \n",
       "\n",
       "                     creation_date  favorite_count  \\\n",
       "0 2019-11-25 11:36:12.360000+00:00             2.0   \n",
       "1 2019-12-01 06:13:36.580000+00:00             NaN   \n",
       "2 2019-11-14 14:14:51.043000+00:00             NaN   \n",
       "3 2019-11-20 06:01:55.060000+00:00             NaN   \n",
       "4 2019-11-21 06:19:37.400000+00:00             NaN   \n",
       "\n",
       "                last_activity_date                   last_edit_date  \\\n",
       "0 2019-11-26 16:49:27.200000+00:00 2019-11-26 16:49:27.200000+00:00   \n",
       "1 2019-12-01 06:13:36.580000+00:00                              NaT   \n",
       "2 2019-11-14 14:14:51.043000+00:00                              NaT   \n",
       "3 2019-11-20 11:39:16.020000+00:00 2019-11-20 11:39:16.020000+00:00   \n",
       "4 2019-11-21 06:19:37.400000+00:00                              NaT   \n",
       "\n",
       "  last_editor_display_name  last_editor_user_id owner_display_name  \\\n",
       "0                     None            3192021.0               None   \n",
       "1                     None                  NaN               None   \n",
       "2                     None                  NaN               None   \n",
       "3                     None            1631967.0               None   \n",
       "4                     None                  NaN               None   \n",
       "\n",
       "   owner_user_id parent_id  post_type_id  score  \\\n",
       "0        3192021      None             1      4   \n",
       "1       10166333      None             1      0   \n",
       "2        5827390      None             1      0   \n",
       "3        7637587      None             1     -1   \n",
       "4        3983650      None             1      0   \n",
       "\n",
       "                                                tags  view_count  \n",
       "0  machine-learning|deep-learning|nlp|pytorch|wor...         256  \n",
       "1            c++|events|sdl|keypress|keyboard-events           1  \n",
       "2           android|performance-testing|benchmarking           2  \n",
       "3                                     mpandroidchart           2  \n",
       "4                                        source-maps           2  "
      ]
     },
     "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",
   "metadata": {},
   "source": [
    "We also take a look at the `posts_answers` table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>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>58545647</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;You can implement the &lt;a href=\"https://docs...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:35:51.947000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:35:51.947000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2541560</td>\n",
       "      <td>58545487</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>58545649</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;You may be having an issue with the \"stage\"...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:35:59.377000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:35:59.377000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4434749</td>\n",
       "      <td>56565949</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>58545664</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;I am not sure why you need that exactly but...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:36:39.870000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:36:39.870000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>8343843</td>\n",
       "      <td>58545068</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>58545675</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;pre&gt;&lt;code&gt;Object delegateObj = readField(valu...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:37:20.207000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:37:20.207000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>12269981</td>\n",
       "      <td>57195785</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>58545677</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;I had to remove the line&lt;/p&gt;\\n\\n&lt;pre&gt;&lt;code&gt;...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:37:51.253000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>2019-10-24 16:37:51.253000+00:00</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1775258</td>\n",
       "      <td>58428566</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id title                                               body  \\\n",
       "0  58545647  None  <p>You can implement the <a href=\"https://docs...   \n",
       "1  58545649  None  <p>You may be having an issue with the \"stage\"...   \n",
       "2  58545664  None  <p>I am not sure why you need that exactly but...   \n",
       "3  58545675  None  <pre><code>Object delegateObj = readField(valu...   \n",
       "4  58545677  None  <p>I had to remove the line</p>\\n\\n<pre><code>...   \n",
       "\n",
       "  accepted_answer_id answer_count  comment_count community_owned_date  \\\n",
       "0               None         None              0                 None   \n",
       "1               None         None              0                 None   \n",
       "2               None         None              0                 None   \n",
       "3               None         None              1                 None   \n",
       "4               None         None              0                 None   \n",
       "\n",
       "                     creation_date favorite_count  \\\n",
       "0 2019-10-24 16:35:51.947000+00:00           None   \n",
       "1 2019-10-24 16:35:59.377000+00:00           None   \n",
       "2 2019-10-24 16:36:39.870000+00:00           None   \n",
       "3 2019-10-24 16:37:20.207000+00:00           None   \n",
       "4 2019-10-24 16:37:51.253000+00:00           None   \n",
       "\n",
       "                last_activity_date last_edit_date last_editor_display_name  \\\n",
       "0 2019-10-24 16:35:51.947000+00:00           None                     None   \n",
       "1 2019-10-24 16:35:59.377000+00:00           None                     None   \n",
       "2 2019-10-24 16:36:39.870000+00:00           None                     None   \n",
       "3 2019-10-24 16:37:20.207000+00:00           None                     None   \n",
       "4 2019-10-24 16:37:51.253000+00:00           None                     None   \n",
       "\n",
       "  last_editor_user_id owner_display_name  owner_user_id  parent_id  \\\n",
       "0                None               None        2541560   58545487   \n",
       "1                None               None        4434749   56565949   \n",
       "2                None               None        8343843   58545068   \n",
       "3                None               None       12269981   57195785   \n",
       "4                None               None        1775258   58428566   \n",
       "\n",
       "   post_type_id  score  tags view_count  \n",
       "0             2      0  None       None  \n",
       "1             2      0  None       None  \n",
       "2             2      0  None       None  \n",
       "3             2      0  None       None  \n",
       "4             2      0  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",
   "metadata": {},
   "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,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of answered questions: 100.0%\n",
      "Number of questions: 134227\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>48278223</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48121252</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48142530</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48164517</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48231020</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       q_id  time_to_answer\n",
       "0  48278223               0\n",
       "1  48121252               0\n",
       "2  48142530               0\n",
       "3  48164517               0\n",
       "4  48231020               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",
   "metadata": {},
   "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,
   "metadata": {},
   "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>q_id</th>\n",
       "      <th>time_to_answer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48378205</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48436005</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48364609</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>48479429</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>48419916</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       q_id  time_to_answer\n",
       "0  48378205             NaN\n",
       "1  48436005             NaN\n",
       "2  48364609             NaN\n",
       "3  48479429             NaN\n",
       "4  48419916             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": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of answered questions: 82.4009331164247%\n",
      "Number of questions: 162895\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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_1.hint()\n",
    "#q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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 `cbeck()` 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,
   "metadata": {},
   "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>3666987</td>\n",
       "      <td>2019-01-14 20:07:58.527000+00:00</td>\n",
       "      <td>2019-01-31 17:41:19.173000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3270760</td>\n",
       "      <td>2019-01-31 15:10:23.127000+00:00</td>\n",
       "      <td>2019-01-31 19:09:26.673000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2415802</td>\n",
       "      <td>2019-01-19 04:29:42.453000+00:00</td>\n",
       "      <td>2019-01-07 23:56:45.687000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5359103</td>\n",
       "      <td>2019-01-28 17:44:53.377000+00:00</td>\n",
       "      <td>2019-01-31 19:35:00.907000+00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>978690</td>\n",
       "      <td>2019-01-30 08:19:12.993000+00:00</td>\n",
       "      <td>2019-01-08 13:06:41.443000+00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   owner_user_id                  q_creation_date  \\\n",
       "0        3666987 2019-01-14 20:07:58.527000+00:00   \n",
       "1        3270760 2019-01-31 15:10:23.127000+00:00   \n",
       "2        2415802 2019-01-19 04:29:42.453000+00:00   \n",
       "3        5359103 2019-01-28 17:44:53.377000+00:00   \n",
       "4         978690 2019-01-30 08:19:12.993000+00:00   \n",
       "\n",
       "                   a_creation_date  \n",
       "0 2019-01-31 17:41:19.173000+00:00  \n",
       "1 2019-01-31 19:09:26.673000+00:00  \n",
       "2 2019-01-07 23:56:45.687000+00:00  \n",
       "3 2019-01-31 19:35:00.907000+00:00  \n",
       "4 2019-01-08 13:06:41.443000+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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_2.hint()\n",
    "#q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>q_creation_date</th>\n",
       "      <th>a_creation_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10898500</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10976441</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10972549</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10978950</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10891714</td>\n",
       "      <td>2019-01-09 19:24:58.883000+00:00</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                  q_creation_date a_creation_date\n",
       "0  10898500                              NaT             NaT\n",
       "1  10976441                              NaT             NaT\n",
       "2  10972549                              NaT             NaT\n",
       "3  10978950                              NaT             NaT\n",
       "4  10891714 2019-01-09 19:24:58.883000+00:00             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_questions` AS q\n",
    "                             ON u.id = q.owner_user_id\n",
    "                         LEFT JOIN `bigquery-public-data.stackoverflow.posts_answers` AS a\n",
    "                             ON u.id = a.owner_user_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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_3.hint()\n",
    "#q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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,
   "metadata": {},
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10503204.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1118066.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6147499.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4445200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10658268.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   owner_user_id\n",
       "0     10503204.0\n",
       "1      1118066.0\n",
       "2      6147499.0\n",
       "3      4445200.0\n",
       "4     10658268.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 AS 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 AS 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,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lines below will give you a hint or solution code\n",
    "#q_4.hint()\n",
    "#q_4.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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",
   "metadata": {},
   "source": [
    "---\n",
    "**[Advanced SQL Home Page](https://www.kaggle.com/learn/advanced-sql)**\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "*Have questions or comments? Visit the [Learn Discussion forum](https://www.kaggle.com/learn-forum) 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.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}