intro_to_sql/06-joining-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[SQL Course Home Page](https://www.kaggle.com/learn/SQL)**\n",
    "\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Intro\n",
    "\n",
    "Stack Overflow (stackoverflow.com) is a widely beloved Question and Answer site for technical questions. You'll probably use it yourself as you keep using SQL (or any programming language). \n",
    "\n",
    "Their data is publicly available. What cool things do you think it would be useful for?\n",
    "\n",
    "Here's one idea:\n",
    "You could set up a service that identifies the Stack Overflow users who have demonstrated expertise with a specific technology by answering related questions about it, so someone could hire those experts for in-depth help.\n",
    "\n",
    "In this exercise, you'll write the SQL queries that might serve as the foundation for this type of service.\n",
    "\n",
    "As usual, run the following cell to set up our feedback system before moving on."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n",
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    }
   ],
   "source": [
    "# Set up feedack system\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.sql.ex6 import *\n",
    "\n",
    "# import package with helper functions \n",
    "import bq_helper\n",
    "\n",
    "# create a helper object for this dataset\n",
    "stack_overflow = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n",
    "                                              dataset_name=\"stackoverflow\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Questions\n",
    "\n",
    "# 1) Explore the Data\n",
    "\n",
    "Before writing queries or **JOIN** clauses, you'll want to see what tables are available. \n",
    "\n",
    "This may be a good time to practice **tab completion** for when you don't remember command names. If you type `stack_overflow.` and then hit tab, you will see a list of methods for the `stack_overflow` object (don't forget the dot before hitting tab.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['badges', 'comments', 'post_history', 'post_links', 'posts_answers', 'posts_moderator_nomination', 'posts_orphaned_tag_wiki', 'posts_privilege_wiki', 'posts_questions', 'posts_tag_wiki', 'posts_tag_wiki_excerpt', 'posts_wiki_placeholder', 'stackoverflow_posts', 'tags', 'users', 'votes']\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"1_ListSOTables\", \"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",
    "list_of_tables = stack_overflow.list_tables()    # get a list of available tables\n",
    "\n",
    "print(list_of_tables)\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2) Review Relevant Tables\n",
    "\n",
    "If you are interested in people who answer questions on a given topic, the `posts_answers` table is a natural place to look. Run the following cell and look at the output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack_overflow.head('posts_answers')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It isn't clear yet how to the find users who answered questions on any given topic. But `posts_answers` has a `parent_id` column. If you are familiar with the Stack Overflow site, you might figure out that the `parent_id` is the question each post is answering.\n",
    "\n",
    "Look at `posts_questions` using the line below."
   ]
  },
  {
   "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>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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stack_overflow.head('posts_questions')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Are there any fields that identify what topic or technology each question is about?\n",
    "\n",
    "If so, how could you find the user ID\\'s of users who answered questions about a specific topic?\n",
    "\n",
    "Think about it, then check the solution by running the code in the next cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 4, \"questionId\": \"2_HowToFindExperts\", \"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",
       "`posts_questions` has a column called `tags` which lists the topics/technologies each question is about.\n",
       "\n",
       "`posts_answers` has a column called `parent_id` which identifies the ID of the question each answer is responding to.\n",
       "`posts_answers` also has an `owner_user_id` column which specifies the ID of the user who answered the question.\n",
       "\n",
       "You can join these two tables to:\n",
       "- determine the `tags` for each answer, and then\n",
       "- select the `owner_user_id` of the answers on the desired tag.\n",
       "\n",
       "This is exactly what you will do over the next few questions.\n"
      ],
      "text/plain": [
       "Solution: \n",
       "`posts_questions` has a column called `tags` which lists the topics/technologies each question is about.\n",
       "\n",
       "`posts_answers` has a column called `parent_id` which identifies the ID of the question each answer is responding to.\n",
       "`posts_answers` also has an `owner_user_id` column which specifies the ID of the user who answered the question.\n",
       "\n",
       "You can join these two tables to:\n",
       "- determine the `tags` for each answer, and then\n",
       "- select the `owner_user_id` of the answers on the desired tag.\n",
       "\n",
       "This is exactly what you will do over the next few questions."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 3 Selecting The Right Questions\n",
    "\n",
    "A lot of this data is text. \n",
    "\n",
    "Here is one last technique you'll learn in this course which you can apply to this text:\n",
    "\n",
    "A **WHERE** clause can limit your results to rows with certain text using the **LIKE** feature. For example, to select just the third row of the `pets` table, we would write\n",
    "\n",
    "`SELECT * FROM PETS WHERE NAME LIKE 'Ripley'`\n",
    "\n",
    "![](https://i.imgur.com/Ef4Puo3.png)\n",
    "\n",
    "You can also use `%` as a \"wildcard\" for any number of characters. So you can get the third row with \n",
    "\n",
    "`SELECT * FROM PETS WHERE NAME LIKE '%ipl%'`\n",
    "\n",
    "Try this yourself.\n",
    "Before finding users who have answered questions, write a query that selects the `id`, `title` and `owner_user_id` from the `posts_questions` table. Restrict the results to rows that contain the word **bigquery** in the `tags` column. Include rows where there is other text in addition to the word `bigquery` (e.g. if a row has a tag `bigquery-sql`, your results should include that too)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         id                                              title  owner_user_id\n",
      "0  59104395  How to store datetimeoffset(4) datatype from s...     12436928.0\n",
      "1  58861311  Count and records from yesterday and add datec...      6347701.0\n",
      "2  59075362  Google Bigquery, getting beginning and end of ...     12447336.0\n",
      "3  59072924  Calculate avg daily and hourly phone call roll...     12379424.0\n",
      "4  58986030  No matching signature for function TIMESTAMP_D...     12081490.0\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_SelectRightQuestions\", \"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",
    "questions_query = \\\n",
    "\"\"\"\n",
    "SELECT id, title, owner_user_id\n",
    "FROM `bigquery-public-data.stackoverflow.posts_questions`\n",
    "WHERE tags LIKE '%bigquery%'\n",
    "\"\"\"\n",
    "\n",
    "questions_results = stack_overflow.query_to_pandas_safe(questions_query, max_gb_scanned=25) # this query reads a lot of data\n",
    "print(questions_results.head())\n",
    "q_3.check()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# q_3.hint()\n",
    "# q_3.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 4 Your First Join\n",
    "Now that you have a query to select questions on any given topic (in this case, you chose `bigquery`), you can find the answers to those questions with a **JOIN**.  \n",
    "\n",
    "Write a SQL query that returns the `id`, `body` and `owner_user_id` from the `posts_answers` table for answers to `bigquery` related questions. That is, you should have one row in your results for each answer to a question that has a `bigquery` in the tag.\n",
    "\n",
    "Here's a reminder of what a **JOIN** looked like in the tutorial\n",
    "```\n",
    "SELECT p.Name AS Pet_Name, o.Name as Owner_Name\n",
    "FROM `bigquery-public-data.pet_records.pets` as p\n",
    "INNER JOIN `bigquery-public-data.pet_records.owners` as o ON p.ID = o.Pet_ID\n",
    "```\n",
    "\n",
    "It may be useful to scroll up and review the results from when you called **head** on `posts_answers` and `posts_questions`.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         id                                               body  owner_user_id\n",
      "0  33483810  <hr>\\n\\n<p>The above example doesn't show how ...      2379486.0\n",
      "1  33504705  <p>I'm not sure if this question is still open...      5520858.0\n",
      "2  33520266  <p>Yes, the link you have is the way to use se...      1066506.0\n",
      "3  33540643  <p>For copying large files from some HTTP loca...        40999.0\n",
      "4  33590170  <p>you've already solved it by partitioning. i...      2213940.0\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"4_FirstJoin\", \"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": [
    "from time import time\n",
    "\n",
    "answers_query = \\\n",
    "\"\"\"\n",
    "SELECT pa.id, pa.body, pa.owner_user_id\n",
    "    FROM `bigquery-public-data.stackoverflow.posts_questions` AS pq\n",
    "        INNER JOIN `bigquery-public-data.stackoverflow.posts_answers` AS pa\n",
    "            ON pq.id = pa.parent_id\n",
    "    WHERE pq.tags LIKE '%bigquery%'\n",
    "\"\"\"\n",
    "\n",
    "answers_results = stack_overflow.query_to_pandas_safe(answers_query, max_gb_scanned=50) # query scans more than 1GB of data, but less than 2.\n",
    "print(answers_results.head())\n",
    "q_4.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# q_4.hint()\n",
    "# q_4.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 5 Answer The Question\n",
    "You have the merge you need. But you want a list of users who have answered many questions... which requires more work beyond your previous result.\n",
    "\n",
    "Write a new query that selects data from the `posts_questions` and `posts_answers` tables. The results should have a single row for each user who answered at least one questions with a tag that includes the string `bigquery`. Each row in your results should have two columns:\n",
    "- a column called `user_id` that contains the `owner_user_id` from the `posts_answers` table\n",
    "- a column called `number_of_answers` that contains the number of answers the user has written to `bigquery` questions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     user_id  number_of_answers\n",
      "0  5239549.0                 39\n",
      "1  8212141.0                 18\n",
      "2  1560780.0                 16\n",
      "3    19880.0                 35\n",
      "4  4001094.0                 93\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"5_BigQueryExperts\", \"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",
    "bigquery_experts_query =  \\\n",
    "\"\"\"\n",
    "SELECT pa.owner_user_id AS user_id, COUNT(1) AS number_of_answers\n",
    "    FROM `bigquery-public-data.stackoverflow.posts_questions` AS pq\n",
    "        INNER JOIN `bigquery-public-data.stackoverflow.posts_answers` AS pa\n",
    "            ON pq.id = pa.parent_id\n",
    "    WHERE pq.tags LIKE '%bigquery%'\n",
    "    GROUP BY pa.owner_user_id\n",
    "    HAVING number_of_answers > 0\n",
    "\"\"\"\n",
    "bigquery_experts_results = stack_overflow.query_to_pandas_safe(bigquery_experts_query, max_gb_scanned=50)\n",
    "\n",
    "print(bigquery_experts_results.head())\n",
    "q_5.check()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# q_5.hint()\n",
    "# q_5.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Building A More Generally Useful Service\n",
    "\n",
    "How could you convert what you've done so it's a general function a website could call on the backend to get experts on any topic?  \n",
    "\n",
    "Think about it and then check the solution below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     user_id  number_of_answers\n",
      "0  3574081.0               1065\n",
      "1  1782792.0                565\n",
      "2   712995.0                478\n",
      "3  2097240.0                445\n",
      "4   992489.0                404\n"
     ]
    }
   ],
   "source": [
    "def expert_finder(topic, bigQueryHelper):\n",
    "    '''\n",
    "    Returns a DataFrame with the user IDs who have written Stack Overflow answers on a topic.\n",
    "    \n",
    "    Inputs:\n",
    "        topic: A string with the topic of interest\n",
    "        bigQueryHelper: A BigQueryHelper object that specifies the connection to the Stack Overflow dataset\n",
    "\n",
    "    Outputs:\n",
    "        results: A DataFrame with columns for user_id and number_of_answers sorted by number_of_answers.\n",
    "    '''\n",
    "    query = f\"\"\"\n",
    "            SELECT pa.owner_user_id AS user_id, COUNT(1) AS number_of_answers\n",
    "                FROM `bigquery-public-data.stackoverflow.posts_questions` AS pq\n",
    "                    INNER JOIN `bigquery-public-data.stackoverflow.posts_answers` AS pa\n",
    "                        ON pq.id = pa.parent_id\n",
    "                WHERE pq.tags LIKE '%{topic}%'\n",
    "                GROUP BY pa.owner_user_id\n",
    "                ORDER BY number_of_answers DESC\n",
    "            \"\"\"\n",
    "    return bigQueryHelper.query_to_pandas_safe(query, max_gb_scanned=50)\n",
    "    \n",
    "print(expert_finder('tensorflow', stack_overflow).head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# q_6.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Congratulations\n",
    "You know all the key components to use BigQuery and SQL effectively. Your SQL skills are sufficient to unlock many of the world's large datasets.\n",
    "\n",
    "Want to go play with your new powers?  Kaggle has BigQuery datasets available [here](https://www.kaggle.com/datasets?sortBy=hottest&group=public&page=1&pageSize=20&size=sizeAll&filetype=fileTypeBigQuery).\n",
    "\n",
    "# Feedback\n",
    "Bring any questions or feedback to the [Learn Discussion Forum](https://www.kaggle.com/learn-forum).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "**[SQL Course Home Page](https://www.kaggle.com/learn/SQL)**\n",
    "\n"
   ]
  }
 ],
 "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
}