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.
alt Hacker News
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.