Intro to SQL/6. Joining Data.ipynb

{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":337535,"sourceType":"datasetVersion","datasetId":22685}],"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**This notebook is an exercise in the [SQL](https://www.kaggle.com/learn/intro-to-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/dansbecker/joining-data).**\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":{}},{"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql.ex6 import *\nprint(\"Setup Complete\")","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:19:59.712138Z","iopub.execute_input":"2024-02-07T15:19:59.712666Z","iopub.status.idle":"2024-02-07T15:20:25.867180Z","shell.execute_reply.started":"2024-02-07T15:19:59.712622Z","shell.execute_reply":"2024-02-07T15:20:25.865779Z"},"trusted":true},"execution_count":1,"outputs":[{"name":"stdout","text":"Using Kaggle's public dataset BigQuery integration.\nSetup Complete\n","output_type":"stream"}]},{"cell_type":"markdown","source":"Run the next cell to fetch the `stackoverflow` dataset.","metadata":{}},{"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)","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:20:26.487259Z","iopub.execute_input":"2024-02-07T15:20:26.487625Z","iopub.status.idle":"2024-02-07T15:20:27.083719Z","shell.execute_reply.started":"2024-02-07T15:20:26.487592Z","shell.execute_reply":"2024-02-07T15:20:27.082447Z"},"trusted":true},"execution_count":3,"outputs":[{"name":"stdout","text":"Using Kaggle's public dataset BigQuery integration.\n","output_type":"stream"}]},{"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":{}},{"cell_type":"code","source":"# Get a list of available tables \ntables = list(client.list_tables(dataset))\nlist_of_tables = [table.table_id for table in tables] \n\n# Print your answer\nprint(list_of_tables)\n\n# Check your answer\nq_1.check()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:20:27.086171Z","iopub.execute_input":"2024-02-07T15:20:27.086589Z","iopub.status.idle":"2024-02-07T15:20:27.545618Z","shell.execute_reply.started":"2024-02-07T15:20:27.086554Z","shell.execute_reply":"2024-02-07T15:20:27.544552Z"},"trusted":true},"execution_count":4,"outputs":[{"name":"stdout","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","output_type":"stream"},{"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.16666666666666666, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"1_ListSOTables\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"markdown","source":"For the solution, uncomment the line below.","metadata":{}},{"cell_type":"code","source":"# q_1.solution()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"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":{}},{"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()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:20:33.467918Z","iopub.execute_input":"2024-02-07T15:20:33.468399Z","iopub.status.idle":"2024-02-07T15:20:34.640162Z","shell.execute_reply.started":"2024-02-07T15:20:33.468362Z","shell.execute_reply":"2024-02-07T15:20:34.639167Z"},"trusted":true},"execution_count":5,"outputs":[{"execution_count":5,"output_type":"execute_result","data":{"text/plain":"     id title                                               body  \\\n0    18  None  <p>For a table like this:</p>\\n\\n<pre><code>CR...   \n1   165  None  <p>You can use a <a href=\"http://sharpdevelop....   \n2  1028  None  <p>The VB code looks something like this:</p>\\...   \n3  1073  None  <p>My first choice would be a dedicated heap t...   \n4  1260  None  <p>I found the answer. all you have to do is a...   \n\n  accepted_answer_id answer_count  comment_count community_owned_date  \\\n0               None         None              2                  NaT   \n1               None         None              0                  NaT   \n2               None         None              0                  NaT   \n3               None         None              0                  NaT   \n4               None         None              0                  NaT   \n\n                     creation_date favorite_count  \\\n0 2008-08-01 05:12:44.193000+00:00           None   \n1 2008-08-01 18:04:25.023000+00:00           None   \n2 2008-08-04 04:58:40.300000+00:00           None   \n3 2008-08-04 07:51:02.997000+00:00           None   \n4 2008-08-04 14:06:02.863000+00:00           None   \n\n                last_activity_date                   last_edit_date  \\\n0 2016-06-02 05:56:26.060000+00:00 2016-06-02 05:56:26.060000+00:00   \n1 2019-04-06 14:03:51.080000+00:00 2019-04-06 14:03:51.080000+00:00   \n2 2013-02-07 13:22:14.680000+00:00 2013-02-07 13:22:14.680000+00:00   \n3 2015-09-01 17:32:32.120000+00:00 2015-09-01 17:32:32.120000+00:00   \n4 2016-12-20 08:38:48.867000+00:00 2016-12-20 08:38:48.867000+00:00   \n\n  last_editor_display_name  last_editor_user_id owner_display_name  \\\n0              Jeff Atwood               126039             phpguy   \n1                     None              1721793        user2189331   \n2                     None               395659        user2189331   \n3                     None                45459        user2189331   \n4                     None              1221571                Jin   \n\n   owner_user_id  parent_id  post_type_id  score  tags view_count  \n0            NaN         17             2     59  None       None  \n1            NaN        145             2     10  None       None  \n2            NaN        947             2      8  None       None  \n3            NaN       1069             2     29  None       None  \n4            NaN       1229             2      1  None       None  ","text/html":"<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>title</th>\n      <th>body</th>\n      <th>accepted_answer_id</th>\n      <th>answer_count</th>\n      <th>comment_count</th>\n      <th>community_owned_date</th>\n      <th>creation_date</th>\n      <th>favorite_count</th>\n      <th>last_activity_date</th>\n      <th>last_edit_date</th>\n      <th>last_editor_display_name</th>\n      <th>last_editor_user_id</th>\n      <th>owner_display_name</th>\n      <th>owner_user_id</th>\n      <th>parent_id</th>\n      <th>post_type_id</th>\n      <th>score</th>\n      <th>tags</th>\n      <th>view_count</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>18</td>\n      <td>None</td>\n      <td>&lt;p&gt;For a table like this:&lt;/p&gt;\\n\\n&lt;pre&gt;&lt;code&gt;CR...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>2</td>\n      <td>NaT</td>\n      <td>2008-08-01 05:12:44.193000+00:00</td>\n      <td>None</td>\n      <td>2016-06-02 05:56:26.060000+00:00</td>\n      <td>2016-06-02 05:56:26.060000+00:00</td>\n      <td>Jeff Atwood</td>\n      <td>126039</td>\n      <td>phpguy</td>\n      <td>NaN</td>\n      <td>17</td>\n      <td>2</td>\n      <td>59</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>165</td>\n      <td>None</td>\n      <td>&lt;p&gt;You can use a &lt;a href=\"http://sharpdevelop....</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-08-01 18:04:25.023000+00:00</td>\n      <td>None</td>\n      <td>2019-04-06 14:03:51.080000+00:00</td>\n      <td>2019-04-06 14:03:51.080000+00:00</td>\n      <td>None</td>\n      <td>1721793</td>\n      <td>user2189331</td>\n      <td>NaN</td>\n      <td>145</td>\n      <td>2</td>\n      <td>10</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>1028</td>\n      <td>None</td>\n      <td>&lt;p&gt;The VB code looks something like this:&lt;/p&gt;\\...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-08-04 04:58:40.300000+00:00</td>\n      <td>None</td>\n      <td>2013-02-07 13:22:14.680000+00:00</td>\n      <td>2013-02-07 13:22:14.680000+00:00</td>\n      <td>None</td>\n      <td>395659</td>\n      <td>user2189331</td>\n      <td>NaN</td>\n      <td>947</td>\n      <td>2</td>\n      <td>8</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1073</td>\n      <td>None</td>\n      <td>&lt;p&gt;My first choice would be a dedicated heap t...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-08-04 07:51:02.997000+00:00</td>\n      <td>None</td>\n      <td>2015-09-01 17:32:32.120000+00:00</td>\n      <td>2015-09-01 17:32:32.120000+00:00</td>\n      <td>None</td>\n      <td>45459</td>\n      <td>user2189331</td>\n      <td>NaN</td>\n      <td>1069</td>\n      <td>2</td>\n      <td>29</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>1260</td>\n      <td>None</td>\n      <td>&lt;p&gt;I found the answer. all you have to do is a...</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-08-04 14:06:02.863000+00:00</td>\n      <td>None</td>\n      <td>2016-12-20 08:38:48.867000+00:00</td>\n      <td>2016-12-20 08:38:48.867000+00:00</td>\n      <td>None</td>\n      <td>1221571</td>\n      <td>Jin</td>\n      <td>NaN</td>\n      <td>1229</td>\n      <td>2</td>\n      <td>1</td>\n      <td>None</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"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":{}},{"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()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:20:39.239243Z","iopub.execute_input":"2024-02-07T15:20:39.239686Z","iopub.status.idle":"2024-02-07T15:20:40.200505Z","shell.execute_reply.started":"2024-02-07T15:20:39.239651Z","shell.execute_reply":"2024-02-07T15:20:40.199044Z"},"trusted":true},"execution_count":6,"outputs":[{"execution_count":6,"output_type":"execute_result","data":{"text/plain":"       id                                              title  \\\n0  320268          Html.ActionLink doesn’t render # properly   \n1  324003                                Primitive recursion   \n2  390605                                 While vs. Do While   \n3  413246                        Protect ASP.NET Source code   \n4  454921  Difference between \"int[] myArray\" and \"int my...   \n\n                                                body  accepted_answer_id  \\\n0  <p>When using Html.ActionLink passing a string...                 NaN   \n1  <p>how  will i define the function 'simplify' ...                 NaN   \n2  <p>I've seen both the blocks of code in use se...            390608.0   \n3  <p>Im currently doing some research in how to ...                 NaN   \n4  <blockquote>\\n  <p><strong>Possible Duplicate:...            454928.0   \n\n   answer_count  comment_count community_owned_date  \\\n0             0              0                  NaT   \n1             0              0                  NaT   \n2             0              0                  NaT   \n3             0              0                  NaT   \n4             0              0                  NaT   \n\n                     creation_date  favorite_count  \\\n0 2008-11-26 10:42:37.477000+00:00               0   \n1 2008-11-27 15:12:37.497000+00:00               0   \n2 2008-12-24 01:49:54.230000+00:00               2   \n3 2009-01-05 14:23:51.040000+00:00               0   \n4 2009-01-18 10:22:52.177000+00:00               0   \n\n                last_activity_date                   last_edit_date  \\\n0 2009-02-06 20:13:54.370000+00:00                              NaT   \n1 2012-09-25 19:54:40.597000+00:00 2012-09-25 19:54:40.597000+00:00   \n2 2008-12-24 03:08:55.897000+00:00                              NaT   \n3 2009-03-24 21:30:22.370000+00:00 2009-01-05 14:42:28.257000+00:00   \n4 2009-01-18 10:30:50.930000+00:00 2017-05-23 11:49:26.567000+00:00   \n\n  last_editor_display_name  last_editor_user_id owner_display_name  \\\n0                     None                  NaN              Paulo   \n1                   Marcin               1288.0               None   \n2                     None                  NaN          Unkwntech   \n3             Tom Anderson              13502.0            Velnias   \n4                     None                 -1.0       Evan Fosmark   \n\n   owner_user_id parent_id  post_type_id  score  \\\n0            NaN      None             1      0   \n1        41000.0      None             1      0   \n2          115.0      None             1      0   \n3            NaN      None             1      0   \n4        49701.0      None             1      0   \n\n                                                tags  view_count  \n0                                        asp.net-mvc         390  \n1  haskell|lambda|functional-programming|lambda-c...         497  \n2                            language-agnostic|loops       11262  \n3                     asp.net|deployment|obfuscation        4823  \n4                                        java|arrays         798  ","text/html":"<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>id</th>\n      <th>title</th>\n      <th>body</th>\n      <th>accepted_answer_id</th>\n      <th>answer_count</th>\n      <th>comment_count</th>\n      <th>community_owned_date</th>\n      <th>creation_date</th>\n      <th>favorite_count</th>\n      <th>last_activity_date</th>\n      <th>last_edit_date</th>\n      <th>last_editor_display_name</th>\n      <th>last_editor_user_id</th>\n      <th>owner_display_name</th>\n      <th>owner_user_id</th>\n      <th>parent_id</th>\n      <th>post_type_id</th>\n      <th>score</th>\n      <th>tags</th>\n      <th>view_count</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>320268</td>\n      <td>Html.ActionLink doesn’t render # properly</td>\n      <td>&lt;p&gt;When using Html.ActionLink passing a string...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-11-26 10:42:37.477000+00:00</td>\n      <td>0</td>\n      <td>2009-02-06 20:13:54.370000+00:00</td>\n      <td>NaT</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>Paulo</td>\n      <td>NaN</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>asp.net-mvc</td>\n      <td>390</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>324003</td>\n      <td>Primitive recursion</td>\n      <td>&lt;p&gt;how  will i define the function 'simplify' ...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-11-27 15:12:37.497000+00:00</td>\n      <td>0</td>\n      <td>2012-09-25 19:54:40.597000+00:00</td>\n      <td>2012-09-25 19:54:40.597000+00:00</td>\n      <td>Marcin</td>\n      <td>1288.0</td>\n      <td>None</td>\n      <td>41000.0</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>haskell|lambda|functional-programming|lambda-c...</td>\n      <td>497</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>390605</td>\n      <td>While vs. Do While</td>\n      <td>&lt;p&gt;I've seen both the blocks of code in use se...</td>\n      <td>390608.0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2008-12-24 01:49:54.230000+00:00</td>\n      <td>2</td>\n      <td>2008-12-24 03:08:55.897000+00:00</td>\n      <td>NaT</td>\n      <td>None</td>\n      <td>NaN</td>\n      <td>Unkwntech</td>\n      <td>115.0</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>language-agnostic|loops</td>\n      <td>11262</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>413246</td>\n      <td>Protect ASP.NET Source code</td>\n      <td>&lt;p&gt;Im currently doing some research in how to ...</td>\n      <td>NaN</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2009-01-05 14:23:51.040000+00:00</td>\n      <td>0</td>\n      <td>2009-03-24 21:30:22.370000+00:00</td>\n      <td>2009-01-05 14:42:28.257000+00:00</td>\n      <td>Tom Anderson</td>\n      <td>13502.0</td>\n      <td>Velnias</td>\n      <td>NaN</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>asp.net|deployment|obfuscation</td>\n      <td>4823</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>454921</td>\n      <td>Difference between \"int[] myArray\" and \"int my...</td>\n      <td>&lt;blockquote&gt;\\n  &lt;p&gt;&lt;strong&gt;Possible Duplicate:...</td>\n      <td>454928.0</td>\n      <td>0</td>\n      <td>0</td>\n      <td>NaT</td>\n      <td>2009-01-18 10:22:52.177000+00:00</td>\n      <td>0</td>\n      <td>2009-01-18 10:30:50.930000+00:00</td>\n      <td>2017-05-23 11:49:26.567000+00:00</td>\n      <td>None</td>\n      <td>-1.0</td>\n      <td>Evan Fosmark</td>\n      <td>49701.0</td>\n      <td>None</td>\n      <td>1</td>\n      <td>0</td>\n      <td>java|arrays</td>\n      <td>798</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"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":{}},{"cell_type":"code","source":"# Check your answer (Run this code cell to receive credit!)\nq_2.solution()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:20:49.875676Z","iopub.execute_input":"2024-02-07T15:20:49.876065Z","iopub.status.idle":"2024-02-07T15:20:49.886862Z","shell.execute_reply.started":"2024-02-07T15:20:49.876034Z","shell.execute_reply":"2024-02-07T15:20:49.885511Z"},"trusted":true},"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, \"questionId\": \"2_HowToFindExperts\", \"learnToolsVersion\": \"0.3.4\", \"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":{}}]},{"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://storage.googleapis.com/kaggle-media/learn/images/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":{}},{"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)\n\n# API request - run the query, and return a pandas DataFrame\nquestions_results = questions_query_job.to_dataframe()\n\n# Preview results\nprint(questions_results.head())\n\n# Check your answer\nq_3.check()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:21:35.557511Z","iopub.execute_input":"2024-02-07T15:21:35.558006Z","iopub.status.idle":"2024-02-07T15:21:39.804121Z","shell.execute_reply.started":"2024-02-07T15:21:35.557965Z","shell.execute_reply":"2024-02-07T15:21:39.802932Z"},"trusted":true},"execution_count":9,"outputs":[{"name":"stdout","text":"         id                                              title  owner_user_id\n0  64345717  Loop by array and union looped result in BigQuery     13304769.0\n1  64610766  BigQuery Transfer jobs from S3 stuck pending o...     14549617.0\n2  64383871  How to get sum of values in days intervals usi...     12472644.0\n3  64251948                BigQuery get row above empty column      4572124.0\n4  64323398  SQL: Remove part of string that is in another ...      6089137.0\n","output_type":"stream"},{"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.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"3_SelectRightQuestions\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"markdown","source":"For a hint or the solution, uncomment the appropriate line below.","metadata":{}},{"cell_type":"code","source":"q_3.hint()\nq_3.solution()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:21:20.872867Z","iopub.execute_input":"2024-02-07T15:21:20.873363Z","iopub.status.idle":"2024-02-07T15:21:20.888475Z","shell.execute_reply.started":"2024-02-07T15:21:20.873326Z","shell.execute_reply":"2024-02-07T15:21:20.887368Z"},"trusted":true},"execution_count":8,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"3_SelectRightQuestions\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Hint: Your **WHERE** clause should be `WHERE tags LIKE '%bigquery%'`.","text/markdown":"<span style=\"color:#3366cc\">Hint:</span> Your **WHERE** clause should be `WHERE tags LIKE '%bigquery%'`."},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"3_SelectRightQuestions\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Solution: \n```python\n\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)\n\n# API request - run the query, and return a pandas DataFrame\nquestions_results = questions_query_job.to_dataframe()\n\n```","text/markdown":"<span style=\"color:#33cc99\">Solution:</span> \n```python\n\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)\n\n# API request - run the query, and return a pandas DataFrame\nquestions_results = questions_query_job.to_dataframe()\n\n```"},"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":{}},{"cell_type":"code","source":"# Your code here\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# Set up the query (cancel the query if it would use too much of \n# your quota, with the limit set to 27 GB)\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=27*10**10)\nanswers_query_job = client.query(answers_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nanswers_results = answers_query_job.to_dataframe()\n\n# Preview results\nprint(answers_results.head())\n\n# Check your answer\nq_4.check()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:21:49.360358Z","iopub.execute_input":"2024-02-07T15:21:49.360776Z","iopub.status.idle":"2024-02-07T15:22:03.640733Z","shell.execute_reply.started":"2024-02-07T15:21:49.360743Z","shell.execute_reply":"2024-02-07T15:22:03.639561Z"},"trusted":true},"execution_count":11,"outputs":[{"name":"stdout","text":"         id                                               body  owner_user_id\n0  57032546  <p>Another solution - same logic, just a diffe...      1391685.0\n1  57032790  <p>Recommendation : </p>\\n\\n<pre><code>select\\...     10369599.0\n2  57036857  <p>Basically, this is the answer</p>\\n\\n<pre><...     11506172.0\n3  57042242  <p>first, I hope that year+week &amp; year+day...      2929192.0\n4  57049923  <p>I think the solutions mentioned by others s...      1391685.0\n","output_type":"stream"},{"name":"stderr","text":"/opt/conda/lib/python3.10/site-packages/learntools/sql/ex6.py:129: FutureWarning: Calling int on a single element Series is deprecated and will raise a TypeError in the future. Use int(ser.iloc[0]) instead\n  correct_owner_user_id = int(answers_answer[answers_answer[\"id\"] == first_id][\"owner_user_id\"])\n/opt/conda/lib/python3.10/site-packages/learntools/sql/ex6.py:130: FutureWarning: Calling int on a single element Series is deprecated and will raise a TypeError in the future. Use int(ser.iloc[0]) instead\n  submitted_owner_user_id = int(results[results[\"id\"] == first_id][\"owner_user_id\"])\n","output_type":"stream"},{"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.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"4_FirstJoin\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"markdown","source":"For a hint or the solution, uncomment the appropriate line below.","metadata":{}},{"cell_type":"code","source":"q_4.hint()\nq_4.solution()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:21:41.638826Z","iopub.execute_input":"2024-02-07T15:21:41.639331Z","iopub.status.idle":"2024-02-07T15:21:41.651217Z","shell.execute_reply.started":"2024-02-07T15:21:41.639292Z","shell.execute_reply":"2024-02-07T15:21:41.650357Z"},"trusted":true},"execution_count":10,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"4_FirstJoin\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Hint: \nDo an **INNER JOIN** between `bigquery-public-data.stackoverflow.posts_questions` and `bigquery-public-data.stackoverflow.posts_answers`.\n\nGive `post_questions` an alias of `q`, and use `a` as an alias for `posts_answers`. The **ON** part of your join is `q.id = a.parent_id`.","text/markdown":"<span style=\"color:#3366cc\">Hint:</span> \nDo an **INNER JOIN** between `bigquery-public-data.stackoverflow.posts_questions` and `bigquery-public-data.stackoverflow.posts_answers`.\n\nGive `post_questions` an alias of `q`, and use `a` as an alias for `posts_answers`. The **ON** part of your join is `q.id = a.parent_id`.\n"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"4_FirstJoin\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Solution: \n```python\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# Set up the query (cancel the query if it would use too much of \n# your quota, with the limit set to 27 GB)\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=27*10**10)\nanswers_query_job = client.query(answers_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nanswers_results = answers_query_job.to_dataframe()\n\n```","text/markdown":"<span style=\"color:#33cc99\">Solution:</span> \n```python\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# Set up the query (cancel the query if it would use too much of \n# your quota, with the limit set to 27 GB)\nsafe_config = bigquery.QueryJobConfig(maximum_bytes_billed=27*10**10)\nanswers_query_job = client.query(answers_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nanswers_results = answers_query_job.to_dataframe()\n\n```"},"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":{}},{"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 a.owner_user_id\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)\nbigquery_experts_query_job = client.query(bigquery_experts_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nbigquery_experts_results = bigquery_experts_query_job.to_dataframe()\n\n# Preview results\nprint(bigquery_experts_results.head())\n\n# Check your answer\nq_5.check()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:22:21.815666Z","iopub.execute_input":"2024-02-07T15:22:21.816087Z","iopub.status.idle":"2024-02-07T15:22:23.714335Z","shell.execute_reply.started":"2024-02-07T15:22:21.816056Z","shell.execute_reply":"2024-02-07T15:22:23.712989Z"},"trusted":true},"execution_count":14,"outputs":[{"name":"stdout","text":"      user_id  number_of_answers\n0   4650934.0                  1\n1   2157547.0                  1\n2   3011380.0                  1\n3   9447598.0                  8\n4  15745884.0                 24\n","output_type":"stream"},{"name":"stderr","text":"/opt/conda/lib/python3.10/site-packages/learntools/sql/ex6.py:199: FutureWarning: Calling int on a single element Series is deprecated and will raise a TypeError in the future. Use int(ser.iloc[0]) instead\n  correct_num = int(bigquery_experts_answer[bigquery_experts_answer[\"user_id\"] == first_id][\"number_of_answers\"])\n/opt/conda/lib/python3.10/site-packages/learntools/sql/ex6.py:200: FutureWarning: Calling int on a single element Series is deprecated and will raise a TypeError in the future. Use int(ser.iloc[0]) instead\n  submitted_num = int(results[results[\"user_id\"] == first_id][\"number_of_answers\"])\n","output_type":"stream"},{"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.16666666666666666, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"5_BigQueryExperts\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Correct","text/markdown":"<span style=\"color:#33cc33\">Correct</span>"},"metadata":{}}]},{"cell_type":"markdown","source":"For a hint or the solution, uncomment the appropriate line below.","metadata":{}},{"cell_type":"code","source":"q_5.hint()\nq_5.solution()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:22:03.643119Z","iopub.execute_input":"2024-02-07T15:22:03.643876Z","iopub.status.idle":"2024-02-07T15:22:03.658240Z","shell.execute_reply.started":"2024-02-07T15:22:03.643832Z","shell.execute_reply":"2024-02-07T15:22:03.656932Z"},"trusted":true},"execution_count":12,"outputs":[{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 2, \"questionType\": 2, \"questionId\": \"5_BigQueryExperts\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Hint: Start with `SELECT a.owner_user_id AS user_id, COUNT(1) AS number_of_answers`","text/markdown":"<span style=\"color:#3366cc\">Hint:</span> Start with `SELECT a.owner_user_id AS user_id, COUNT(1) AS number_of_answers`"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"<IPython.core.display.Javascript object>","application/javascript":"parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"interactionType\": 3, \"questionType\": 2, \"questionId\": \"5_BigQueryExperts\", \"learnToolsVersion\": \"0.3.4\", \"valueTowardsCompletion\": 0.0, \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\", \"outcomeType\": 4}}, \"*\")"},"metadata":{}},{"output_type":"display_data","data":{"text/plain":"Solution: \n```python\n\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 a.owner_user_id\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)\nbigquery_experts_query_job = client.query(bigquery_experts_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nbigquery_experts_results = bigquery_experts_query_job.to_dataframe()\n\n```","text/markdown":"<span style=\"color:#33cc99\">Solution:</span> \n```python\n\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 a.owner_user_id\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)\nbigquery_experts_query_job = client.query(bigquery_experts_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nbigquery_experts_results = bigquery_experts_query_job.to_dataframe()\n\n```"},"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":{}},{"cell_type":"code","source":"# Check your answer (Run this code cell to receive credit!)\nq_6.solution()","metadata":{"execution":{"iopub.status.busy":"2024-02-07T15:22:25.377891Z","iopub.execute_input":"2024-02-07T15:22:25.378376Z","iopub.status.idle":"2024-02-07T15:22:25.389348Z","shell.execute_reply.started":"2024-02-07T15:22:25.378339Z","shell.execute_reply":"2024-02-07T15:22:25.388067Z"},"trusted":true},"execution_count":15,"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, \"questionId\": \"6_GeneralizeExpertFinder\", \"learnToolsVersion\": \"0.3.4\", \"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 '%{topic}%'\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 '%{topic}%'\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":{}}]},{"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?fileType=bigQuery).","metadata":{}},{"cell_type":"markdown","source":"---\n\n\n\n\n*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/intro-to-sql/discussion) to chat with other learners.*","metadata":{}}]}