SQL_and_BigQuery_day6_JOINING_DATA.ipynb

{"cells":[{"metadata":{},"cell_type":"markdown","source":"**[SQL Home Page](https://www.kaggle.com/learn/intro-to-sql)**\n\n---\n"},{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\n[Stack Overflow](https://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\nTheir data is publicly available. What cool things do you think it would be useful for?\n\nHere's one idea:\nYou 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\nIn this exercise, you'll write the SQL queries that might serve as the foundation for this type of service.\n\nAs usual, run the following cell to set up our feedback system before moving on."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql.ex6 import *\nprint(\"Setup Complete\")","execution_count":1,"outputs":[{"output_type":"stream","text":"Using Kaggle's public dataset BigQuery integration.\nSetup Complete\n","name":"stdout"}]},{"metadata":{},"cell_type":"markdown","source":"Run the next cell to fetch the `stackoverflow` dataset."},{"metadata":{"trusted":true},"cell_type":"code","source":"from google.cloud import bigquery\n\n# Create a \"Client\" object\nclient = bigquery.Client()\n\n# Construct a reference to the \"stackoverflow\" dataset\ndataset_ref = client.dataset(\"stackoverflow\", project=\"bigquery-public-data\")\n\n# API request - fetch the dataset\ndataset = client.get_dataset(dataset_ref)\n\n","execution_count":2,"outputs":[{"output_type":"stream","text":"Using Kaggle's public dataset BigQuery integration.\n","name":"stdout"}]},{"metadata":{},"cell_type":"markdown","source":"# Exercises\n\n### 1) Explore the data\n\nBefore writing queries or **JOIN** clauses, you'll want to see what tables are available. \n\n*Hint*: Tab completion is helpful whenever you can't remember a command. Type `client.` and then hit the tab key. Don't forget the period before hitting tab."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Get a list of available tables \nlist_of_tables = [ i.table_id for i in client.list_tables(dataset)]\n\n# Print your answer\nprint(list_of_tables)\n\n# Check your answer\nq_1.check()","execution_count":3,"outputs":[{"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","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 1, \"learnTutorialId\": 82, \"questionId\": \"1_ListSOTables\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"For the solution, uncomment the line below."},{"metadata":{"trusted":true},"cell_type":"code","source":"#q_1.solution()","execution_count":4,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 2) Review relevant tables\n\nIf 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."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Construct a reference to the \"posts_answers\" table\nanswers_table_ref = dataset_ref.table(\"posts_answers\")\n\n# API request - fetch the table\nanswers_table = client.get_table(answers_table_ref)\n\n# Preview the first five lines of the \"posts_answers\" table\nclient.list_rows(answers_table, max_results=5).to_dataframe()","execution_count":5,"outputs":[{"output_type":"execute_result","execution_count":5,"data":{"text/plain":"         id                                               body  comment_count  \\\n0  47949879  <p>Building onto what you have nearly got..</p...              0   \n1  15503802  <p>Normally, \"naturally aligned\" means that an...              1   \n2   3497784  <p>Looking with Reflector, it seems to indicat...              0   \n3  39122150  <p>Probably not. I don't know what version of ...              1   \n4  36403800  <p><code>replace(',', '')</code> only replaces...              0   \n\n  community_owned_date                    creation_date  \\\n0                 None 2017-12-23 04:57:17.177000+00:00   \n1                 None 2013-03-19 15:34:54.153000+00:00   \n2                 None 2010-08-16 22:04:25.970000+00:00   \n3                 None 2016-08-24 11:30:55.667000+00:00   \n4                 None 2016-04-04 13:18:15.710000+00:00   \n\n                last_activity_date                   last_edit_date  \\\n0 2017-12-23 04:57:17.177000+00:00                              NaT   \n1 2013-03-19 15:34:54.153000+00:00                              NaT   \n2 2010-08-16 22:04:25.970000+00:00                              NaT   \n3 2016-08-24 11:30:55.667000+00:00 2017-05-23 12:09:42.380000+00:00   \n4 2016-04-04 13:18:15.710000+00:00                              NaT   \n\n  last_editor_display_name  last_editor_user_id owner_display_name  \\\n0                                           NaN                      \n1                                           NaN                      \n2                                           NaN                      \n3                                          -1.0                      \n4                                           NaN                      \n\n   owner_user_id  parent_id  post_type_id  score tags  \n0        5602957   47949810             2      2       \n1         179910   15503537             2      3       \n2           1831    3497732             2      2       \n3        2868335   39110336             2      3       \n4        2825245   36403626             2      2       ","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>body</th>\n      <th>comment_count</th>\n      <th>community_owned_date</th>\n      <th>creation_date</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    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>47949879</td>\n      <td>&lt;p&gt;Building onto what you have nearly got..&lt;/p...</td>\n      <td>0</td>\n      <td>None</td>\n      <td>2017-12-23 04:57:17.177000+00:00</td>\n      <td>2017-12-23 04:57:17.177000+00:00</td>\n      <td>NaT</td>\n      <td></td>\n      <td>NaN</td>\n      <td></td>\n      <td>5602957</td>\n      <td>47949810</td>\n      <td>2</td>\n      <td>2</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>15503802</td>\n      <td>&lt;p&gt;Normally, \"naturally aligned\" means that an...</td>\n      <td>1</td>\n      <td>None</td>\n      <td>2013-03-19 15:34:54.153000+00:00</td>\n      <td>2013-03-19 15:34:54.153000+00:00</td>\n      <td>NaT</td>\n      <td></td>\n      <td>NaN</td>\n      <td></td>\n      <td>179910</td>\n      <td>15503537</td>\n      <td>2</td>\n      <td>3</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3497784</td>\n      <td>&lt;p&gt;Looking with Reflector, it seems to indicat...</td>\n      <td>0</td>\n      <td>None</td>\n      <td>2010-08-16 22:04:25.970000+00:00</td>\n      <td>2010-08-16 22:04:25.970000+00:00</td>\n      <td>NaT</td>\n      <td></td>\n      <td>NaN</td>\n      <td></td>\n      <td>1831</td>\n      <td>3497732</td>\n      <td>2</td>\n      <td>2</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>39122150</td>\n      <td>&lt;p&gt;Probably not. I don't know what version of ...</td>\n      <td>1</td>\n      <td>None</td>\n      <td>2016-08-24 11:30:55.667000+00:00</td>\n      <td>2016-08-24 11:30:55.667000+00:00</td>\n      <td>2017-05-23 12:09:42.380000+00:00</td>\n      <td></td>\n      <td>-1.0</td>\n      <td></td>\n      <td>2868335</td>\n      <td>39110336</td>\n      <td>2</td>\n      <td>3</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>36403800</td>\n      <td>&lt;p&gt;&lt;code&gt;replace(',', '')&lt;/code&gt; only replaces...</td>\n      <td>0</td>\n      <td>None</td>\n      <td>2016-04-04 13:18:15.710000+00:00</td>\n      <td>2016-04-04 13:18:15.710000+00:00</td>\n      <td>NaT</td>\n      <td></td>\n      <td>NaN</td>\n      <td></td>\n      <td>2825245</td>\n      <td>36403626</td>\n      <td>2</td>\n      <td>2</td>\n      <td></td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"It isn't clear yet how to 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\nLook at `posts_questions` using the cell below."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Construct a reference to the \"posts_questions\" table\nquestions_table_ref = dataset_ref.table(\"posts_questions\")\n\n# API request - fetch the table\nquestions_table = client.get_table(questions_table_ref)\n\n# Preview the first five lines of the \"posts_questions\" table\nclient.list_rows(questions_table, max_results=5).to_dataframe()","execution_count":6,"outputs":[{"output_type":"execute_result","execution_count":6,"data":{"text/plain":"         id                                              title  \\\n0   9804136  How do I clear an array of a structure in C, a...   \n1  44896935  How to implement threading to run two bash she...   \n2   3059091  How to remove carriage returns from output of ...   \n3   8926063  Code Coverage: Why is end marker red (End If, ...   \n4  52977342  incorrect checksum for freed object - object w...   \n\n                                                body  accepted_answer_id  \\\n0  <p>Two questions:</p>\\n\\n<ol>\\n<li>How to quic...           9804289.0   \n1  <p>I have to record a wav file and at the same...          44897223.0   \n2  <p>I am using wordpress as a CMS and trying to...           3592816.0   \n3  <p>I use MS-Test with Visual Studio 2010 and V...           8934375.0   \n4  <p>I was hoping the return values in c++11 are...                 NaN   \n\n   answer_count  comment_count community_owned_date  \\\n0             3              2                 None   \n1             2              6                 None   \n2             7              5                 None   \n3             4              0                 None   \n4             0             10                 None   \n\n                     creation_date  favorite_count  \\\n0 2012-03-21 11:54:26.897000+00:00             NaN   \n1 2017-07-04 04:14:50.020000+00:00             1.0   \n2 2010-06-17 05:26:30.147000+00:00            11.0   \n3 2012-01-19 12:32:19.227000+00:00             NaN   \n4 2018-10-24 20:17:45.643000+00:00             1.0   \n\n                last_activity_date                   last_edit_date  \\\n0 2012-03-21 14:33:12.307000+00:00 2012-03-21 14:33:12.307000+00:00   \n1 2017-07-11 09:23:08.210000+00:00 2017-07-11 09:23:08.210000+00:00   \n2 2017-05-11 18:05:49.817000+00:00 2010-08-28 06:13:26.817000+00:00   \n3 2012-01-19 22:33:52.037000+00:00 2012-01-19 13:16:56.100000+00:00   \n4 2018-10-24 20:17:45.643000+00:00                              NaT   \n\n  last_editor_display_name  last_editor_user_id owner_display_name  \\\n0                                      882600.0                      \n1                                     3178797.0                      \n2                                       82330.0                      \n3                                      254041.0                      \n4                                           NaN                      \n\n   owner_user_id  post_type_id  score  \\\n0         662586             1      1   \n1        3178797             1      1   \n2          82330             1     26   \n3         254041             1      4   \n4        1935611             1      0   \n\n                                                tags  view_count  \n0                                           c|struct        6462  \n1  python|multithreading|bash|shell|python-multit...         411  \n2               php|javascript|wordpress|google-maps       73286  \n3          vb.net|visual-studio|mstest|code-coverage         566  \n4                                          c++|c++11         203  ","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>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>9804136</td>\n      <td>How do I clear an array of a structure in C, a...</td>\n      <td>&lt;p&gt;Two questions:&lt;/p&gt;\\n\\n&lt;ol&gt;\\n&lt;li&gt;How to quic...</td>\n      <td>9804289.0</td>\n      <td>3</td>\n      <td>2</td>\n      <td>None</td>\n      <td>2012-03-21 11:54:26.897000+00:00</td>\n      <td>NaN</td>\n      <td>2012-03-21 14:33:12.307000+00:00</td>\n      <td>2012-03-21 14:33:12.307000+00:00</td>\n      <td></td>\n      <td>882600.0</td>\n      <td></td>\n      <td>662586</td>\n      <td>1</td>\n      <td>1</td>\n      <td>c|struct</td>\n      <td>6462</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>44896935</td>\n      <td>How to implement threading to run two bash she...</td>\n      <td>&lt;p&gt;I have to record a wav file and at the same...</td>\n      <td>44897223.0</td>\n      <td>2</td>\n      <td>6</td>\n      <td>None</td>\n      <td>2017-07-04 04:14:50.020000+00:00</td>\n      <td>1.0</td>\n      <td>2017-07-11 09:23:08.210000+00:00</td>\n      <td>2017-07-11 09:23:08.210000+00:00</td>\n      <td></td>\n      <td>3178797.0</td>\n      <td></td>\n      <td>3178797</td>\n      <td>1</td>\n      <td>1</td>\n      <td>python|multithreading|bash|shell|python-multit...</td>\n      <td>411</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3059091</td>\n      <td>How to remove carriage returns from output of ...</td>\n      <td>&lt;p&gt;I am using wordpress as a CMS and trying to...</td>\n      <td>3592816.0</td>\n      <td>7</td>\n      <td>5</td>\n      <td>None</td>\n      <td>2010-06-17 05:26:30.147000+00:00</td>\n      <td>11.0</td>\n      <td>2017-05-11 18:05:49.817000+00:00</td>\n      <td>2010-08-28 06:13:26.817000+00:00</td>\n      <td></td>\n      <td>82330.0</td>\n      <td></td>\n      <td>82330</td>\n      <td>1</td>\n      <td>26</td>\n      <td>php|javascript|wordpress|google-maps</td>\n      <td>73286</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>8926063</td>\n      <td>Code Coverage: Why is end marker red (End If, ...</td>\n      <td>&lt;p&gt;I use MS-Test with Visual Studio 2010 and V...</td>\n      <td>8934375.0</td>\n      <td>4</td>\n      <td>0</td>\n      <td>None</td>\n      <td>2012-01-19 12:32:19.227000+00:00</td>\n      <td>NaN</td>\n      <td>2012-01-19 22:33:52.037000+00:00</td>\n      <td>2012-01-19 13:16:56.100000+00:00</td>\n      <td></td>\n      <td>254041.0</td>\n      <td></td>\n      <td>254041</td>\n      <td>1</td>\n      <td>4</td>\n      <td>vb.net|visual-studio|mstest|code-coverage</td>\n      <td>566</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>52977342</td>\n      <td>incorrect checksum for freed object - object w...</td>\n      <td>&lt;p&gt;I was hoping the return values in c++11 are...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>10</td>\n      <td>None</td>\n      <td>2018-10-24 20:17:45.643000+00:00</td>\n      <td>1.0</td>\n      <td>2018-10-24 20:17:45.643000+00:00</td>\n      <td>NaT</td>\n      <td></td>\n      <td>NaN</td>\n      <td></td>\n      <td>1935611</td>\n      <td>1</td>\n      <td>0</td>\n      <td>c++|c++11</td>\n      <td>203</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"Are there any fields that identify what topic or technology each question is about? If so, how could you find the IDs of users who answered questions about a specific topic?\n\nThink about it, and then check the solution by running the code in the next cell."},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"q_2.solution()","execution_count":7,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 4, \"learnTutorialId\": 82, \"questionId\": \"2_HowToFindExperts\", \"learnToolsVersion\": \"0.3.1\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"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\nYou 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\nThis is exactly what you will do over the next few questions.","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\nYou 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\nThis is exactly what you will do over the next few questions.\n"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"### 3) Selecting the right questions\n\nA lot of this data is text. \n\nWe'll explore one last technique in this course which you can apply to this text.\n\nA **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 from the tutorial, we could use the query in the picture below.\n\n![](https://i.imgur.com/RccsXBr.png) \n\nYou can also use `%` as a \"wildcard\" for any number of characters. So you can also get the third row with:\n\n```\nquery = \"\"\"\n        SELECT * \n        FROM `bigquery-public-data.pet_records.pets` \n        WHERE Name LIKE '%ipl%'\n        \"\"\"\n```\n\nTry this yourself. Write a query that selects the `id`, `title` and `owner_user_id` columns from the `posts_questions` table. \n- Restrict the results to rows that contain the word \"bigquery\" in the `tags` column. \n- 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)."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code here\nquestions_query = \"\"\"\n                  SELECT id,title, owner_user_id\n                  FROM `bigquery-public-data.stackoverflow.posts_questions`\n                  WHERE tags like '%bigquery%'\n                  \"\"\"\n\n# Set up the query (cancel the query if it would use too much of \n# your quota, with the limit set to 1 GB)\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nquestions_query_job = client.query(questions_query,job_config=safe_config) # Your code goes here\n\n# API request - run the query, and return a pandas DataFrame\nquestions_results = questions_query_job.to_dataframe() # Your code goes here\n\n# Preview results\nprint(questions_results.head())\n\n# Check your answer\nq_3.check()","execution_count":8,"outputs":[{"output_type":"stream","text":"         id                                              title  owner_user_id\n0  41270218        Resource Exceeded error message in order by      7327296.0\n1  54686057  unnest() used, but still cannot access field t...     11060526.0\n2  20746489                                   Uploading to GCS      2200045.0\n3  49546047  BigQuery UPDATE/DELETE statements per day per ...      5066083.0\n4  51747814               Dynamic queries with BigQuery from R      7257337.0\n","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 82, \"questionId\": \"3_SelectRightQuestions\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"For a hint or the solution, uncomment the appropriate line below."},{"metadata":{"trusted":false},"cell_type":"code","source":"#q_3.hint()\n#q_3.solution()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 4) Your first join\nNow 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\nWrite a query that returns the `id`, `body` and `owner_user_id` columns from the `posts_answers` table for answers to \"bigquery\"-related questions. \n- You should have one row in your results for each answer to a question that has \"bigquery\" in the tags.  \n- Remember you can get the tags for a question from the `tags` column in the `posts_questions` table.\n\nHere's a reminder of what a **JOIN** looked like in the tutorial:\n```\nquery = \"\"\"\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 \n            ON p.ID = o.Pet_ID\n        \"\"\"\n```\n\nIt may be useful to scroll up and review the first several rows of the `posts_answers` and `posts_questions` tables.  "},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code here\n\n\n\n\nanswers_query = \"\"\"\n                SELECT a.id, a.body, a.owner_user_id\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.tags LIKE '%bigquery%'\n                \"\"\"\n\n\n\n# Set up the query\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nanswers_query_job = client.query(answers_query,job_config=safe_config) # Your code goes here\n\n# API request - run the query, and return a pandas DataFrame\nanswers_results = answers_query_job.to_dataframe() # Your code goes here\n\n# Preview results\nprint(answers_results.head())\n\n# Check your answer\nq_4.check()","execution_count":13,"outputs":[{"output_type":"stream","text":"         id                                               body  owner_user_id\n0  52238894  <p>I was able to find the following <a href=\"h...      9036351.0\n1  20219025  <p>There is a possible solution here if you do...      3035921.0\n2  39188585  <p>As per your comment, Considering you have a...      3349551.0\n3  47087736  <p>Option 1.\\nExport table by partition. I cre...      2543803.0\n4  52749810  <p>I think cloud datastore can be used as a ba...      1608682.0\n","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 82, \"questionId\": \"4_FirstJoin\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"### 5) Answer the question\nYou 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\nWrite a new query that has a single row for each user who answered at least one question with a tag that includes the string \"bigquery\". Your results should have two columns:\n- `user_id` - contains the `owner_user_id` column from the `posts_answers` table\n- `number_of_answers` - contains the number of answers the user has written to \"bigquery\"-related questions"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Your code here\nbigquery_experts_query = \"\"\"\n                SELECT a.owner_user_id as user_id, count(1) as number_of_answers\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.tags LIKE '%bigquery%'\n                GROUP BY user_id\n                ORDER by number_of_answers DESC\n\n\"\"\"\n\n# Set up the query\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\nbigquery_experts_query_job = client.query(bigquery_experts_query) # Your code goes here\n\n# API request - run the query, and return a pandas DataFrame\nbigquery_experts_results = bigquery_experts_query_job.to_dataframe() # Your code goes here\n\n# Preview results\nprint(bigquery_experts_results.head())\n\n# Check your answer\nq_5.check()","execution_count":16,"outputs":[{"output_type":"stream","text":"     user_id  number_of_answers\n0  5221944.0               2338\n1  6253347.0                698\n2   132438.0                687\n3  1366527.0                617\n4   243782.0                450\n","name":"stdout"},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 82, \"questionId\": \"5_BigQueryExperts\", \"learnToolsVersion\": \"0.3.1\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"### 6) Building a more generally useful service\n\nHow could you convert what you've done to a general function a website could call on the backend to get experts on any topic?  \n\nThink about it and then check the solution below."},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"q_6.solution()","execution_count":17,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 4, \"learnTutorialId\": 82, \"questionId\": \"6_GeneralizeExpertFinder\", \"learnToolsVersion\": \"0.3.1\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Solution: \n```python\n\ndef expert_finder(topic, client):\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        client: A Client 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. Follows similar logic to bigquery_experts_results shown above.\n    '''\n    my_query = \"\"\"\n               SELECT a.owner_user_id AS user_id, COUNT(1) AS number_of_answers\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.tags like '%' + tag + '%'\n               GROUP BY a.owner_user_id\n               \"\"\"\n               \n    # Set up the query (a real service would have good error handling for \n    # queries that scan too much data)\n    safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)      \n    my_query_job = client.query(my_query, job_config=safe_config)\n    \n    # API request - run the query, and return a pandas DataFrame\n    results = my_query_job.to_dataframe()\n\n    return results\n\n```","text/markdown":"<span style=\"color:#33cc99\">Solution:</span> \n```python\n\ndef expert_finder(topic, client):\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        client: A Client 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. Follows similar logic to bigquery_experts_results shown above.\n    '''\n    my_query = \"\"\"\n               SELECT a.owner_user_id AS user_id, COUNT(1) AS number_of_answers\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.tags like '%' + tag + '%'\n               GROUP BY a.owner_user_id\n               \"\"\"\n               \n    # Set up the query (a real service would have good error handling for \n    # queries that scan too much data)\n    safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)      \n    my_query_job = client.query(my_query, job_config=safe_config)\n    \n    # API request - run the query, and return a pandas DataFrame\n    results = my_query_job.to_dataframe()\n\n    return results\n\n```"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"# Congratulations!\n\nYou know all the key components to use BigQuery and SQL effectively. Your SQL skills are sufficient to unlock many of the world's largest datasets.\n\nWant 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\nBring any questions or feedback to the [Learn Discussion Forum](https://www.kaggle.com/learn-forum)."},{"metadata":{},"cell_type":"markdown","source":"---\n**[SQL Home Page](https://www.kaggle.com/learn/intro-to-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":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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.5"}},"nbformat":4,"nbformat_minor":1}