I then got the idea of actually figuring out how many. Then I first wanted to try out algolia but then later, I found out about clickhouse and how it had a play and the api for playing is so simple, I am definitely gonna make more projects on top of clickhouse play for HN (seriously my mind got blown because I was assuming that the browser -> api was gonna be hard but it seriously wasn't)
Then decided to think to write a github page about it for other people as well.
Anyways, this was one of the most fun project I had. So it turns out that I personally have written 0.64 Game of thrones words in Hackernews itself.
Dang has written 11.15 Volumes equivalent to game of thrones which is actually really crazy.
When I searched dang I was shocked haha. Anyways Dang, If you are reading this, I know that we all like to talk about how moderation of HN has issues but seriously man, the amount of efforts you put in is really lovely & respectable. We all love you.
I still feel like there are some issues where people flag anything they dislike which can be frustrating and other things but that still doesn't really impact the moderation and the moderation team (dang) is pretty awesome in my opinion even if the website does have this flaw in my opinion but Hackernews is one of the best websites man!
Dang today's your day! We can discuss the issues of flagging and others some other day, Have a nice day now!
(Also a little side fact but I picked game of thrones because my name of github is SerJaimeLannister because I was watching game of thrones in my brother's dorm room once in his college room and I literally just thought one or two episodes and started watching from s4 or something and then literally the second I got home, I binge watched Game of thrones till end and then s1 s2 but I think that I haven't watched some seasons I think s3 iirc more but still I loved the show so much and I think I had lost my old github account and naming is always hard especially in programming so picked SerJaimeLannister but this is the reason why I picked the novel equivalent to be game of thrones!)
It appears to not correlate current and former user-names after a name change. HN search has that issue too. I guess there is not a way to enumerate someones former username. Maybe that is for the best.
This is pretty cool! This week I was just thinking of vibe coding something with my HN profile as well (e.g, analyze how my writing has changed over the decade-ish of being on here).
Also, 95k words written on here apparently. Cool to know haha.
Huh. In the top 1500, with approximately one GoT worth of text in ~17 years.
Also, I recognize four of the top five users as prolific commenters, but dragonwriter doesn’t ring a bell at all. Maybe they frequent all the threads that I don’t.
It’s funny how I spend so much time on HN, yet couldn’t point out a single username (that I don’t know IRL) besides dang.
This is one reason I feel an odd disconnect (anonymity?) with HN that isn’t felt on other social platforms I’ve been a part of. Those often have avatars or some other visual form of recognition that helps put a “face” to a name.
I’m not sure if that’s a good or bad thing, but I definitely think it’s intentional.
There are a handful of people I recognize, most because they’re people I knew from outside HN (and one because he has the same last name as someone I went to high school with and lives local to me and I always wonder if they’re related but I’ve never asked). But yes, the de-emphasis on user names is, I think, a good thing since it ends up being interacting with content rather than personality.
> It’s funny how I spend so much time on HN, yet couldn’t point out a single username besides dang.
That’s very true. These days, when I come across especially thoughtful comments, I’ll sometimes click through to the user’s profile afterward. I think it’s better to judge a comment on its own merits than to let implicit bias creep in based on things like “CEO,” fame, a high-karma account, and so on.
Let me help you. Words / [days since account creation] would be a far better metric to decide if you have an HN problem, and by that metric you easily surpass current account #1000.
In fact, if I make a proper leaderboard by that metric, you are #914. And #731 if we only consider accounts older than 60 days.
I did rally simple frequency analysis based on corpus source a while ago and the results were super clear, you can tell a corpus by its frequency fingerprint. I wonder if something similar to this could fingerprint bot accounts?
Was curious yet again (Please note once again that the sql query is LLM generated)
WITH
-- 1. Find the latest karma for the user
latest_user_data AS (
SELECT
id,
argMax(karma, update_time) AS current_karma
FROM hackernews_changes_profiles
WHERE lower(id) = lower('6LLvveMx2koXfwn') -- Put the username here
GROUP BY id
),
-- 2. Calculate their total word count
total_word_stats AS (
SELECT
by,
sum(length(splitByWhitespace(text))) AS total_words
FROM hackernews_history
WHERE lower(by) = lower('6LLvveMx2koXfwn') -- Put the username here
AND type = 'comment'
AND deleted = 0
GROUP BY by
)
-- 3. Join them and calculate the ratio
SELECT
k.id AS user,
k.current_karma AS karma,
w.total_words,
-- Ratio: How much karma they get for every 100 words they write
round((k.current_karma / w.total_words) * 100, 2) AS karma_per_100_words,
-- Raw Ratio
round(k.current_karma / w.total_words, 4) AS raw_ratio
FROM latest_user_data k
JOIN total_word_stats w ON lower(k.id) = lower(w.by)
user 6LLvveMx2koXfwn
karma 1288
total_words 6696
karma_per_100_words 19.24
raw_ratio 0.1924
The difference between the fact that your karma's lower/words different is that the database behind it is old from clickhouse.
Ran it on my own it's 0.0063 which makes sense because much of what I write isn't intended for pure comment-karma but rather just whatever I am thinking actually. I am still honestly just happy that this post had reached the front page of HN (looks like now its on the second page)
Finally going to sleep now. I have tried to answer to many queries/interesting things that people wanted something for. I will still see if I may have missed any & respond to them tomorrow if I might have the time tomorrow.
Ooh I cracked the top 500. I’m at about 475k words.
Took me a few tries to find my user since I wasn’t expecting the case sensitivity.
Thanks for this. Another book you could add for comparison purposes would be James Joyce’s Ulysses. Or I guess the unabridged The Stand by Stephen King would be good too.
Ooh The Stand (unabridged) is estimated at 473,000 words! I wrote The Stand in comment length. Wow.
for an account i created in june 2024, top %0.54 is a lot. I need to spend less time on HN. more than that, I need stop typing walls of text, has to be annoying to readers! :)
I'm also naturally curious about the byte count --- using the accepted standard of 5 for words to characters, and since I almost never post anything but ASCII, I've been writing approximately 1.25KB per day here; or just over 5.5MB worth of text so far. Considering that English text compresses very well, and using ~20% as a rough ratio, this means that all ~1.2M words of my comments here, compressed, would still fit on one 3.5" floppy disk.
This is so cool. I genuinely hope you try it. I remembered the "website which runs on floppy" hn post from it. Maybe you can even have a website too which shows all your Hackernews comments while being on floppy.
I genuinely hope you do it!! Please let me know (mail me) if you ever do it!
Cool! Just a thought: instead of having to query the Clickhouse cluster whenever a client clicks "View Top 1000 Leaderboard" (which could cause a lot of load), it might be useful to instead fetch the top 1000 every hour (day?) and display the top 1000 as a static list.
99 comments
[ 2.8 ms ] story [ 90.3 ms ] threadI then got the idea of actually figuring out how many. Then I first wanted to try out algolia but then later, I found out about clickhouse and how it had a play and the api for playing is so simple, I am definitely gonna make more projects on top of clickhouse play for HN (seriously my mind got blown because I was assuming that the browser -> api was gonna be hard but it seriously wasn't)
Then decided to think to write a github page about it for other people as well.
Anyways, this was one of the most fun project I had. So it turns out that I personally have written 0.64 Game of thrones words in Hackernews itself.
Dang has written 11.15 Volumes equivalent to game of thrones which is actually really crazy.
When I searched dang I was shocked haha. Anyways Dang, If you are reading this, I know that we all like to talk about how moderation of HN has issues but seriously man, the amount of efforts you put in is really lovely & respectable. We all love you.
I still feel like there are some issues where people flag anything they dislike which can be frustrating and other things but that still doesn't really impact the moderation and the moderation team (dang) is pretty awesome in my opinion even if the website does have this flaw in my opinion but Hackernews is one of the best websites man!
Dang today's your day! We can discuss the issues of flagging and others some other day, Have a nice day now!
(Also a little side fact but I picked game of thrones because my name of github is SerJaimeLannister because I was watching game of thrones in my brother's dorm room once in his college room and I literally just thought one or two episodes and started watching from s4 or something and then literally the second I got home, I binge watched Game of thrones till end and then s1 s2 but I think that I haven't watched some seasons I think s3 iirc more but still I loved the show so much and I think I had lost my old github account and naming is always hard especially in programming so picked SerJaimeLannister but this is the reason why I picked the novel equivalent to be game of thrones!)
I'm nowhere near that (~125k words), but for many of us, it's a good part of our life's corpus. :)
Also, 95k words written on here apparently. Cool to know haha.
Also, I recognize four of the top five users as prolific commenters, but dragonwriter doesn’t ring a bell at all. Maybe they frequent all the threads that I don’t.
> Global Rank > 385 / 774235
> Word Count > 509,412
> Top 0.05%
I don't know if I'm too long-winded or I comment too much or both. Good to know I'm in the top 400 regardless.
This would be pretty cool for other sites. My Reddit stats are probably way worse.
This is one reason I feel an odd disconnect (anonymity?) with HN that isn’t felt on other social platforms I’ve been a part of. Those often have avatars or some other visual form of recognition that helps put a “face” to a name.
I’m not sure if that’s a good or bad thing, but I definitely think it’s intentional.
That’s very true. These days, when I come across especially thoughtful comments, I’ll sometimes click through to the user’s profile afterward. I think it’s better to judge a comment on its own merits than to let implicit bias creep in based on things like “CEO,” fame, a high-karma account, and so on.
In fact, if I make a proper leaderboard by that metric, you are #914. And #731 if we only consider accounts older than 60 days.
Here's the data: https://pastes.io/name-words
If only any of that was useful!
On a side note though there is (maybe intentional) case sensitivity? Can't remember how hn usernames work.
WITH -- 1. Find the latest karma for the user latest_user_data AS ( SELECT id, argMax(karma, update_time) AS current_karma FROM hackernews_changes_profiles WHERE lower(id) = lower('6LLvveMx2koXfwn') -- Put the username here GROUP BY id ), -- 2. Calculate their total word count total_word_stats AS ( SELECT by, sum(length(splitByWhitespace(text))) AS total_words FROM hackernews_history WHERE lower(by) = lower('6LLvveMx2koXfwn') -- Put the username here AND type = 'comment' AND deleted = 0 GROUP BY by ) -- 3. Join them and calculate the ratio SELECT k.id AS user, k.current_karma AS karma, w.total_words, -- Ratio: How much karma they get for every 100 words they write round((k.current_karma / w.total_words) * 100, 2) AS karma_per_100_words, -- Raw Ratio round(k.current_karma / w.total_words, 4) AS raw_ratio FROM latest_user_data k JOIN total_word_stats w ON lower(k.id) = lower(w.by)
user 6LLvveMx2koXfwn karma 1288 total_words 6696 karma_per_100_words 19.24 raw_ratio 0.1924
The difference between the fact that your karma's lower/words different is that the database behind it is old from clickhouse.
Ran it on my own it's 0.0063 which makes sense because much of what I write isn't intended for pure comment-karma but rather just whatever I am thinking actually. I am still honestly just happy that this post had reached the front page of HN (looks like now its on the second page)
Finally going to sleep now. I have tried to answer to many queries/interesting things that people wanted something for. I will still see if I may have missed any & respond to them tomorrow if I might have the time tomorrow.
I know them, by tone. I read his/her take on the topic. Turns out you don't need to see any faces or body ratios of any kind to connect with people.
Thanks for keeping HN 'stable/sane'!
Took me a few tries to find my user since I wasn’t expecting the case sensitivity.
Thanks for this. Another book you could add for comparison purposes would be James Joyce’s Ulysses. Or I guess the unabridged The Stand by Stephen King would be good too.
Ooh The Stand (unabridged) is estimated at 473,000 words! I wrote The Stand in comment length. Wow.
I genuinely hope you do it!! Please let me know (mail me) if you ever do it!