intro_to_sql/03-group-by-having-count.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**[SQL Home Page](https://www.kaggle.com/learn/intro-to-sql)**\n",
    "\n",
    "---\n",
    "\n",
    "*This exercise involves you writing code, and we check it automatically to tell you if it's right. We're having a temporary problem with out checking infrastructure, causing a bar that says `None` in some cases when you have the right answer. We're sorry. We're fixing it. In the meantime, if you see a bar saying `None` that means you've done something good.*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction\n",
    "\n",
    "Queries 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",
    "\n",
    "Before you get started, run the following cell to set everything up:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n",
      "Setup Complete\n"
     ]
    }
   ],
   "source": [
    "# Set up feedback system\n",
    "from learntools.core import binder\n",
    "binder.bind(globals())\n",
    "from learntools.sql.ex3 import *\n",
    "print(\"Setup Complete\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code cell below fetches the `comments` table from the `hacker_news` dataset.  We also preview the first five rows of the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using Kaggle's public dataset BigQuery integration.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>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>"
      ],
      "text/plain": [
       "         id  by author        time                   time_ts  \\\n",
       "0   2701393  5l     5l  1309184881 2011-06-27 14:28:01+00:00   \n",
       "1   5811403  99     99  1370234048 2013-06-03 04:34:08+00:00   \n",
       "2     21623  AF     AF  1178992400 2007-05-12 17:53:20+00:00   \n",
       "3  10159727  EA     EA  1441206574 2015-09-02 15:09:34+00:00   \n",
       "4   2988424  Iv     Iv  1315853580 2011-09-12 18:53:00+00:00   \n",
       "\n",
       "                                                text    parent deleted  dead  \\\n",
       "0  And the glazier who fixed all the broken windo...   2701243    None  None   \n",
       "1  Does canada have the equivalent of H1B/Green c...   5804452    None  None   \n",
       "2  Speaking of Rails, there are other options in ...     21611    None  None   \n",
       "3  Humans and large livestock (and maybe even pet...  10159396    None  None   \n",
       "4  I must say I reacted in the same way when I re...   2988179    None  None   \n",
       "\n",
       "   ranking  \n",
       "0        0  \n",
       "1        0  \n",
       "2        0  \n",
       "3        0  \n",
       "4        0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from google.cloud import bigquery\n",
    "\n",
    "# Create a \"Client\" object\n",
    "client = bigquery.Client()\n",
    "\n",
    "# Construct a reference to the \"hacker_news\" dataset\n",
    "dataset_ref = client.dataset(\"hacker_news\", project=\"bigquery-public-data\")\n",
    "\n",
    "# API request - fetch the dataset\n",
    "dataset = client.get_dataset(dataset_ref)\n",
    "\n",
    "# Construct a reference to the \"comments\" table\n",
    "table_ref = dataset_ref.table(\"comments\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table = client.get_table(table_ref)\n",
    "\n",
    "# Preview the first five lines of the \"comments\" table\n",
    "client.list_rows(table, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercises\n",
    "\n",
    "### 1) Prolific commenters\n",
    "\n",
    "Hacker 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",
    "\n",
    "In case sample query is helpful, here is a query you saw in the tutorial to answer a similar question:\n",
    "```\n",
    "query = \"\"\"\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",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "         author  NumPosts\n",
      "0  dragonwriter     10723\n",
      "1          None    227736\n",
      "2           eru     10448\n",
      "3       rbanffy     10557\n",
      "4         DanBC     12902\n"
     ]
    },
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.5, \"interactionType\": 1, \"questionType\": 2, \"questionId\": \"1_ProlificCommenters\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Query to select prolific commenters and post counts\n",
    "prolific_commenters_query = \"\"\"SELECT author, COUNT(id) AS NumPosts\n",
    "                                 FROM `bigquery-public-data.hacker_news.comments`\n",
    "                                 GROUP BY author\n",
    "                                 HAVING COUNT(id) > 10000\"\"\"\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)\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "query_job = client.query(prolific_commenters_query, job_config=safe_config)\n",
    "\n",
    "# API request - run the query, and return a pandas DataFrame\n",
    "prolific_commenters = query_job.to_dataframe()\n",
    "\n",
    "# View top few rows of results\n",
    "print(prolific_commenters.head())\n",
    "\n",
    "# Check your answer\n",
    "q_1.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the solution, uncomment the line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_1.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Deleted comments\n",
    "\n",
    "How many comments have been deleted? (If a comment was deleted, the `deleted` column in the comments table will have the value `True`.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>num_deleted_posts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>227736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num_deleted_posts\n",
       "0             227736"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Write your query here and figure out the answer\n",
    "\n",
    "# Query to determine how many posts were deleted\n",
    "deleted_posts_query = \"\"\"SELECT COUNT(1) AS num_deleted_posts\n",
    "                           FROM `bigquery-public-data.hacker_news.comments`\n",
    "                           WHERE deleted = True \"\"\"\n",
    "\n",
    "# Set up the query\n",
    "query_job = client.query(deleted_posts_query)\n",
    "\n",
    "# API request - run the query, and return a pandas DataFrame\n",
    "deleted_posts = query_job.to_dataframe()\n",
    "\n",
    "# View results\n",
    "deleted_posts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "parent.postMessage({\"jupyterEvent\": \"custom.exercise_interaction\", \"data\": {\"outcomeType\": 1, \"valueTowardsCompletion\": 0.5, \"interactionType\": 1, \"questionType\": 1, \"questionId\": \"2_NumDeletedPosts\", \"learnToolsVersion\": \"0.3.4\", \"failureMessage\": \"\", \"exceptionClass\": \"\", \"trace\": \"\"}}, \"*\")"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/markdown": [
       "<span style=\"color:#33cc33\">Correct</span>"
      ],
      "text/plain": [
       "Correct"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "num_deleted_posts = deleted_posts.loc[0, 'num_deleted_posts'] # Put your answer here\n",
    "\n",
    "q_2.check()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the solution, uncomment the line below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q_2.solution()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Keep Going\n",
    "**[Click here](https://www.kaggle.com/dansbecker/order-by)** to move on and learn about the **ORDER BY** clause."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "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": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}