SQL_and_BigQuery_day3.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\nQueries with **GROUP BY** can be powerful. There are many small things that can trip you up (like the order of the clauses), but it will start to feel natural once you've done it a few times. Here, you'll write queries using **GROUP BY** to answer questions from the Hacker News dataset.\n\nBefore you get started, run the following cell to set everything up:"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Set up feedback system\nfrom learntools.core import binder\nbinder.bind(globals())\nfrom learntools.sql.ex3 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":"The code cell below fetches the `comments` table from the `hacker_news` dataset.  We also preview the first five rows of the table."},{"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 \"hacker_news\" dataset\ndataset_ref = client.dataset(\"hacker_news\", project=\"bigquery-public-data\")\n\n# API request - fetch the dataset\ndataset = client.get_dataset(dataset_ref)\n\n# Construct a reference to the \"comments\" table\ntable_ref = dataset_ref.table(\"comments\")\n\n# API request - fetch the table\ntable = client.get_table(table_ref)\n\n# Preview the first five lines of the \"comments\" table\nclient.list_rows(table, max_results=5).to_dataframe()","execution_count":2,"outputs":[{"output_type":"stream","text":"Using Kaggle's public dataset BigQuery integration.\n","name":"stdout"},{"output_type":"execute_result","execution_count":2,"data":{"text/plain":"         id  by author        time   ...      parent deleted  dead ranking\n0   2701393  5l     5l  1309184881   ...     2701243    None  None       0\n1   5811403  99     99  1370234048   ...     5804452    None  None       0\n2     21623  AF     AF  1178992400   ...       21611    None  None       0\n3  10159727  EA     EA  1441206574   ...    10159396    None  None       0\n4   2988424  Iv     Iv  1315853580   ...     2988179    None  None       0\n\n[5 rows x 10 columns]","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>by</th>\n      <th>author</th>\n      <th>time</th>\n      <th>time_ts</th>\n      <th>text</th>\n      <th>parent</th>\n      <th>deleted</th>\n      <th>dead</th>\n      <th>ranking</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2701393</td>\n      <td>5l</td>\n      <td>5l</td>\n      <td>1309184881</td>\n      <td>2011-06-27 14:28:01+00:00</td>\n      <td>And the glazier who fixed all the broken windo...</td>\n      <td>2701243</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>5811403</td>\n      <td>99</td>\n      <td>99</td>\n      <td>1370234048</td>\n      <td>2013-06-03 04:34:08+00:00</td>\n      <td>Does canada have the equivalent of H1B/Green c...</td>\n      <td>5804452</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>21623</td>\n      <td>AF</td>\n      <td>AF</td>\n      <td>1178992400</td>\n      <td>2007-05-12 17:53:20+00:00</td>\n      <td>Speaking of Rails, there are other options in ...</td>\n      <td>21611</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10159727</td>\n      <td>EA</td>\n      <td>EA</td>\n      <td>1441206574</td>\n      <td>2015-09-02 15:09:34+00:00</td>\n      <td>Humans and large livestock (and maybe even pet...</td>\n      <td>10159396</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2988424</td>\n      <td>Iv</td>\n      <td>Iv</td>\n      <td>1315853580</td>\n      <td>2011-09-12 18:53:00+00:00</td>\n      <td>I must say I reacted in the same way when I re...</td>\n      <td>2988179</td>\n      <td>None</td>\n      <td>None</td>\n      <td>0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"},"metadata":{}}]},{"metadata":{},"cell_type":"markdown","source":"# Exercises\n\n### 1) Prolific commenters\n\nHacker News would like to send awards to everyone who has written more than 10,000 posts. Write a query that returns all authors with more than 10,000 posts as well as their post counts. Call the column with post counts `NumPosts`.\n\nIn case sample query is helpful, here is a query you saw in the tutorial to answer a similar question:\n```\nquery = \"\"\"\n        SELECT parent, COUNT(1) AS NumPosts\n        FROM `bigquery-public-data.hacker_news.comments`\n        GROUP BY parent\n        HAVING COUNT(1) > 10\n        \"\"\"\n```"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Query to select prolific commenters and post counts\nprolific_commenters_query = \"\"\"\n        SELECT author, COUNT(1) AS NumPosts\n        FROM `bigquery-public-data.hacker_news.comments`\n        GROUP BY author\n        HAVING COUNT(1) > 10000\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)\nquery_job = client.query(prolific_commenters_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nprolific_commenters = query_job.to_dataframe()\n\n# View top few rows of results\nprint(prolific_commenters.head())\n\n# Check your answer\nq_1.check()","execution_count":4,"outputs":[{"output_type":"stream","text":"         author  NumPosts\n0           eru     10448\n1       rbanffy     10557\n2  dragonwriter     10723\n3          None    227736\n4         DanBC     12902\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.5, \"interactionType\": 1, \"questionType\": 2, \"learnTutorialId\": 79, \"questionId\": \"1_ProlificCommenters\", \"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":false},"cell_type":"code","source":"#q_1.solution()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 2) Deleted comments\n\nHow many comments have been deleted? (If a comment was deleted, the `deleted` column in the comments table will have the value `True`.)"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Write your query here and figure out the answer\n\ncount_query = \"\"\"\n        SELECT COUNT(*) as count\n        FROM `bigquery-public-data.hacker_news.comments`\n        WHERE deleted = True\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)\nquery_job = client.query(count_query, job_config=safe_config)\n\n# API request - run the query, and return a pandas DataFrame\nquerydf = query_job.to_dataframe()\n\n\n","execution_count":7,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":16,"outputs":[{"output_type":"execute_result","execution_count":16,"data":{"text/plain":"227736"},"metadata":{}}]},{"metadata":{"trusted":true},"cell_type":"code","source":"num_deleted_posts = list(querydf['count'])[0] # Put your answer here\n\nq_2.check()","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\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.5, \"interactionType\": 1, \"questionType\": 1, \"learnTutorialId\": 79, \"questionId\": \"2_NumDeletedPosts\", \"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_2.solution()","execution_count":19,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Keep Going\n**[Click here](https://www.kaggle.com/dansbecker/order-by)** to move on and learn about the **ORDER BY** clause."},{"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}