3 ms·
So that others can play with the data, here's a reverse engineering of the BigQuery OP used to create the leaderboard: #standardSQL SELECT domain,
by minimaxir 9y ago
So that others can play with the data, here's a reverse engineering of the BigQuery OP used to create the leaderboard:
#standardSQL
SELECT
domain,
COUNT(*) AS num_posts,
perc_75,
AVG(score) AS avg_score,
(AVG(score) + 2*perc_75) * LOG(COUNT(*)) AS calc_score
FROM (
SELECT
REGEXP_REPLACE(NET.HOST(url), 'www.', '') AS domain,
score,
PERCENTILE_CONT(score,
0.75) OVER (PARTITION BY REGEXP_REPLACE(NET.HOST(url), 'www.', '')) AS perc_75
FROM
`bigquery-public-data.hacker_news.full`
WHERE
type = 'story'
AND url IS NOT NULL )
GROUP BY
domain,
perc_75
ORDER BY
calc_score DESC
Top 10000 results: https://docs.google.com/spreadsheets/d/1Z9atmizTAPkgFiBte2eQiQgxEAiMyMB7Q99fzMzfIJs/edit?usp=sharing https://docs.google.com/spreadsheets/d/1Z9atmizTAPkgFiBte2eQ...
(it's apparently not a perfect match since there appears to be a minimum # of posts requirement for domains [e.g. without that requirement, https://news.ycombinator.com/from?site=pardonsnowden.org https://news.ycombinator.com/from?site=pardonsnowden.org is #3], which should be added to the description of the leaderboard)
- dkroy 9y agoNot going to lie. The first thing I checked in this site was for yours. You made the cut for the 1 year timespan. Congrats!
- refrigerator 9y agoI think that's pretty much it, though my domain regex is much uglier! Yeah you're right about a minimum # of posts cutoff — I set it to 25. Forgot to add this to the description but have added it now. Thanks!