hacker_news_query.ipynb

{
  "cells": [
    {
      "metadata": {
        "_uuid": "d806df7cf446e426d20f6f22c0a8f0f3dbc2d01e",
        "_cell_guid": "0d7c72bb-92fc-4dd5-aaf1-3f09d5596f70"
      },
      "cell_type": "markdown",
      "source": "# Get Started\n\nAfter forking this notebook, run the code in the following cell:"
    },
    {
      "metadata": {
        "_uuid": "e8e05de6187c3fc6f4bbd50f14db639b62a51bf1",
        "_cell_guid": "dbc3a790-b44a-4bea-a04a-b68fa2fed4c0",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# import package with helper functions \nimport bq_helper\n\n# create a helper object for this dataset\nhacker_news = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n                                   dataset_name=\"hacker_news\")\n\n# print the first couple rows of the \"comments\" table\nhacker_news.head(\"comments\")",
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 1,
          "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": {
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19"
      },
      "cell_type": "markdown",
      "source": "# Question\nUsing the Hacker News dataset in BigQuery, answer the following questions:\n\n#### 1) How many stories (use the \"id\" column) are there of each type (in the \"type\" column) in the full table?"
    },
    {
      "metadata": {
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\" SELECT type, COUNT(id)\n            FROM `bigquery-public-data.hacker_news.full`\n            GROUP BY type\n\"\"\"\nstories_of_each_type = hacker_news.query_to_pandas_safe(query)\nstories_of_each_type.head()",
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 2,
          "data": {
            "text/plain": "      type       f0_\n0  comment  14136753\n1    story   2947287\n2  pollopt     11909\n3      job     10620\n4     poll      1738",
            "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>type</th>\n      <th>f0_</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>comment</td>\n      <td>14136753</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>story</td>\n      <td>2947287</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>pollopt</td>\n      <td>11909</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>job</td>\n      <td>10620</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>poll</td>\n      <td>1738</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "eb321bab8e85853cbff7c4e70fdc631aa587617e",
        "_cell_guid": "1b5237c2-b956-41fe-80f5-4d185e40a126"
      },
      "cell_type": "markdown",
      "source": "#### 2) How many comments have been deleted? (If a comment was deleted the \"deleted\" column in the comments table will have the value \"True\".)"
    },
    {
      "metadata": {
        "_uuid": "940a57fa95be21d02981f268d84b684304b86593",
        "_cell_guid": "17c1d2f3-655a-4c05-985f-4f1f345fe368",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\" SELECT deleted, COUNT(id)\n            FROM `bigquery-public-data.hacker_news.comments`\n            GROUP BY deleted\n\"\"\"\nnum_deleted_comments = hacker_news.query_to_pandas_safe(query)\nnum_deleted_comments.head()",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 3,
          "data": {
            "text/plain": "  deleted      f0_\n0    True   227736\n1    None  8171681",
            "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>deleted</th>\n      <th>f0_</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>True</td>\n      <td>227736</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>None</td>\n      <td>8171681</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "dd5691b5813a4fd68a6ccb34d97e6d693458383d",
        "_cell_guid": "5f8c5e7b-853d-45ec-ab93-092c47e45971"
      },
      "cell_type": "markdown",
      "source": "#### 3) Modify one of the queries you wrote above to use a different aggregate function.\nYou can read about aggregate functions other than COUNT() **[in these docs](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#aggregate-functions)**"
    },
    {
      "metadata": {
        "_uuid": "587435c6647dc9c86d4c47a224ded128bd799eee",
        "_cell_guid": "02eabdfe-fa24-41b0-a94a-fd0e9aba8172",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your Code Here\nquery = \"\"\" SELECT type, MAX(time)\n            FROM `bigquery-public-data.hacker_news.full`\n            GROUP BY type\n\"\"\"\nlatest_time_of_each_type = hacker_news.query_to_pandas_safe(query)\nlatest_time_of_each_type.head()",
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 4,
          "data": {
            "text/plain": "      type         f0_\n0  pollopt  1526501753\n1     poll  1526501753\n2      job  1526766226\n3  comment  1526768600\n4    story  1526768607",
            "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>type</th>\n      <th>f0_</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>pollopt</td>\n      <td>1526501753</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>poll</td>\n      <td>1526501753</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>job</td>\n      <td>1526766226</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>comment</td>\n      <td>1526768600</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>story</td>\n      <td>1526768607</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "fc08b831741d6e9047b43fcf744f4701527a2ca7",
        "_cell_guid": "827df875-f5ce-4435-9b6e-3fd44b64c69c"
      },
      "cell_type": "markdown",
      "source": "---\n\n# Keep Going\n[Click here](https://www.kaggle.com/dansbecker/order-by) to move on and learn about the ORDER BY clause.\n\n# Feedback\nBring any questions or feedback to the [Learn Discussion Forum](kaggle.com/learn-forum).\n\n----\n\n*This exercise is part of the [SQL Series](https://www.kaggle.com/learn/sql) on Kaggle Learn.*"
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}