Hacker News Data Exploration

I have the Hacker News data in the form of Parquet files. It is parsed with my hn-data-fetcher project.

I will be using DuckDB to explore the Hacker News data. I found it much faster than SQLite. Both in term of performance and ease of use as I don’t need to do indexing on the table to make it workable.

import duckdb
conn = duckdb.connect()
conn.sql("CREATE VIEW hn AS SELECT * FROM read_parquet('~/hn-data-fetcher/data_parquet/*/*/*.parquet')")
count = conn.sql("SELECT COUNT(*) FROM hn").fetchone()[0]
print(f"Total rows: {count}")
Total rows: 41636178

Schema of the table

conn.sql("DESCRIBE hn").show()
┌─────────────┬──────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type  │  null   │   key   │ default │  extra  │
│   varchar   │   varchar    │ varchar │ varchar │ varchar │ varchar │
├─────────────┼──────────────┼─────────┼─────────┼─────────┼─────────┤
│ id          │ BIGINT       │ YES     │ NULL    │ NULL    │ NULL    │
│ time        │ TIMESTAMP_NS │ YES     │ NULL    │ NULL    │ NULL    │
│ by          │ VARCHAR      │ YES     │ NULL    │ NULL    │ NULL    │
│ title       │ VARCHAR      │ YES     │ NULL    │ NULL    │ NULL    │
│ url         │ VARCHAR      │ YES     │ NULL    │ NULL    │ NULL    │
│ text        │ VARCHAR      │ YES     │ NULL    │ NULL    │ NULL    │
│ score       │ DOUBLE       │ YES     │ NULL    │ NULL    │ NULL    │
│ type        │ VARCHAR      │ YES     │ NULL    │ NULL    │ NULL    │
│ parent      │ DOUBLE       │ YES     │ NULL    │ NULL    │ NULL    │
│ poll        │ DOUBLE       │ YES     │ NULL    │ NULL    │ NULL    │
│ parts       │ INTEGER      │ YES     │ NULL    │ NULL    │ NULL    │
│ descendants │ DOUBLE       │ YES     │ NULL    │ NULL    │ NULL    │
│ dead        │ INTEGER      │ YES     │ NULL    │ NULL    │ NULL    │
│ deleted     │ INTEGER      │ YES     │ NULL    │ NULL    │ NULL    │
│ kids        │ BIGINT[]     │ YES     │ NULL    │ NULL    │ NULL    │
│ month       │ BIGINT       │ YES     │ NULL    │ NULL    │ NULL    │
│ year        │ BIGINT       │ YES     │ NULL    │ NULL    │ NULL    │
├─────────────┴──────────────┴─────────┴─────────┴─────────┴─────────┤
│ 17 rows                                                  6 columns │
└────────────────────────────────────────────────────────────────────┘

Number of posts and comments

Show code (27 lines)
import altair as alt

df = conn.sql("""
    SELECT 
        type,
        COUNT(*) as count
    FROM hn 
    GROUP BY type
""").df()

bar = alt.Chart(df).mark_bar().encode(
    x=alt.X('type:N', title='Content Type'),
    y=alt.Y('count:Q', title='Count'),
    text=alt.Text('count:Q', format=',')
).properties(
    title='Distribution of Hacker News Content Types',
    width=600
)
text = bar.mark_text(
    align='center',
    dy=-10  # Move text up by 10 pixels
).encode(
    text=alt.Text('count:Q', format=',')
)
chart = alt.layer(bar, text)
chart
Majority of the content are comments and posts, which is not surprising.

Number of comments and story over time

Show code (31 lines)
import pandas as pd

df = conn.sql("""
    SELECT 
        year,
        month,
        type,
        COUNT(*) as count
    FROM hn 
    WHERE type IN ('comment', 'story')
    GROUP BY year, month, type
    ORDER BY year, month
""").df()

df['date'] = pd.to_datetime(df['year'].astype(str) + '-' + df['month'].astype(str) + '-01')
chart = alt.Chart(df).mark_line().encode(
    x=alt.X('date:T', title='Date'),
    y=alt.Y('count:Q', title='Count'),
    color=alt.Color('type:N', title='Content Type'),
    tooltip=[
        alt.Tooltip('date:T', title='Date', format='%B %Y'),
        alt.Tooltip('type:N', title='Type'),
        alt.Tooltip('count:Q', title='Count', format=',')
    ]
).properties(
    title='Monthly Comments and Stories on Hacker News',
    width=800,
    height=400
)
chart

Interesting that the number of comments increase over time, while the number of stories seems to stop growing since 2012.

There is also drop of comments in starting on end of 2023.

Activity heatmap

Show code (30 lines)
df = conn.sql("""
    SELECT 
        EXTRACT(HOUR FROM (time - INTERVAL '6' HOUR)) as hour,
        EXTRACT(DOW FROM (time - INTERVAL '6' HOUR)) as day_of_week,
        COUNT(*) as count
    FROM hn
    GROUP BY hour, day_of_week
    ORDER BY hour, day_of_week
""").df()

days = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']
df['day_name'] = df['day_of_week'].map(lambda x: days[int(x)])

chart = alt.Chart(df).mark_rect().encode(
    x=alt.X('hour:O', title='Hour of Day (CST)'),
    y=alt.Y('day_name:O', title='Day of Week', sort=days),
    color=alt.Color('count:Q', title='Number of Posts',
                   scale=alt.Scale(scheme='greens')),
    tooltip=[
        alt.Tooltip('hour:O', title='Hour'),
        alt.Tooltip('day_name:O', title='Day'),
        alt.Tooltip('count:Q', title='Count', format=',')
    ]
).properties(
    title='Activity Heatmap by Hour and Day of Week',
    width=800,
    height=300
)
chart

Hour is shifted to USA Central Time (-6 hours) since a lot of Hacker News users are in USA.

Looks like the most active time is afternoon on work days. Are people using Hacker News to procrastinate after lunch break?

Every month, there are two threads on Hacker News that are dedicated to hiring. One is about who is hiring, and the other is about who wants to be hired.

Show code (55 lines)
df = conn.sql("""
    WITH hiring_posts AS (
        SELECT 
            year,
            month,
            'Who is Hiring' as type,
            SUM(descendants) as comments
        FROM hn 
        WHERE title LIKE '%Who is hiring%'
            AND type = 'story'
            AND year >= 2013
        GROUP BY year, month
        
        UNION ALL
        
        SELECT 
            year,
            month,
            'Who wants to be hired' as type,
            SUM(descendants) as comments
        FROM hn 
        WHERE title LIKE '%Who wants to be hired%'
            AND type = 'story'
            AND year >= 2013
        GROUP BY year, month
    )
    SELECT 
        year,
        month,
        type,
        comments,
        year + month/12.0 as timeline
    FROM hiring_posts
    ORDER BY year, month, type
""").df()

chart = alt.Chart(df).mark_line(point=False).encode(
    x=alt.X('timeline:Q', 
            title='Year',
            axis=alt.Axis(format='d')),  # Use 'd' format to show integers without decimals
    y=alt.Y('comments:Q', title='Comments per Thread'),
    color=alt.Color('type:N', title='Thread Type'),
    tooltip=[
        alt.Tooltip('year:Q', title='Year'),
        alt.Tooltip('month:Q', title='Month'),
        alt.Tooltip('type:N', title='Type'),
        alt.Tooltip('comments:Q', title='Comments', format='.1f')
    ]
).properties(
    title='Monthly Hiring vs Seeking Work Thread Activity',
    width=800,
    height=400
)
chart

Looking at the graph, we can see several interesting patterns:

  1. Overall trend: The “Who is hiring” threads consistently get more comments than “Who wants to be hired” threads throughout the entire period.

  2. Peak activity: There was a notable spike in activity around 2020-2021, particularly in the “Who is hiring” threads, possibly related to the tech industry changes during the COVID-19 pandemic.

  3. Recent trends: After 2021, both thread types show a decline in activity, with comment counts returning to levels similar to 2018-2019. The gap between hiring and seeking work threads has also narrowed in recent years.

  4. Seasonality: There appears to be some seasonal variation in both thread types, with regular ups and downs throughout each year, though the pattern is more pronounced in the hiring threads.

Top story of each year

Show code (21 lines)
# Get top story by score for each year
from IPython.display import HTML
df = conn.sql("""
    SELECT 
        year,
        CONCAT('<a href="https://news.ycombinator.com/item?id=', id, '" target="_blank">', title, '</a>') as title,
        score,
        by,
    FROM hn
    WHERE type = 'story'
    AND score = (
        SELECT MAX(score)
        FROM hn AS h2 
        WHERE h2.year = hn.year
        AND h2.type = 'story'
    )
    ORDER BY year DESC
""").df()

pd.set_option('display.max_colwidth', None)
display(HTML(df.to_html(escape=False, index=False)))

Top story of all time

Show code (14 lines)
df = conn.sql("""
    SELECT 
        CONCAT('<a href="https://news.ycombinator.com/item?id=', id, '" target="_blank">', title, '</a>') as title,
        score,
        by
    FROM hn
    WHERE type = 'story'
    ORDER BY score DESC
    LIMIT 10
""").df()

pd.set_option('display.max_colwidth', None)
display(HTML(df.to_html(escape=False, index=False)))
Show code (14 lines)
df = conn.sql("""
    SELECT 
        CONCAT('<a href="https://news.ycombinator.com/item?id=', id, '" target="_blank">', title, '</a>') as title,
        score,
        by
    FROM hn
    WHERE type = 'story'
    AND title LIKE 'Show HN:%'
    ORDER BY score DESC
    LIMIT 10
""").df()

pd.set_option('display.max_colwidth', None)
display(HTML(df.to_html(escape=False, index=False)))
Show code (15 lines)
df = conn.sql("""
    SELECT 
        CONCAT('<a href="https://news.ycombinator.com/item?id=', id, '" target="_blank">', title, '</a>') as title,
        score,
        by
    FROM hn
    WHERE type = 'story'
    AND title LIKE 'Ask HN:%'
    ORDER BY score DESC
    LIMIT 10
""").df()

pd.set_option('display.max_colwidth', None)
display(HTML(df.to_html(escape=False, index=False)))

Most submitted domain

Show code (25 lines)
df = conn.sql("""
    SELECT 
        REGEXP_EXTRACT(url, 'https?://([^/]+)') as domain,
        COUNT(*) as count
    FROM hn
    WHERE type = 'story'
    AND url IS NOT NULL 
    AND url != ''
    GROUP BY domain
    ORDER BY count DESC
    LIMIT 20
""").df()

chart = alt.Chart(df).mark_bar().encode(
    x=alt.X('count:Q', title='Number of submissions'),
    y=alt.Y('domain:N', sort='-x', title='Domain'),
    tooltip=['domain', 'count']
).properties(
    title='Most Submitted Domains on HN',
    width=800,
    height=400
)

chart

Most upvoted domain

Show code (27 lines)
df = conn.sql("""
    SELECT 
        REGEXP_EXTRACT(url, 'https?://([^/]+)') as domain,
        COUNT(*) as count,
        SUM(score) as total_score,
        AVG(score) as avg_score
    FROM hn
    WHERE type = 'story'
    AND url IS NOT NULL 
    AND url != ''
    GROUP BY domain
    ORDER BY total_score DESC
    LIMIT 20
""").df()

chart = alt.Chart(df).mark_bar().encode(
    x=alt.X('total_score:Q', title='Total score'),
    y=alt.Y('domain:N', sort='-x', title='Domain'),
    tooltip=['domain', 'total_score', 'count', 'avg_score']
).properties(
    title='Most Upvoted Domains on HN',
    width=800,
    height=400
)

chart

High quality submission

But then, what is usually sources that get most upvoted often vs people just spamming it?

Show code (30 lines)
df = conn.sql("""
    SELECT 
        REGEXP_EXTRACT(url, 'https?://([^/]+)') as domain,
        COUNT(*) as count,
        SUM(score) as total_score,
        AVG(score) as avg_score,
        total_score / COUNT(*) as score_per_submission
    FROM hn
    WHERE type = 'story'
    AND url IS NOT NULL 
    AND url != ''
    AND score > 0
    GROUP BY domain
    HAVING count >= 10
    ORDER BY score_per_submission DESC
    LIMIT 20
""").df()

chart = alt.Chart(df).mark_bar().encode(
    x=alt.X('score_per_submission:Q', title='Average Score per Submission'),
    y=alt.Y('domain:N', sort='-x', title='Domain'),
    tooltip=['domain', 'total_score', 'count', 'avg_score', 'score_per_submission']
).properties(
    title='Domains with Highest Average Score per Submission (min 10 submissions)',
    width=800,
    height=400
)

chart
df['domain'] = df['domain'].apply(lambda x: f'<a href="{x}" target="_blank">{x}</a>')
display(HTML(df.to_html(escape=False, index=False)))
domaincounttotal_scoreavg_scorescore_per_submission
https://www.susanjfowler.com104781.0478.100000478.100000
https://ciechanow.ski6828649.0421.308824421.308824
https://intuitiveexplanations.com124957.0413.083333413.083333
https://dkb.io124532.0377.666667377.666667
https://www.phind.com134832.0371.692308371.692308
https://learningmusic.ableton.com124422.0368.500000368.500000
https://bugs.xdavidhu.me113901.0354.636364354.636364
http://blog.rust-lang.org3411646.0342.529412342.529412
http://blog.chriszacharias.com113727.0338.818182338.818182
https://oimo.io257867.0314.680000314.680000
http://immersivemath.com123630.0302.500000302.500000
https://www.moore.org113019.0274.454545274.454545
https://tinyprojects.dev205307.0265.350000265.350000
https://tauri.studio102642.0264.200000264.200000
https://casparwre.de112862.0260.181818260.181818
https://mattferraro.dev112859.0259.909091259.909091
https://www.sofuckingagile.com102591.0259.100000259.100000
https://asahilinux.org287010.0250.357143250.357143
https://omar.website102493.0249.300000249.300000
https://www.tablix.org102492.0249.200000249.200000

🗒️ Download (notebook.ipynb)