rise-of-hackernews.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HackerNews Dataset Analysis\n",
    "This dataset contains all stories and comments from [HackerNews](https://news.ycombinator.com/news)  from its launch in 2006. Each story contains a story id, the author that made the post, when it was written, and the number of points the story received. <br>\n",
    "Hacker News is a social news website focusing on computer science and entrepreneurship. It is run by Paul Graham's investment fund and startup incubator, Y Combinator. In general, content that can be submitted is defined as \"anything that gratifies one's intellectual curiosity\". <br>\n",
    "\n",
    "There are 4 tables in this public dataset:\n",
    "- stories (a.k.a posts)\n",
    "- comments\n",
    "- full\n",
    "- full_2015 <br>\n",
    "<br>\n",
    "* An example [post](https://news.ycombinator.com/item?id=8596682) from HackerNews.\n",
    "* An example of a [comment](https://news.ycombinator.com/item?id=8597333) from HackerNews.\n",
    "* A post can have many comments.\n",
    "* A comment can have many comments.\n",
    "* Posts can have scores.\n",
    "* Posts have rankings showing their popularity.\n",
    "\n",
    "\n",
    "So the structure is:\n",
    "* Story\n",
    "  * comment\n",
    "    * comment\n",
    "      * comment\n",
    "\n",
    "**Now the question is, how this structure represented in the tables ?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 1) Understanding the structure between *stories* and *comments* tables\n",
    "Before diving deeper in the queries lets understand the structure of the tables by investigating following questions:\n",
    "* Parent_id column in the comments table: \n",
    "  * what do they represent? \n",
    "  * do they only represent parent id of the comment or the parent id of the story?\n",
    "* How much of a comments does a story has, which column represents it?\n",
    "* Are the comment and story ids globally unique?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19",
    "_kg_hide-input": true,
    "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5"
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "for dirname, _, filenames in os.walk('/kaggle/input'):\n",
    "    for filename in filenames:\n",
    "        print(os.path.join(dirname, filename))\n",
    "\n",
    "# Any results you write to the current directory are saved as output."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stories Table - First Rows"
   ]
  },
  {
   "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>score</th>\n",
       "      <th>time</th>\n",
       "      <th>time_ts</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>text</th>\n",
       "      <th>deleted</th>\n",
       "      <th>dead</th>\n",
       "      <th>descendants</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6940813</td>\n",
       "      <td>sarath237</td>\n",
       "      <td>0</td>\n",
       "      <td>1387536270</td>\n",
       "      <td>2013-12-20 10:44:30+00:00</td>\n",
       "      <td>Sheryl Brindo Hot Pics</td>\n",
       "      <td>http://www.youtube.com/watch?v=ym1cyxneB0Y</td>\n",
       "      <td>Sheryl Brindo Hot Pics</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sarath237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6991401</td>\n",
       "      <td>123123321321</td>\n",
       "      <td>0</td>\n",
       "      <td>1388508751</td>\n",
       "      <td>2013-12-31 16:52:31+00:00</td>\n",
       "      <td>Are you people also put off by the culture of ...</td>\n",
       "      <td></td>\n",
       "      <td>They&amp;#x27;re pretty explicitly &amp;#x27;startup f...</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>123123321321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1531556</td>\n",
       "      <td>ssn</td>\n",
       "      <td>0</td>\n",
       "      <td>1279617234</td>\n",
       "      <td>2010-07-20 09:13:54+00:00</td>\n",
       "      <td>New UI for Google Image Search</td>\n",
       "      <td>http://googlesystem.blogspot.com/2010/07/googl...</td>\n",
       "      <td>Again following on Bing's lead.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ssn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5012398</td>\n",
       "      <td>hoju</td>\n",
       "      <td>0</td>\n",
       "      <td>1357387877</td>\n",
       "      <td>2013-01-05 12:11:17+00:00</td>\n",
       "      <td>Historic website screenshots</td>\n",
       "      <td>http://webscraping.com/blog/Generate-website-s...</td>\n",
       "      <td>Python script to generate historic screenshots...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>hoju</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7214182</td>\n",
       "      <td>kogir</td>\n",
       "      <td>0</td>\n",
       "      <td>1401561740</td>\n",
       "      <td>2014-05-31 18:42:20+00:00</td>\n",
       "      <td>Placeholder</td>\n",
       "      <td></td>\n",
       "      <td>Mind the gap.</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>kogir</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id            by  score        time                   time_ts  \\\n",
       "0  6940813     sarath237      0  1387536270 2013-12-20 10:44:30+00:00   \n",
       "1  6991401  123123321321      0  1388508751 2013-12-31 16:52:31+00:00   \n",
       "2  1531556           ssn      0  1279617234 2010-07-20 09:13:54+00:00   \n",
       "3  5012398          hoju      0  1357387877 2013-01-05 12:11:17+00:00   \n",
       "4  7214182         kogir      0  1401561740 2014-05-31 18:42:20+00:00   \n",
       "\n",
       "                                               title  \\\n",
       "0                            Sheryl Brindo Hot Pics    \n",
       "1  Are you people also put off by the culture of ...   \n",
       "2                     New UI for Google Image Search   \n",
       "3                       Historic website screenshots   \n",
       "4                                        Placeholder   \n",
       "\n",
       "                                                 url  \\\n",
       "0         http://www.youtube.com/watch?v=ym1cyxneB0Y   \n",
       "1                                                      \n",
       "2  http://googlesystem.blogspot.com/2010/07/googl...   \n",
       "3  http://webscraping.com/blog/Generate-website-s...   \n",
       "4                                                      \n",
       "\n",
       "                                                text deleted  dead  \\\n",
       "0                             Sheryl Brindo Hot Pics    None  True   \n",
       "1  They&#x27;re pretty explicitly &#x27;startup f...    None  True   \n",
       "2                    Again following on Bing's lead.    None  None   \n",
       "3  Python script to generate historic screenshots...    None  None   \n",
       "4                                      Mind the gap.    None  None   \n",
       "\n",
       "   descendants        author  \n",
       "0          NaN     sarath237  \n",
       "1          NaN  123123321321  \n",
       "2          0.0           ssn  \n",
       "3          0.0          hoju  \n",
       "4          0.0         kogir  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view first rows of stories table\n",
    "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",
    "# Construct a reference to the \"stories\" table\n",
    "table_ref_stories = dataset_ref.table(\"stories\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table_stories = client.get_table(table_ref_stories)\n",
    "\n",
    "# Preview the first five lines of the \"stories\" table\n",
    "client.list_rows(table_stories, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Stories**\n",
    "* `id` is the unique key of the story.\n",
    "* `descendants` is the number of all comments that a story has.\n",
    "* Points of a story is represented as score in the table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stories Table - Attributes and Field Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SchemaField('id', 'INTEGER', 'NULLABLE', 'Unique story ID', ()),\n",
       " SchemaField('by', 'STRING', 'NULLABLE', 'Username of submitter', ()),\n",
       " SchemaField('score', 'INTEGER', 'NULLABLE', 'Story score', ()),\n",
       " SchemaField('time', 'INTEGER', 'NULLABLE', 'Unix time', ()),\n",
       " SchemaField('time_ts', 'TIMESTAMP', 'NULLABLE', 'Human readable time in UTC (format: YYYY-MM-DD hh:mm:ss)', ()),\n",
       " SchemaField('title', 'STRING', 'NULLABLE', 'Story title', ()),\n",
       " SchemaField('url', 'STRING', 'NULLABLE', 'Story url', ()),\n",
       " SchemaField('text', 'STRING', 'NULLABLE', 'Story text', ()),\n",
       " SchemaField('deleted', 'BOOLEAN', 'NULLABLE', 'Is deleted?', ()),\n",
       " SchemaField('dead', 'BOOLEAN', 'NULLABLE', 'Is dead?', ()),\n",
       " SchemaField('descendants', 'INTEGER', 'NULLABLE', 'Number of story descendants', ()),\n",
       " SchemaField('author', 'STRING', 'NULLABLE', 'Username of author', ())]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_stories.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comments Table - First Rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
    "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a"
   },
   "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>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": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Construct a reference to the \"comments\" table\n",
    "table_ref_comments = dataset_ref.table(\"comments\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table_comments = client.get_table(table_ref_comments)\n",
    "\n",
    "# Preview the first five lines of the \"comments\" table\n",
    "client.list_rows(table_comments, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comments Table - Attributes and Field Data Types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[SchemaField('id', 'INTEGER', 'NULLABLE', 'Unique comment ID', ()),\n",
       " SchemaField('by', 'STRING', 'NULLABLE', 'Username of commenter', ()),\n",
       " SchemaField('author', 'STRING', 'NULLABLE', 'Username of author', ()),\n",
       " SchemaField('time', 'INTEGER', 'NULLABLE', 'Unix time', ()),\n",
       " SchemaField('time_ts', 'TIMESTAMP', 'NULLABLE', 'Human readable time in UTC (format: YYYY-MM-DD hh:mm:ss)', ()),\n",
       " SchemaField('text', 'STRING', 'NULLABLE', 'Comment text', ()),\n",
       " SchemaField('parent', 'INTEGER', 'NULLABLE', 'Parent comment ID', ()),\n",
       " SchemaField('deleted', 'BOOLEAN', 'NULLABLE', 'Is deleted?', ()),\n",
       " SchemaField('dead', 'BOOLEAN', 'NULLABLE', 'Is dead?', ()),\n",
       " SchemaField('ranking', 'INTEGER', 'NULLABLE', 'Comment ranking', ())]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_comments.schema"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parent column in the `comments` table, what does it represent?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# look at the parent_id column in the comments table\n",
    "# then search for parent_ids who does not represent a comment_id in the comments table\n",
    "# if this results are no empty, it means parent column represent parend comment id and one another id\n",
    "query_for_id_selection = \"\"\" \n",
    "                            SELECT DISTINCT(parent)\n",
    "                            FROM `bigquery-public-data.hacker_news.comments`\n",
    "                            WHERE parent NOT IN (\n",
    "                                                    SELECT id\n",
    "                                                    FROM `bigquery-public-data.hacker_news.comments`)\n",
    "            \n",
    "\"\"\"\n",
    "# set quota not to exceed limits\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "\n",
    "# create job to execute query\n",
    "query_for_id_selection_job = client.query(query_for_id_selection, job_config=safe_config)\n",
    "\n",
    "#load results into a dataframe\n",
    "query_for_id_result = query_for_id_selection_job.to_dataframe()\n",
    "\n",
    "# turn dataframe to a a series, then list, then list of strings to use in the query\n",
    "non_comment_ids = \",\".join(map(str,query_for_id_result.head(20).parent.tolist()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# function to write query results to a dataframe\n",
    "# without exceeding the 1 GB quota per query\n",
    "def query_to_dataframe(query_name):\n",
    "    safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "    query_name_job = client.query(query_name, job_config=safe_config)\n",
    "    return query_name_job.to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of parent_ids that are not comment_ids: 485055\n"
     ]
    }
   ],
   "source": [
    "print(\"Number of parent_ids that are not comment_ids:\", query_for_id_result.parent.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Conclusion 1:** There are 485055 parent_ids that does not represent to a comment_id. Lets have a look at some of them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'7868175,1155342,8703793,3085102,3917074,4318042,2777883,4727721,165587,1771361,1139768,4684420,1340396,8411638,2684620,6495507,4625764,5483767,10097630,7081246'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_comment_ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>id</th>\n",
       "      <th>by</th>\n",
       "      <th>score</th>\n",
       "      <th>time</th>\n",
       "      <th>time_ts</th>\n",
       "      <th>title</th>\n",
       "      <th>url</th>\n",
       "      <th>text</th>\n",
       "      <th>deleted</th>\n",
       "      <th>dead</th>\n",
       "      <th>descendants</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3917074</td>\n",
       "      <td>ahsanhilal</td>\n",
       "      <td>34</td>\n",
       "      <td>1335919322</td>\n",
       "      <td>2012-05-02 00:42:02+00:00</td>\n",
       "      <td>Ask HN: Socialcam spamming Facebook newsfeeds ...</td>\n",
       "      <td></td>\n",
       "      <td>So I clicked play on a social cam video somebo...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>ahsanhilal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4625764</td>\n",
       "      <td>zengr</td>\n",
       "      <td>2</td>\n",
       "      <td>1349678518</td>\n",
       "      <td>2012-10-08 06:41:58+00:00</td>\n",
       "      <td>Einstein letter on God to be auctioned on eBay</td>\n",
       "      <td>http://www.huffingtonpost.com/huff-wires/20121...</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>zengr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4318042</td>\n",
       "      <td>patrickod</td>\n",
       "      <td>273</td>\n",
       "      <td>1343751808</td>\n",
       "      <td>2012-07-31 16:23:28+00:00</td>\n",
       "      <td>How I made $10k in one day with Facebook Ads</td>\n",
       "      <td>http://irvinebroque.tumblr.com/post/2841539387...</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>113</td>\n",
       "      <td>patrickod</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>165587</td>\n",
       "      <td>NickSmith</td>\n",
       "      <td>2</td>\n",
       "      <td>1208377299</td>\n",
       "      <td>2008-04-16 20:21:39+00:00</td>\n",
       "      <td>Aha! moments in Lisp... What were your 'Aha mo...</td>\n",
       "      <td>http://groups.google.com/group/comp.lang.lisp/...</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>NickSmith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5483767</td>\n",
       "      <td>markchristian</td>\n",
       "      <td>81</td>\n",
       "      <td>1364955284</td>\n",
       "      <td>2013-04-03 02:14:44+00:00</td>\n",
       "      <td>Show HN: my new app Timebar has a clever UI (I...</td>\n",
       "      <td></td>\n",
       "      <td>Howdy, gang;\\nAfter almost two weeks of sittin...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>56</td>\n",
       "      <td>markchristian</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id             by  score        time                   time_ts  \\\n",
       "0  3917074     ahsanhilal     34  1335919322 2012-05-02 00:42:02+00:00   \n",
       "1  4625764          zengr      2  1349678518 2012-10-08 06:41:58+00:00   \n",
       "2  4318042      patrickod    273  1343751808 2012-07-31 16:23:28+00:00   \n",
       "3   165587      NickSmith      2  1208377299 2008-04-16 20:21:39+00:00   \n",
       "4  5483767  markchristian     81  1364955284 2013-04-03 02:14:44+00:00   \n",
       "\n",
       "                                               title  \\\n",
       "0  Ask HN: Socialcam spamming Facebook newsfeeds ...   \n",
       "1     Einstein letter on God to be auctioned on eBay   \n",
       "2       How I made $10k in one day with Facebook Ads   \n",
       "3  Aha! moments in Lisp... What were your 'Aha mo...   \n",
       "4  Show HN: my new app Timebar has a clever UI (I...   \n",
       "\n",
       "                                                 url  \\\n",
       "0                                                      \n",
       "1  http://www.huffingtonpost.com/huff-wires/20121...   \n",
       "2  http://irvinebroque.tumblr.com/post/2841539387...   \n",
       "3  http://groups.google.com/group/comp.lang.lisp/...   \n",
       "4                                                      \n",
       "\n",
       "                                                text deleted  dead  \\\n",
       "0  So I clicked play on a social cam video somebo...    None  None   \n",
       "1                                                       None  None   \n",
       "2                                                       None  None   \n",
       "3                                                       None  None   \n",
       "4  Howdy, gang;\\nAfter almost two weeks of sittin...    None  None   \n",
       "\n",
       "   descendants         author  \n",
       "0            4     ahsanhilal  \n",
       "1            1          zengr  \n",
       "2          113      patrickod  \n",
       "3            3      NickSmith  \n",
       "4           56  markchristian  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# are parent_ids that does not represent a comment_id, do they belong to a story?\n",
    "non_comment_id_query = \"\"\" \n",
    "                            SELECT *\n",
    "                            FROM `bigquery-public-data.hacker_news.stories` AS stories\n",
    "                            WHERE id IN ({})\n",
    "                    \"\"\".format(non_comment_ids)\n",
    "\n",
    "# print query results\n",
    "query_to_dataframe(non_comment_id_query).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Conclusion 2:** Some of the non_comment_ids are story_ids."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How much of comments does a story has, which column represents it?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of comments that story 4318042 has: 53\n"
     ]
    }
   ],
   "source": [
    "# look at parent id row 4318042 to see how many comments are dependant on it\n",
    "# in the stories table, number of  descendants are 113\n",
    "query_number_of_comments = \"\"\"\n",
    "                            SELECT *\n",
    "                            FROM `bigquery-public-data.hacker_news.comments`\n",
    "                            WHERE parent = 4318042\n",
    "\"\"\"\n",
    "\n",
    "print(\"Number of comments that story 4318042 has:\", len(query_to_dataframe(query_number_of_comments)))\n",
    "# it did not give 122 rows because of the tree structure.\n",
    "# it only give the first level comment id which is 12."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* When number of descendants were 113, number of comments belong to that story is 53.\n",
    "* This is an expected result because of the tree structure that post and comments have.\n",
    "* 53 is the number of first level comments created for that story, 113 is the number of all sub-comments that a story has. <br>\n",
    "\n",
    "**Conclusion 3:** Descendants attribute are the total number of comments including sub-comments that a story has."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Are id columns in the stories and comments globally unique ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id]\n",
       "Index: []"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_query = \"\"\" \n",
    "                SELECT id\n",
    "                FROM `bigquery-public-data.hacker_news.stories`\n",
    "                WHERE id IN (\n",
    "                            SELECT id\n",
    "                            FROM `bigquery-public-data.hacker_news.comments`\n",
    "                            )\n",
    "\"\"\"\n",
    "\n",
    "query_to_dataframe(id_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Conclusion 4:** Ids are globally unique among stories and comments table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2) With the conclusions in mind, some interesting questions to ask:\n",
    "* Recent studies have found that many forums tend to be dominated by a very small fraction of users. Is this true of Hacker News?\n",
    "* Hacker News has received complaints that the site is biased towards Y Combinator startups. Do the data support this?\n",
    "* What is the average number of daily comments created per day?\n",
    "* What is the number of users that HackerNews had over years?\n",
    "* How long does it take for a post to receive comment?\n",
    "* How many of the comments receive sub-comments ?\n",
    "* Is it more common for users to first create post or provide comments?\n",
    "* For the users who joined the site in January 2014. When did they post their first story or comment, if ever?\n",
    "* How many distinct users posted on October, 2015?\n",
    "* What is the moving average (within the 15 day window) of number of posts created in each post category? \n",
    "* What is the rank of the stories based on scores, created in the same day ?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## a) Recent studies have found that many forums tend to be dominated by a very small fraction of users. Is this true of Hacker News?\n",
    "* number of posts created\n",
    "* number of comments created\n",
    "\n",
    "shows a sign of being a active and dominated user in the HackerNews.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>author</th>\n",
       "      <th>number_of_stories</th>\n",
       "      <th>number_of_comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>25721</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>cwan</td>\n",
       "      <td>7001</td>\n",
       "      <td>389.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>shawndumas</td>\n",
       "      <td>6507</td>\n",
       "      <td>967.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>evo_9</td>\n",
       "      <td>5456</td>\n",
       "      <td>506.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>nickb</td>\n",
       "      <td>4276</td>\n",
       "      <td>1152.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>iProject</td>\n",
       "      <td>4237</td>\n",
       "      <td>36.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>bootload</td>\n",
       "      <td>4151</td>\n",
       "      <td>2678.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>edw519</td>\n",
       "      <td>3788</td>\n",
       "      <td>4570.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>ColinWright</td>\n",
       "      <td>3702</td>\n",
       "      <td>5309.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>tokenadult</td>\n",
       "      <td>3622</td>\n",
       "      <td>6706.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        author  number_of_stories  number_of_comments\n",
       "0         None              25721                 NaN\n",
       "1         cwan               7001               389.0\n",
       "2   shawndumas               6507               967.0\n",
       "3        evo_9               5456               506.0\n",
       "4        nickb               4276              1152.0\n",
       "5     iProject               4237                36.0\n",
       "6     bootload               4151              2678.0\n",
       "7       edw519               3788              4570.0\n",
       "8  ColinWright               3702              5309.0\n",
       "9   tokenadult               3622              6706.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to have a look we are going to count the number of comments and posts created \n",
    "# for not deleted and dead ones\n",
    "active_users_query = \"\"\"\n",
    "                        WITH active_users_from_stories AS (\n",
    "                            SELECT author,\n",
    "                                COUNT(*) AS number_of_stories\n",
    "                            FROM `bigquery-public-data.hacker_news.stories`\n",
    "                            WHERE deleted IS NOT TRUE AND dead IS NOT TRUE\n",
    "                            GROUP BY author\n",
    "                            ORDER BY number_of_stories DESC\n",
    "                        ),\n",
    "                        active_users_from_comments AS (\n",
    "                            SELECT author,\n",
    "                                COUNT(*) AS number_of_comments\n",
    "                            FROM `bigquery-public-data.hacker_news.comments`\n",
    "                            WHERE deleted IS NOT TRUE AND dead IS NOT TRUE\n",
    "                            GROUP BY author\n",
    "                            ORDER BY number_of_comments DESC\n",
    "                        )\n",
    "                        SELECT active_users_from_comments.author,\n",
    "                            number_of_stories,\n",
    "                            number_of_comments\n",
    "                        FROM active_users_from_stories\n",
    "                        FULL JOIN active_users_from_comments\n",
    "                         ON active_users_from_stories.author = active_users_from_comments.author\n",
    "                        ORDER BY number_of_stories DESC\n",
    "                        LIMIT 10\n",
    "\"\"\"\n",
    "# if you change last ORDER BY clause to number_of_comments will list the users who commented most\n",
    "safe_config = bigquery.QueryJobConfig(maximum_bytes_billed=10**10)\n",
    "active_users_query_job = client.query(active_users_query, job_config=safe_config)\n",
    "active_users_query_result = active_users_query_job.to_dataframe()\n",
    "active_users_query_result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Most active users in terms of number of stories created have created the 0.05 of the whole stories\n"
     ]
    }
   ],
   "source": [
    "# look at the total number of stories in the whole hackernews which is not dead or deleted\n",
    "total_stories = \"\"\"\n",
    "                    SELECT COUNT(id)\n",
    "                    FROM `bigquery-public-data.hacker_news.stories`\n",
    "                    WHERE deleted IS NOT TRUE AND dead IS NOT TRUE\n",
    "\"\"\"\n",
    "total_stories_df = client.query(total_stories).result().to_dataframe()\n",
    "\n",
    "percentage = active_users_query_result.number_of_stories.sum()/total_stories_df.f0_.iloc[0]\n",
    "\n",
    "print(\"Most active users in terms of number of stories created have created the {} of the whole stories\"\n",
    "      .format(round(percentage,2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## b) Hacker News has received complaints that the site is biased towards Y Combinator startups. Do the data support this?\n",
    "\n",
    "Here are some of the most popular YCombinator startups:\n",
    "\n",
    "* AirBnB\n",
    "* Stripe\n",
    "* Dropbox\n",
    "* Zapier\n",
    "* Reddit\n",
    "and so on.."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>title</th>\n",
       "      <th>url</th>\n",
       "      <th>text</th>\n",
       "      <th>dead</th>\n",
       "      <th>by</th>\n",
       "      <th>score</th>\n",
       "      <th>time</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>type</th>\n",
       "      <th>id</th>\n",
       "      <th>parent</th>\n",
       "      <th>descendants</th>\n",
       "      <th>ranking</th>\n",
       "      <th>deleted</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>&amp;gt;Imagine being in a software team and disre...</td>\n",
       "      <td>None</td>\n",
       "      <td>nabla9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1508324981</td>\n",
       "      <td>2017-10-18 11:09:41+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>15498153</td>\n",
       "      <td>15497888.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Need your opinion on PadKite, the first multit...</td>\n",
       "      <td>http://padkite.com</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>JoseVigil</td>\n",
       "      <td>40.0</td>\n",
       "      <td>1301020096</td>\n",
       "      <td>2011-03-25 02:28:16+00:00</td>\n",
       "      <td>story</td>\n",
       "      <td>2366954</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>70-80% of atmospheric oxygen comes from ocean ...</td>\n",
       "      <td>None</td>\n",
       "      <td>Nadya</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1453402215</td>\n",
       "      <td>2016-01-21 18:50:15+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>10947337</td>\n",
       "      <td>10946877.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;p&gt;&lt;pre&gt;&lt;code&gt;  OK, so what were they going to...</td>\n",
       "      <td>None</td>\n",
       "      <td>michaelt</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1519121875</td>\n",
       "      <td>2018-02-20 10:17:55+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>16419184</td>\n",
       "      <td>16419046.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>What surprised me was that ironically reductio...</td>\n",
       "      <td>None</td>\n",
       "      <td>salimmadjd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1535990892</td>\n",
       "      <td>2018-09-03 16:08:12+00:00</td>\n",
       "      <td>comment</td>\n",
       "      <td>17903321</td>\n",
       "      <td>17902533.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               title                 url  \\\n",
       "0                                               None                None   \n",
       "1  Need your opinion on PadKite, the first multit...  http://padkite.com   \n",
       "2                                               None                None   \n",
       "3                                               None                None   \n",
       "4                                               None                None   \n",
       "\n",
       "                                                text  dead          by  score  \\\n",
       "0  &gt;Imagine being in a software team and disre...  None      nabla9    NaN   \n",
       "1                                               None  None   JoseVigil   40.0   \n",
       "2  70-80% of atmospheric oxygen comes from ocean ...  None       Nadya    NaN   \n",
       "3  <p><pre><code>  OK, so what were they going to...  None    michaelt    NaN   \n",
       "4  What surprised me was that ironically reductio...  None  salimmadjd    NaN   \n",
       "\n",
       "         time                 timestamp     type        id      parent  \\\n",
       "0  1508324981 2017-10-18 11:09:41+00:00  comment  15498153  15497888.0   \n",
       "1  1301020096 2011-03-25 02:28:16+00:00    story   2366954         NaN   \n",
       "2  1453402215 2016-01-21 18:50:15+00:00  comment  10947337  10946877.0   \n",
       "3  1519121875 2018-02-20 10:17:55+00:00  comment  16419184  16419046.0   \n",
       "4  1535990892 2018-09-03 16:08:12+00:00  comment  17903321  17902533.0   \n",
       "\n",
       "   descendants ranking deleted  \n",
       "0          NaN    None    None  \n",
       "1         34.0    None    None  \n",
       "2          NaN    None    None  \n",
       "3          NaN    None    None  \n",
       "4          NaN    None    None  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before providing an answer to this question investigate and understand full table\n",
    "\n",
    "# Construct a reference to the \"full\" table\n",
    "table_ref_full = dataset_ref.table(\"full\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table_full = client.get_table(table_ref_full)\n",
    "\n",
    "# Preview the first five lines of the \"full\" table\n",
    "client.list_rows(table_full, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>popular_startup_name</th>\n",
       "      <th>total_ranking</th>\n",
       "      <th>total_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>38387600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Reddit</td>\n",
       "      <td>None</td>\n",
       "      <td>117769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dropbox</td>\n",
       "      <td>None</td>\n",
       "      <td>89843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Stripe</td>\n",
       "      <td>None</td>\n",
       "      <td>57933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Airbnb</td>\n",
       "      <td>None</td>\n",
       "      <td>52437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Zapier</td>\n",
       "      <td>None</td>\n",
       "      <td>2778</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  popular_startup_name total_ranking  total_score\n",
       "0                 None          None     38387600\n",
       "1               Reddit          None       117769\n",
       "2              Dropbox          None        89843\n",
       "3               Stripe          None        57933\n",
       "4               Airbnb          None        52437\n",
       "5               Zapier          None         2778"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# if those keywords occur in the full table's text or title column we can say they got covered\n",
    "coverage_query = \"\"\"WITH startup_ranking_score AS (\n",
    "                    SELECT CASE \n",
    "                        WHEN title LIKE \"%Airbnb%\" OR text LIKE \"%Airbnb%\" THEN \"Airbnb\"\n",
    "                        WHEN title LIKE \"%Stripe%\" OR text LIKE \"%Stripe%\" THEN \"Stripe\"\n",
    "                        WHEN title LIKE \"%Dropbox%\" OR text LIKE \"%Dropbox%\" THEN \"Dropbox\"\n",
    "                        WHEN title LIKE \"%Zapier%\" OR text LIKE \"%Zapier%\" THEN \"Zapier\"\n",
    "                        WHEN title LIKE \"%Reddit%\" OR text LIKE \"%Reddit%\" THEN \"Reddit\"\n",
    "                        END AS popular_startup_name,\n",
    "                    ranking,\n",
    "                    score\n",
    "                    FROM `bigquery-public-data.hacker_news.full`\n",
    "                                                                )\n",
    "                    SELECT popular_startup_name,\n",
    "                        SUM(ranking) AS total_ranking,\n",
    "                        SUM(score) AS total_score\n",
    "                    FROM startup_ranking_score\n",
    "                    GROUP BY popular_startup_name\n",
    "                    ORDER BY total_score DESC\n",
    "\"\"\"\n",
    "query_to_dataframe(coverage_query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "YCombinator startups got covered by HackerNews. However, most of the stories belong to other subjects showing there is no bias towards those most popular startups."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## c) What is the average number of daily comments created per day?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>year</th>\n",
       "      <th>average_daily_comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2006</td>\n",
       "      <td>4.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007</td>\n",
       "      <td>224.234177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008</td>\n",
       "      <td>677.382514</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009</td>\n",
       "      <td>1345.435616</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>2307.989041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011</td>\n",
       "      <td>2862.682192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2012</td>\n",
       "      <td>3406.450820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013</td>\n",
       "      <td>4562.136986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014</td>\n",
       "      <td>4150.587912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2015</td>\n",
       "      <td>4473.744755</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  average_daily_comments\n",
       "0  2006                4.000000\n",
       "1  2007              224.234177\n",
       "2  2008              677.382514\n",
       "3  2009             1345.435616\n",
       "4  2010             2307.989041\n",
       "5  2011             2862.682192\n",
       "6  2012             3406.450820\n",
       "7  2013             4562.136986\n",
       "8  2014             4150.587912\n",
       "9  2015             4473.744755"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to look at this first total number of comments generated per day will be investigated\n",
    "# then the average of the year will be aggregated\n",
    "average_daily_comments_per_year = \"\"\" WITH total_comments_generated_per_day AS (\n",
    "                                        SELECT EXTRACT(DAYOFYEAR FROM time_ts) AS day,\n",
    "                                            EXTRACT(YEAR FROM time_ts) AS year,\n",
    "                                            COUNT(id) AS total_comments\n",
    "                                        FROM `bigquery-public-data.hacker_news.comments`\n",
    "                                        GROUP BY year, day\n",
    "                                        )\n",
    "                                        SELECT year, \n",
    "                                            AVG(total_comments) AS average_daily_comments\n",
    "                                        FROM total_comments_generated_per_day\n",
    "                                        GROUP BY year\n",
    "                                        ORDER BY year\n",
    "\"\"\"\n",
    "query_to_dataframe(average_daily_comments_per_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looks like popularity of HackerNews increased constantly and dramatically from 2006 to 2015."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## d) What is the number of users that HackerNews had over years?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>year</th>\n",
       "      <th>number_of_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2006</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2007</td>\n",
       "      <td>4158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008</td>\n",
       "      <td>11936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009</td>\n",
       "      <td>26497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2010</td>\n",
       "      <td>45217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011</td>\n",
       "      <td>80301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2012</td>\n",
       "      <td>92339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2013</td>\n",
       "      <td>101526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2014</td>\n",
       "      <td>111154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2015</td>\n",
       "      <td>121848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016</td>\n",
       "      <td>132450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2017</td>\n",
       "      <td>142960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2018</td>\n",
       "      <td>139739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2019</td>\n",
       "      <td>115502</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    year  number_of_users\n",
       "0   2006               16\n",
       "1   2007             4158\n",
       "2   2008            11936\n",
       "3   2009            26497\n",
       "4   2010            45217\n",
       "5   2011            80301\n",
       "6   2012            92339\n",
       "7   2013           101526\n",
       "8   2014           111154\n",
       "9   2015           121848\n",
       "10  2016           132450\n",
       "11  2017           142960\n",
       "12  2018           139739\n",
       "13  2019           115502"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# this question will be investigated in the full table \n",
    "number_of_users = \"\"\"\n",
    "                    SELECT EXTRACT(YEAR FROM timestamp) AS year,\n",
    "                        COUNT(DISTINCT f.by) AS number_of_users\n",
    "                    FROM `bigquery-public-data.hacker_news.full` AS f\n",
    "                    WHERE timestamp IS NOT NULL\n",
    "                    GROUP BY year\n",
    "                    ORDER BY year     \n",
    "\"\"\"\n",
    "query_to_dataframe(number_of_users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Number of users constantly growed, supports the idea of the increasing popularity of HackerNews."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## e) How long does it take for a post to receive comment?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>story_id</th>\n",
       "      <th>second</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7674135</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9429031</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2533536</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8137640</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8101858</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3460425</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9645834</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9150995</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8285007</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9043495</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   story_id  second\n",
       "0   7674135       3\n",
       "1   9429031       4\n",
       "2   2533536       4\n",
       "3   8137640       4\n",
       "4   8101858       5\n",
       "5   3460425       5\n",
       "6   9645834       5\n",
       "7   9150995       5\n",
       "8   8285007       5\n",
       "9   9043495       5"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to answer this question stories and comments tables will be joined and \n",
    "# and timedifference of the time_ts will be investigated\n",
    "time_to_receive_comment = \"\"\"\n",
    "                            WITH time_difference AS (\n",
    "                                SELECT stories.id AS story_id,\n",
    "                                    MIN(TIMESTAMP_DIFF(comments.time_ts, stories.time_ts, SECOND)) AS second\n",
    "                                FROM `bigquery-public-data.hacker_news.stories` AS stories\n",
    "                                LEFT JOIN `bigquery-public-data.hacker_news.comments` AS comments\n",
    "                                    ON stories.id = comments.parent\n",
    "                                GROUP BY story_id\n",
    "                                ORDER BY second ASC)\n",
    "                            SELECT *\n",
    "                            FROM time_difference\n",
    "                            WHERE second >= 0\n",
    "\"\"\"\n",
    "query_to_dataframe(time_to_receive_comment).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It takes only seconds a story to receive a comment."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average number of hours passed for a story to receive a comment: 10.06\n"
     ]
    }
   ],
   "source": [
    "print(\"Average number of hours passed for a story to receive a comment:\",\n",
    "      round(query_to_dataframe(time_to_receive_comment).second.mean()/3600,2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## f) How many of the comments receive sub-comments ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# to answer this question we are going to look at the ratio of \n",
    "# comments receiving subcomments to all comments\n",
    "\n",
    "# total number of comments in the comments table\n",
    "total_num_comments = \"\"\"\n",
    "                        SELECT COUNT(DISTINCT id)\n",
    "                        FROM `bigquery-public-data.hacker_news.comments`\n",
    "                    \"\"\"\n",
    "\n",
    "# total number of comments having sub_comments in the comments table\n",
    "total_num_comments_w_sub_comments = \"\"\" WITH comments_w_subcomment_list AS (\n",
    "                                            SELECT id,\n",
    "                                                CASE\n",
    "                                                    WHEN id IN (\n",
    "                                                        SELECT DISTINCT(parent)\n",
    "                                                        FROM `bigquery-public-data.hacker_news.comments`) \n",
    "                                                            THEN 1\n",
    "                                                    ELSE 0\n",
    "                                                END AS is_commented\n",
    "                                            FROM `bigquery-public-data.hacker_news.comments`)\n",
    "                                        SELECT SUM(is_commented)\n",
    "                                        FROM comments_w_subcomment_list\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of comments with replies 43.82\n"
     ]
    }
   ],
   "source": [
    "percent_of_commented_comments = 100 * (query_to_dataframe(total_num_comments_w_sub_comments).f0_.iloc[0] \n",
    "                                      / query_to_dataframe(total_num_comments).f0_.iloc[0])\n",
    "\n",
    "print(\"Percentage of comments with replies {}\".format(round(percent_of_commented_comments,2)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## g) Is it more common for users to first create post or provide comments ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>type</th>\n",
       "      <th>number_of_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>comment</td>\n",
       "      <td>236359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>story</td>\n",
       "      <td>210368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      type  number_of_users\n",
       "0  comment           236359\n",
       "1    story           210368\n",
       "2     None                0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# we are going to investigate this question in the union of comments and stories table\n",
    "# with necessary attributes\n",
    "user_and_creation_date = \"\"\" WITH authors_creation_times AS (\n",
    "                                SELECT author, \n",
    "                                    time_ts,\n",
    "                                        CASE\n",
    "                                            WHEN author IS NOT NULL THEN \"story\"\n",
    "                                                ELSE NULL\n",
    "                                        END AS type\n",
    "                                FROM `bigquery-public-data.hacker_news.stories` AS stories\n",
    "                                UNION ALL\n",
    "                                SELECT author,\n",
    "                                    time_ts,\n",
    "                                        CASE\n",
    "                                            WHEN author IS NOT NULL THEN \"comment\"\n",
    "                                                ELSE NULL\n",
    "                                    END AS type\n",
    "                                FROM `bigquery-public-data.hacker_news.comments` AS comments),\n",
    "                            first_creation_date AS (\n",
    "                                SELECT author,\n",
    "                                    type,\n",
    "                                    MIN(time_ts) AS first_activity_time\n",
    "                                FROM authors_creation_times\n",
    "                                GROUP BY author, type)\n",
    "                            SELECT type,\n",
    "                                COUNT(author) AS number_of_users\n",
    "                            FROM first_creation_date\n",
    "                            GROUP BY type        \n",
    "\"\"\"\n",
    "query_to_dataframe(user_and_creation_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "People tend to comment first in the HackerNews. However, there is no significant difference between number of users first created a comment or story."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## h) For the users who joined the site in January 2014, When did they post their first story or comment, if ever?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>author</th>\n",
       "      <th>first_activity</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>jiyannwei</td>\n",
       "      <td>2014-01-08 07:26:19+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>kennystone</td>\n",
       "      <td>2014-01-22 14:43:06+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nottombrown</td>\n",
       "      <td>2014-01-31 14:10:32+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>jbdowney</td>\n",
       "      <td>2014-01-16 07:09:50+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>harryzhang</td>\n",
       "      <td>2014-01-20 20:13:38+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bradgessler</td>\n",
       "      <td>2014-01-09 07:33:02+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>kparashar</td>\n",
       "      <td>2014-01-15 04:59:20+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>None</td>\n",
       "      <td>2014-01-01 00:21:49+00:00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>varunsrin</td>\n",
       "      <td>2014-01-29 20:34:15+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>srlake</td>\n",
       "      <td>2014-01-15 19:43:35+00:00</td>\n",
       "      <td>job</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        author            first_activity  type\n",
       "0    jiyannwei 2014-01-08 07:26:19+00:00   job\n",
       "1   kennystone 2014-01-22 14:43:06+00:00   job\n",
       "2  nottombrown 2014-01-31 14:10:32+00:00   job\n",
       "3     jbdowney 2014-01-16 07:09:50+00:00   job\n",
       "4   harryzhang 2014-01-20 20:13:38+00:00   job\n",
       "5  bradgessler 2014-01-09 07:33:02+00:00   job\n",
       "6    kparashar 2014-01-15 04:59:20+00:00   job\n",
       "7         None 2014-01-01 00:21:49+00:00  None\n",
       "8    varunsrin 2014-01-29 20:34:15+00:00   job\n",
       "9       srlake 2014-01-15 19:43:35+00:00   job"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to answer this question\n",
    "# first users who make their first activity on HackerNews on January 2014 will be identified\n",
    "# then their activity will be matched from full table\n",
    "# to make the query more efficient CTEs will be used rather than joining multiple tables at once\n",
    "\n",
    "users_w_first_activity_2014_01 = \"\"\"WITH users_from_2014_01 AS (\n",
    "                                        SELECT f.by AS author,\n",
    "                                            MIN(timestamp) AS first_activity\n",
    "                                        FROM `bigquery-public-data.hacker_news.full` AS f\n",
    "                                        WHERE timestamp >= '2014-01-01' AND timestamp < '2014-02-01'\n",
    "                                        GROUP BY f.by)\n",
    "                                    SELECT users_from_2014_01.author,\n",
    "                                        users_from_2014_01.first_activity,\n",
    "                                        f.type\n",
    "                                    FROM users_from_2014_01\n",
    "                                    LEFT JOIN `bigquery-public-data.hacker_news.full` AS f\n",
    "                                    ON users_from_2014_01.author = f.by \n",
    "                                        AND users_from_2014_01.first_activity = f.timestamp   \n",
    "                                \"\"\"\n",
    "query_to_dataframe(users_w_first_activity_2014_01).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First activities of the users who discovered HackerNews on January 2914, is to create a job post or pollopt."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## i) How many distinct users posted on October, 2015?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>by</th>\n",
       "      <th>score</th>\n",
       "      <th>time</th>\n",
       "      <th>title</th>\n",
       "      <th>type</th>\n",
       "      <th>url</th>\n",
       "      <th>text</th>\n",
       "      <th>parent</th>\n",
       "      <th>deleted</th>\n",
       "      <th>dead</th>\n",
       "      <th>descendants</th>\n",
       "      <th>id</th>\n",
       "      <th>ranking</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>danmaz74</td>\n",
       "      <td>None</td>\n",
       "      <td>1438616833</td>\n",
       "      <td>None</td>\n",
       "      <td>comment</td>\n",
       "      <td>None</td>\n",
       "      <td>Do those analyses also account for the energy ...</td>\n",
       "      <td>9996591</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9996887</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gbraad</td>\n",
       "      <td>None</td>\n",
       "      <td>1438616954</td>\n",
       "      <td>None</td>\n",
       "      <td>comment</td>\n",
       "      <td>None</td>\n",
       "      <td>Full stack engineer who&amp;#x27;s proficient with...</td>\n",
       "      <td>9996335</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9996912</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>qeorge</td>\n",
       "      <td>None</td>\n",
       "      <td>1260997308</td>\n",
       "      <td>None</td>\n",
       "      <td>comment</td>\n",
       "      <td>None</td>\n",
       "      <td>&lt;i&gt;It seems that IE has been a consistent impe...</td>\n",
       "      <td>999251</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>999709</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Flammy</td>\n",
       "      <td>None</td>\n",
       "      <td>1438617965</td>\n",
       "      <td>None</td>\n",
       "      <td>comment</td>\n",
       "      <td>None</td>\n",
       "      <td>Hey all,&lt;p&gt;Ivy Softworks is looking for talent...</td>\n",
       "      <td>9996333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9997096</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>scottliquid15</td>\n",
       "      <td>None</td>\n",
       "      <td>1438618127</td>\n",
       "      <td>None</td>\n",
       "      <td>comment</td>\n",
       "      <td>None</td>\n",
       "      <td>LiquidTalent is an exclusive marketplace for d...</td>\n",
       "      <td>9996333</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>9997137</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              by score        time title     type   url  \\\n",
       "0       danmaz74  None  1438616833  None  comment  None   \n",
       "1         gbraad  None  1438616954  None  comment  None   \n",
       "2         qeorge  None  1260997308  None  comment  None   \n",
       "3         Flammy  None  1438617965  None  comment  None   \n",
       "4  scottliquid15  None  1438618127  None  comment  None   \n",
       "\n",
       "                                                text   parent deleted  dead  \\\n",
       "0  Do those analyses also account for the energy ...  9996591    None  None   \n",
       "1  Full stack engineer who&#x27;s proficient with...  9996335    None  None   \n",
       "2  <i>It seems that IE has been a consistent impe...   999251    None  None   \n",
       "3  Hey all,<p>Ivy Softworks is looking for talent...  9996333    None  None   \n",
       "4  LiquidTalent is an exclusive marketplace for d...  9996333    None  None   \n",
       "\n",
       "  descendants       id ranking  \n",
       "0        None  9996887    None  \n",
       "1        None  9996912    None  \n",
       "2        None   999709    None  \n",
       "3        None  9997096    None  \n",
       "4        None  9997137    None  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before answering this question lets look at the first rows of full_201510 table\n",
    "# Construct a reference to the \"full\" table\n",
    "table_ref_full = dataset_ref.table(\"full_201510\")\n",
    "\n",
    "# API request - fetch the table\n",
    "table_full = client.get_table(table_ref_full)\n",
    "\n",
    "# Preview the first five lines of the \"full\" table\n",
    "client.list_rows(table_full, max_results=5).to_dataframe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>number_of_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>352410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   number_of_users\n",
       "0           352410"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# to answer this we are going to use full_201510 table\n",
    "users_posted_201510 = \"\"\" \n",
    "                        SELECT COUNT(DISTINCT f.by) AS number_of_users\n",
    "                        FROM `bigquery-public-data.hacker_news.full_201510` AS f\n",
    "                     \"\"\"\n",
    "query_to_dataframe(users_posted_201510)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## j) What is the moving average (within the 15 day window) of number of posts created from 2018 and onwards, in each post category? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>date</th>\n",
       "      <th>type</th>\n",
       "      <th>moving_average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>comment</td>\n",
       "      <td>4390.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>job</td>\n",
       "      <td>2.133333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>story</td>\n",
       "      <td>634.933333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>comment</td>\n",
       "      <td>7175.066667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>job</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>poll</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>pollopt</td>\n",
       "      <td>2.800000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2018-01-02</td>\n",
       "      <td>story</td>\n",
       "      <td>1134.933333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>comment</td>\n",
       "      <td>8425.866667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2018-01-03</td>\n",
       "      <td>job</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     type  moving_average\n",
       "0  2018-01-01  comment     4390.866667\n",
       "1  2018-01-01      job        2.133333\n",
       "2  2018-01-01    story      634.933333\n",
       "3  2018-01-02  comment     7175.066667\n",
       "4  2018-01-02      job        3.000000\n",
       "5  2018-01-02     poll        1.000000\n",
       "6  2018-01-02  pollopt        2.800000\n",
       "7  2018-01-02    story     1134.933333\n",
       "8  2018-01-03  comment     8425.866667\n",
       "9  2018-01-03      job        7.000000"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of posts created temporary table will be created using full table\n",
    "# and then moving averages per date and post category will be calculated\n",
    "# using analytic functions\n",
    "moving_average_query = \"\"\" WITH num_posts_per_day_type AS ( \n",
    "                            SELECT EXTRACT(DATE FROM timestamp) AS date,\n",
    "                                type,\n",
    "                                COUNT(id) AS num_posts\n",
    "                            FROM `bigquery-public-data.hacker_news.full` \n",
    "                            WHERE timestamp >= \"2018-01-01\"\n",
    "                            GROUP BY date, type\n",
    "                            )\n",
    "                          SELECT date,\n",
    "                            type,\n",
    "                            AVG(num_posts) OVER (\n",
    "                                PARTITION BY type\n",
    "                                ORDER BY num_posts\n",
    "                                ROWS BETWEEN 7 PRECEDING AND 7 FOLLOWING) AS moving_average\n",
    "                          FROM num_posts_per_day_type\n",
    "                          ORDER BY date\n",
    "\"\"\"\n",
    "query_to_dataframe(moving_average_query).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Number of posts created in story and comment category have a significantly higher moving averages than job, poll and pollopt category."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## k) What is the rank of the stories based on scores, created in the same day ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>id</th>\n",
       "      <th>score</th>\n",
       "      <th>date</th>\n",
       "      <th>score_rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>52</td>\n",
       "      <td>2</td>\n",
       "      <td>2006-10-12</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>51</td>\n",
       "      <td>3</td>\n",
       "      <td>2006-10-12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>49</td>\n",
       "      <td>4</td>\n",
       "      <td>2006-10-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>50</td>\n",
       "      <td>4</td>\n",
       "      <td>2006-10-12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53</td>\n",
       "      <td>7</td>\n",
       "      <td>2006-10-12</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>48</td>\n",
       "      <td>9</td>\n",
       "      <td>2006-10-12</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>518</td>\n",
       "      <td>1</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>523</td>\n",
       "      <td>1</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>423</td>\n",
       "      <td>1</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>527</td>\n",
       "      <td>1</td>\n",
       "      <td>2007-02-22</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  score        date  score_rank\n",
       "0   52      2  2006-10-12           1\n",
       "1   51      3  2006-10-12           2\n",
       "2   49      4  2006-10-12           3\n",
       "3   50      4  2006-10-12           3\n",
       "4   53      7  2006-10-12           5\n",
       "5   48      9  2006-10-12           6\n",
       "6  518      1  2007-02-22           1\n",
       "7  523      1  2007-02-22           1\n",
       "8  423      1  2007-02-22           1\n",
       "9  527      1  2007-02-22           1"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# scores will be grouped and order per day created from scores table\n",
    "# and a rank will be assigned using analytic functions\n",
    "scores_query = \"\"\"\n",
    "                    SELECT id,\n",
    "                        score,\n",
    "                        EXTRACT(DATE FROM time_ts) AS date,\n",
    "                        RANK() OVER(\n",
    "                            PARTITION BY EXTRACT(DATE FROM time_ts)\n",
    "                            ORDER BY score) AS score_rank\n",
    "                    FROM `bigquery-public-data.hacker_news.stories`\n",
    "                    WHERE score >= 0   \n",
    "\"\"\"\n",
    "query_to_dataframe(scores_query).head(10)"
   ]
  }
 ],
 "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": 1
}