exercise-nested-and-repeated-data.ipynb

{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.10.10","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"**This notebook is an exercise in the [Advanced SQL](https://www.kaggle.com/learn/advanced-sql) course.  You can reference the tutorial at [this link](https://www.kaggle.com/alexisbcook/nested-and-repeated-data).**\n\n---\n","metadata":{}},{"cell_type":"markdown","source":"# Introduction\n\nNow that you know how to query nested and repeated data, you're ready to draw interesting insights from the [GitHub Repos](https://www.kaggle.com/github/github-repos) dataset.  \n\nBefore you get started, run the following cell to set everything up.","metadata":{}},{"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql_advanced.ex3 import *\nprint(\"Setup Complete\")","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:33:25.784812Z","iopub.execute_input":"2023-06-07T20:33:25.785842Z","iopub.status.idle":"2023-06-07T20:33:29.362319Z","shell.execute_reply.started":"2023-06-07T20:33:25.785802Z","shell.execute_reply":"2023-06-07T20:33:29.361171Z"},"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":"# Exercises\n\n### 1) Who had the most commits in 2016?\n\nGitHub is the most popular place to collaborate on software projects. A GitHub **repository** (or repo) is a collection of files associated with a specific project, and a GitHub **commit** is a change that a user has made to a repository.  We refer to the user as a **committer**.\n\nThe `sample_commits` table contains a small sample of GitHub commits, where each row corresponds to different commit.  The code cell below fetches the table and shows the first five rows of this table.","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 \"github_repos\" dataset\ndataset_ref = client.dataset(\"github_repos\", project=\"bigquery-public-data\")\n\n# API request - fetch the dataset\ndataset = client.get_dataset(dataset_ref)\n\n# Construct a reference to the \"sample_commits\" table\ntable_ref = dataset_ref.table(\"sample_commits\")\n\n# API request - fetch the table\nsample_commits_table = client.get_table(table_ref)\n\n# Preview the first five lines of the table\nclient.list_rows(sample_commits_table, max_results=5).to_dataframe()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:34:27.872803Z","iopub.execute_input":"2023-06-07T20:34:27.873330Z","iopub.status.idle":"2023-06-07T20:34:29.157284Z","shell.execute_reply.started":"2023-06-07T20:34:27.873287Z","shell.execute_reply":"2023-06-07T20:34:29.156495Z"},"trusted":true},"execution_count":2,"outputs":[{"name":"stdout","text":"Using Kaggle's public dataset BigQuery integration.\n","output_type":"stream"},{"execution_count":2,"output_type":"execute_result","data":{"text/plain":"                                     commit  \\\n0  afdba32e2a9ea729a9f9f280dbf6c718773c7ded   \n1  eb846d9f147455e4e5e1863bfb5e31974bb69b7c   \n2  f8798ccbefc0e4ef7438c080b7ba0410738c8cfa   \n3  b83ae6d421435c6204150300f1c25bfbd39cd62b   \n4  aaabee8b7686dfe49f10289cb4b7a817b99e5dd9   \n\n                                       tree  \\\n0  d77cca8a096e5320f3194d4a6ca1b4fef2dc9b99   \n1  443efbb146c7824508be817923bab04c2185810e   \n2  9133440693c02314f1f6f95629b3594ce24ad0f8   \n3  99c6b661ab7de05c2fd49aa62624d2d6bf8abc69   \n4  7ccc6cf829a93d46daf484164a5466c91eca2efa   \n\n                                              parent  \\\n0         [d65e55d4999b394e37ffe12543ecd2a17b7c44fc]   \n1         [3af6b35261182ff185db1f0fd271254147e2663e]   \n2         [261e767628bb5971b9032439818237cc8511ea94]   \n3         [de1414a654e66b81b5348dbc5259ecf2fb61655e]   \n4  [795e9364215dc98b1dea888ebae22383ecbbb92a, 2f2...   \n\n                                              author  \\\n0  {'name': 'Jason Gunthorpe', 'email': 'a99b91d7...   \n1  {'name': 'Hannes Reinecke', 'email': 'b0d1e9e4...   \n2  {'name': 'Yong Zhang', 'email': '34add0fe16a1f...   \n3  {'name': 'Christoph Hellwig', 'email': '923f77...   \n4  {'name': 'Luciano Coelho', 'email': 'd1ef58086...   \n\n                                           committer  \\\n0  {'name': 'Peter Huewe', 'email': '014f16385c5a...   \n1  {'name': 'Christoph Hellwig', 'email': '923f77...   \n2  {'name': 'Florian Tobias Schandinat', 'email':...   \n3  {'name': 'Jens Axboe', 'email': 'cd8c6775e60d6...   \n4  {'name': 'Luciano Coelho', 'email': 'd1ef58086...   \n\n                                             subject  \\\n0  tpm: Pull everything related to /dev/tpmX into...   \n1  scsi: rename SERVICE_ACTION_IN to SERVICE_ACTI...   \n2                   video: irq: Remove IRQF_DISABLED   \n3               fs: remove mapping->backing_dev_info   \n4       Merge branch 'wl12xx-next' into for-linville   \n\n                                             message  \\\n0  tpm: Pull everything related to /dev/tpmX into...   \n1  scsi: rename SERVICE_ACTION_IN to SERVICE_ACTI...   \n2  video: irq: Remove IRQF_DISABLED\\n\\nSince comm...   \n3  fs: remove mapping->backing_dev_info\\n\\nNow th...   \n4  Merge branch 'wl12xx-next' into for-linville\\n...   \n\n                                             trailer  \\\n0  [{'key': 'Signed-off-by', 'value': 'Jason Gunt...   \n1  [{'key': 'Signed-off-by', 'value': 'Hannes Rei...   \n2  [{'key': 'Signed-off-by', 'value': 'Yong Zhang...   \n3  [{'key': 'Signed-off-by', 'value': 'Christoph ...   \n4  [{'key': 'Conflicts', 'value': '', 'email': No...   \n\n                                          difference difference_truncated  \\\n0  [{'old_mode': 33188.0, 'new_mode': 33188, 'old...                 None   \n1  [{'old_mode': 33188.0, 'new_mode': 33188, 'old...                 None   \n2  [{'old_mode': 33188.0, 'new_mode': 33188, 'old...                 None   \n3  [{'old_mode': 33188.0, 'new_mode': 33188, 'old...                 None   \n4  [{'old_mode': 33188.0, 'new_mode': 33188, 'old...                 None   \n\n        repo_name encoding  \n0  torvalds/linux     None  \n1  torvalds/linux     None  \n2  torvalds/linux     None  \n3  torvalds/linux     None  \n4  torvalds/linux     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>commit</th>\n      <th>tree</th>\n      <th>parent</th>\n      <th>author</th>\n      <th>committer</th>\n      <th>subject</th>\n      <th>message</th>\n      <th>trailer</th>\n      <th>difference</th>\n      <th>difference_truncated</th>\n      <th>repo_name</th>\n      <th>encoding</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>afdba32e2a9ea729a9f9f280dbf6c718773c7ded</td>\n      <td>d77cca8a096e5320f3194d4a6ca1b4fef2dc9b99</td>\n      <td>[d65e55d4999b394e37ffe12543ecd2a17b7c44fc]</td>\n      <td>{'name': 'Jason Gunthorpe', 'email': 'a99b91d7...</td>\n      <td>{'name': 'Peter Huewe', 'email': '014f16385c5a...</td>\n      <td>tpm: Pull everything related to /dev/tpmX into...</td>\n      <td>tpm: Pull everything related to /dev/tpmX into...</td>\n      <td>[{'key': 'Signed-off-by', 'value': 'Jason Gunt...</td>\n      <td>[{'old_mode': 33188.0, 'new_mode': 33188, 'old...</td>\n      <td>None</td>\n      <td>torvalds/linux</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>eb846d9f147455e4e5e1863bfb5e31974bb69b7c</td>\n      <td>443efbb146c7824508be817923bab04c2185810e</td>\n      <td>[3af6b35261182ff185db1f0fd271254147e2663e]</td>\n      <td>{'name': 'Hannes Reinecke', 'email': 'b0d1e9e4...</td>\n      <td>{'name': 'Christoph Hellwig', 'email': '923f77...</td>\n      <td>scsi: rename SERVICE_ACTION_IN to SERVICE_ACTI...</td>\n      <td>scsi: rename SERVICE_ACTION_IN to SERVICE_ACTI...</td>\n      <td>[{'key': 'Signed-off-by', 'value': 'Hannes Rei...</td>\n      <td>[{'old_mode': 33188.0, 'new_mode': 33188, 'old...</td>\n      <td>None</td>\n      <td>torvalds/linux</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>f8798ccbefc0e4ef7438c080b7ba0410738c8cfa</td>\n      <td>9133440693c02314f1f6f95629b3594ce24ad0f8</td>\n      <td>[261e767628bb5971b9032439818237cc8511ea94]</td>\n      <td>{'name': 'Yong Zhang', 'email': '34add0fe16a1f...</td>\n      <td>{'name': 'Florian Tobias Schandinat', 'email':...</td>\n      <td>video: irq: Remove IRQF_DISABLED</td>\n      <td>video: irq: Remove IRQF_DISABLED\\n\\nSince comm...</td>\n      <td>[{'key': 'Signed-off-by', 'value': 'Yong Zhang...</td>\n      <td>[{'old_mode': 33188.0, 'new_mode': 33188, 'old...</td>\n      <td>None</td>\n      <td>torvalds/linux</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>b83ae6d421435c6204150300f1c25bfbd39cd62b</td>\n      <td>99c6b661ab7de05c2fd49aa62624d2d6bf8abc69</td>\n      <td>[de1414a654e66b81b5348dbc5259ecf2fb61655e]</td>\n      <td>{'name': 'Christoph Hellwig', 'email': '923f77...</td>\n      <td>{'name': 'Jens Axboe', 'email': 'cd8c6775e60d6...</td>\n      <td>fs: remove mapping-&gt;backing_dev_info</td>\n      <td>fs: remove mapping-&gt;backing_dev_info\\n\\nNow th...</td>\n      <td>[{'key': 'Signed-off-by', 'value': 'Christoph ...</td>\n      <td>[{'old_mode': 33188.0, 'new_mode': 33188, 'old...</td>\n      <td>None</td>\n      <td>torvalds/linux</td>\n      <td>None</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>aaabee8b7686dfe49f10289cb4b7a817b99e5dd9</td>\n      <td>7ccc6cf829a93d46daf484164a5466c91eca2efa</td>\n      <td>[795e9364215dc98b1dea888ebae22383ecbbb92a, 2f2...</td>\n      <td>{'name': 'Luciano Coelho', 'email': 'd1ef58086...</td>\n      <td>{'name': 'Luciano Coelho', 'email': 'd1ef58086...</td>\n      <td>Merge branch 'wl12xx-next' into for-linville</td>\n      <td>Merge branch 'wl12xx-next' into for-linville\\n...</td>\n      <td>[{'key': 'Conflicts', 'value': '', 'email': No...</td>\n      <td>[{'old_mode': 33188.0, 'new_mode': 33188, 'old...</td>\n      <td>None</td>\n      <td>torvalds/linux</td>\n      <td>None</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"Run the next code cell to print the table schema. ","metadata":{}},{"cell_type":"code","source":"# Print information on all the columns in the table\nsample_commits_table.schema","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:34:32.650162Z","iopub.execute_input":"2023-06-07T20:34:32.650720Z","iopub.status.idle":"2023-06-07T20:34:32.661100Z","shell.execute_reply.started":"2023-06-07T20:34:32.650676Z","shell.execute_reply":"2023-06-07T20:34:32.659792Z"},"trusted":true},"execution_count":3,"outputs":[{"execution_count":3,"output_type":"execute_result","data":{"text/plain":"[SchemaField('commit', 'STRING', 'NULLABLE', None, (), None),\n SchemaField('tree', 'STRING', 'NULLABLE', None, (), None),\n SchemaField('parent', 'STRING', 'REPEATED', None, (), None),\n SchemaField('author', 'RECORD', 'NULLABLE', None, (SchemaField('name', 'STRING', 'NULLABLE', None, (), None), SchemaField('email', 'STRING', 'NULLABLE', None, (), None), SchemaField('time_sec', 'INTEGER', 'NULLABLE', None, (), None), SchemaField('tz_offset', 'INTEGER', 'NULLABLE', None, (), None), SchemaField('date', 'TIMESTAMP', 'NULLABLE', None, (), None)), None),\n SchemaField('committer', 'RECORD', 'NULLABLE', None, (SchemaField('name', 'STRING', 'NULLABLE', None, (), None), SchemaField('email', 'STRING', 'NULLABLE', None, (), None), SchemaField('time_sec', 'INTEGER', 'NULLABLE', None, (), None), SchemaField('tz_offset', 'INTEGER', 'NULLABLE', None, (), None), SchemaField('date', 'TIMESTAMP', 'NULLABLE', None, (), None)), None),\n SchemaField('subject', 'STRING', 'NULLABLE', None, (), None),\n SchemaField('message', 'STRING', 'NULLABLE', None, (), None),\n SchemaField('trailer', 'RECORD', 'REPEATED', None, (SchemaField('key', 'STRING', 'NULLABLE', None, (), None), SchemaField('value', 'STRING', 'NULLABLE', None, (), None), SchemaField('email', 'STRING', 'NULLABLE', None, (), None)), None),\n SchemaField('difference', 'RECORD', 'REPEATED', None, (SchemaField('old_mode', 'INTEGER', 'NULLABLE', None, (), None), SchemaField('new_mode', 'INTEGER', 'NULLABLE', None, (), None), SchemaField('old_path', 'STRING', 'NULLABLE', None, (), None), SchemaField('new_path', 'STRING', 'NULLABLE', None, (), None), SchemaField('old_sha1', 'STRING', 'NULLABLE', None, (), None), SchemaField('new_sha1', 'STRING', 'NULLABLE', None, (), None), SchemaField('old_repo', 'STRING', 'NULLABLE', None, (), None), SchemaField('new_repo', 'STRING', 'NULLABLE', None, (), None)), None),\n SchemaField('difference_truncated', 'BOOLEAN', 'NULLABLE', None, (), None),\n SchemaField('repo_name', 'STRING', 'NULLABLE', None, (), None),\n SchemaField('encoding', 'STRING', 'NULLABLE', None, (), None)]"},"metadata":{}}]},{"cell_type":"markdown","source":"Write a query to find the individuals with the most commits in this table in 2016.  Your query should return a table with two columns:\n- `committer_name` - contains the name of each individual with a commit (from 2016) in the table\n- `num_commits` - shows the number of commits the individual has in the table (from 2016)\n\nSort the table, so that people with more commits appear first.\n\n**NOTE**: You can find the name of each committer and the date of the commit under the \"committer\" column, in the \"name\" and \"date\" child fields, respectively.","metadata":{}},{"cell_type":"code","source":"# Write a query to find the answer\nmax_commits_query = \"\"\"\n                    SELECT committer.name AS committer_name, COUNT(*) AS num_commits\n                    FROM `bigquery-public-data.github_repos.sample_commits`\n                    WHERE committer.date >= '2016-01-01' AND committer.date < '2017-01-01'\n                    GROUP BY committer_name\n                    ORDER BY num_commits DESC\n                    \"\"\"\n\n# Check your answer\nq_1.check()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:34:50.748122Z","iopub.execute_input":"2023-06-07T20:34:50.748577Z","iopub.status.idle":"2023-06-07T20:34:52.117398Z","shell.execute_reply.started":"2023-06-07T20:34:50.748539Z","shell.execute_reply":"2023-06-07T20:34:52.116516Z"},"trusted":true},"execution_count":4,"outputs":[{"output_type":"display_data","data":{"text/plain":"          committer_name  num_commits\n0     Greg Kroah-Hartman         3545\n1        David S. Miller         3120\n2  TensorFlower Gardener         2449\n3         Linus Torvalds         2424\n4        Benjamin Pasero         1127","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>committer_name</th>\n      <th>num_commits</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Greg Kroah-Hartman</td>\n      <td>3545</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>David S. Miller</td>\n      <td>3120</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>TensorFlower Gardener</td>\n      <td>2449</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Linus Torvalds</td>\n      <td>2424</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Benjamin Pasero</td>\n      <td>1127</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}},{"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, \"questionId\": \"1_MaxCommits\", \"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":"code","source":"# Lines below will give you a hint or solution code\n#q_1.hint()\n#q_1.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 2) Look at languages!\n\nNow you will work with the `languages` table.  Run the code cell below to print the first few rows.","metadata":{}},{"cell_type":"code","source":"# Construct a reference to the \"languages\" table\ntable_ref = dataset_ref.table(\"languages\")\n\n# API request - fetch the table\nlanguages_table = client.get_table(table_ref)\n\n# Preview the first five lines of the table\nclient.list_rows(languages_table, max_results=5).to_dataframe()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:34:56.138688Z","iopub.execute_input":"2023-06-07T20:34:56.139054Z","iopub.status.idle":"2023-06-07T20:34:57.055169Z","shell.execute_reply.started":"2023-06-07T20:34:56.139012Z","shell.execute_reply":"2023-06-07T20:34:57.053814Z"},"trusted":true},"execution_count":5,"outputs":[{"execution_count":5,"output_type":"execute_result","data":{"text/plain":"             repo_name                         language\n0    lemi136/puntovent     [{'name': 'C', 'bytes': 80}]\n1       taxigps/nctool   [{'name': 'C', 'bytes': 4461}]\n2          ahy1/strbuf   [{'name': 'C', 'bytes': 5573}]\n3  nleiten/mod_rpaf-ng  [{'name': 'C', 'bytes': 30330}]\n4  kmcallister/alameda  [{'name': 'C', 'bytes': 17077}]","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>repo_name</th>\n      <th>language</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>lemi136/puntovent</td>\n      <td>[{'name': 'C', 'bytes': 80}]</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>taxigps/nctool</td>\n      <td>[{'name': 'C', 'bytes': 4461}]</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>ahy1/strbuf</td>\n      <td>[{'name': 'C', 'bytes': 5573}]</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>nleiten/mod_rpaf-ng</td>\n      <td>[{'name': 'C', 'bytes': 30330}]</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>kmcallister/alameda</td>\n      <td>[{'name': 'C', 'bytes': 17077}]</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"cell_type":"markdown","source":"Each row of the `languages` table corresponds to a different repository.  \n- The \"repo_name\" column contains the name of the repository,\n- the \"name\" field in the \"language\" column contains the programming languages that can be found in the repo, and \n- the \"bytes\" field in the \"language\" column has the size of the files (in bytes, for the corresponding language).\n\nRun the following code cell to print the table schema.","metadata":{}},{"cell_type":"code","source":"# Print information on all the columns in the table\nlanguages_table.schema","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:35:01.259241Z","iopub.execute_input":"2023-06-07T20:35:01.259638Z","iopub.status.idle":"2023-06-07T20:35:01.267122Z","shell.execute_reply.started":"2023-06-07T20:35:01.259608Z","shell.execute_reply":"2023-06-07T20:35:01.265930Z"},"trusted":true},"execution_count":6,"outputs":[{"execution_count":6,"output_type":"execute_result","data":{"text/plain":"[SchemaField('repo_name', 'STRING', 'NULLABLE', None, (), None),\n SchemaField('language', 'RECORD', 'REPEATED', None, (SchemaField('name', 'STRING', 'NULLABLE', None, (), None), SchemaField('bytes', 'INTEGER', 'NULLABLE', None, (), None)), None)]"},"metadata":{}}]},{"cell_type":"markdown","source":"Assume for the moment that you have access to a table called `sample_languages` that contains only a very small subset of the rows from the `languages` table: in fact, it contains only three rows!  This table is depicted in the image below.\n\n![](https://storage.googleapis.com/kaggle-media/learn/images/qAb5lZ2.png)\n\nHow many rows are in the table returned by the query below?\n\n![](https://storage.googleapis.com/kaggle-media/learn/images/Q5qYAtz.png)\n\nFill in your answer in the next code cell.","metadata":{}},{"cell_type":"code","source":"# Fill in the blank\nnum_rows = 6\n\n# Check your answer\nq_2.check()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:35:22.789012Z","iopub.execute_input":"2023-06-07T20:35:22.789602Z","iopub.status.idle":"2023-06-07T20:35:22.801189Z","shell.execute_reply.started":"2023-06-07T20:35:22.789555Z","shell.execute_reply":"2023-06-07T20:35:22.799659Z"},"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\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.25, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"2_LookLang\", \"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":"code","source":"# Lines below will give you a hint or solution code\n#q_2.hint()\n#q_2.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 3) What's the most popular programming language?\n\nWrite a query to leverage the information in the `languages` table to determine which programming languages appear in the most repositories.  The table returned by your query should have two columns:\n- `language_name` - the name of the programming language\n- `num_repos` - the number of repositories in the `languages` table that use the programming language\n\nSort the table so that languages that appear in more repos are shown first.","metadata":{}},{"cell_type":"code","source":"# Write a query to find the answer\npop_lang_query = \"\"\"\n                 SELECT l.name as language_name, COUNT(*) as num_repos\n                 FROM `bigquery-public-data.github_repos.languages`,\n                     UNNEST(language) AS l\n                 GROUP BY language_name\n                 ORDER BY num_repos DESC\n                 \"\"\"\n\n# Check your answer\nq_3.check()","metadata":{"execution":{"iopub.status.busy":"2023-06-07T20:35:47.310072Z","iopub.execute_input":"2023-06-07T20:35:47.310536Z","iopub.status.idle":"2023-06-07T20:35:48.447891Z","shell.execute_reply.started":"2023-06-07T20:35:47.310504Z","shell.execute_reply":"2023-06-07T20:35:48.446250Z"},"trusted":true},"execution_count":8,"outputs":[{"output_type":"display_data","data":{"text/plain":"  language_name  num_repos\n0    JavaScript    1099966\n1           CSS     807826\n2          HTML     777433\n3         Shell     640886\n4        Python     550905","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>language_name</th>\n      <th>num_repos</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>JavaScript</td>\n      <td>1099966</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>CSS</td>\n      <td>807826</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>HTML</td>\n      <td>777433</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Shell</td>\n      <td>640886</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Python</td>\n      <td>550905</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}},{"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, \"questionId\": \"3_PopLang\", \"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":"code","source":"# Lines below will give you a hint or solution code\n#q_3.hint()\n#q_3.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### 4) Which languages are used in the repository with the most languages?\n\nFor this question, you'll restrict your attention to the repository with name `'polyrabbit/polyglot'`.\n\nWrite a query that returns a table with one row for each language in this repository.  The table should have two columns:\n- `name` - the name of the programming language\n- `bytes` - the total number of bytes of that programming language\n\nSort the table by the `bytes` column so that programming languages that take up more space in the repo appear first.","metadata":{}},{"cell_type":"code","source":"# Your code here\nall_langs_query = \"\"\"\n                  \"\"\"\n\n# Check your answer\nq_4.check()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Lines below will give you a hint or solution code\n#q_4.hint()\n#q_4.solution()","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Keep going\n\nLearn how to make your queries **[more efficient](https://www.kaggle.com/alexisbcook/writing-efficient-queries)**.","metadata":{}},{"cell_type":"markdown","source":"---\n\n\n\n\n*Have questions or comments? Visit the [course discussion forum](https://www.kaggle.com/learn/advanced-sql/discussion) to chat with other learners.*","metadata":{}}]}