3 ms·
Since they didn't publish their data source, let me add a useful note: How to count the number of stars using GitHub Archive and BigQuery. Naive query: #sta
by fhoffa 10y ago
Since they didn't publish their data source, let me add a useful note: How to count the number of stars using GitHub Archive and BigQuery.
Naive query:
#standardSQL
SELECT repo.id, ANY_VALUE(repo.name) name, COUNT(*) as num_stars
FROM `githubarchive.month.2016*`
WHERE type = "WatchEvent"
GROUP BY repo.id
ORDER BY num_stars DESC
LIMIT 1000
But let's fight "star fraud". There is an easy way to register "fake" stars - if you star and unstar a project repeatedly, each time this will register as a WatchEvent on the GitHub Archive log.
Better query, removes duplicates:
#standardSQL
SELECT repo_id, ANY_VALUE(name) name, COUNT(*) as num_stars
FROM (
SELECT repo.id repo_id, ANY_VALUE(repo.name) name, actor.id
FROM `githubarchive.month.2016*`
WHERE type = "WatchEvent"
GROUP BY repo.id, actor.id
)
GROUP BY repo_id
ORDER BY num_stars DESC
LIMIT 1000
If we put all together, these are the real results:
* https://docs.google.com/spreadsheets/d/1aDlXrk3U1z5s0-1Is8KHOowJ_oSw79mzwAOPmK5LmeI/edit https://docs.google.com/spreadsheets/d/1aDlXrk3U1z5s0-1Is8KH...
Projects like 'fivethirtyeight/data' lose -864 stars (23%), going down 230 places in the ranking, while projects like 'FormidableLabs/nodejs-dashboard' lose less than 1% of their stars, going up 49 places.
When I said 'stars fraud' I'm not presuming malice, but with these star rankings we do create an incentive :).
Disclaimer: I'm Felipe Hoffa, and I work for Google Cloud http://twitter.com/felipehoffa http://twitter.com/felipehoffa
- minimaxir 10y agoHuh, using ANY_VALUE as a dedupe is pretty clever. :)
- fhoffa 10y agoThanks! The full query joining both result sets gets more interesting: #standardSQL SELECT *, real_rank-rank diff_rank FROM ( SELECT a.id, a.name, a.num_stars, b.num_stars removing_dups, b.num_stars-a.num_stars diff, ROW_NUMBER() OVER(ORDER BY a.num_stars DESC) rank, ROW_NUMBER() OVER(ORDER BY b.num_stars DESC) real_rank FROM ( SELECT repo.id, STRING_AGG(DISTINCT repo.name) name, COUNT(*) as num_stars FROM `githubarchive.month.2016*` WHERE type = "WatchEvent" GROUP BY repo.id ORDER BY num_stars DESC LIMIT 1000 ) a JOIN ( SELECT repo_id, ANY_VALUE(name) name, COUNT(*) as num_stars FROM ( SELECT repo.id repo_id, ANY_VALUE(repo.name) name, actor.id FROM `githubarchive.month.2016*` WHERE type = "WatchEvent" GROUP BY repo.id, actor.id ) GROUP BY repo_id ORDER BY num_stars DESC LIMIT 1000 ) b ON a.id=b.repo_id ORDER BY num_stars DESC ) ORDER BY real_rank-rank Since some projects change their name, but not their id, I used STRING_AGG(DISTINCT repo.name) to get all of their different names throughout the year :).
- BinaryIdiot 10y agoNice work! You should create a web app to allow anyone to run this query so I don't have to setup a SQL DB with the archive data :) (Naturally I want to check how my own project faired against star fraud)
- fhoffa 10y agoYou can try it in the next 5 minutes! https://www.reddit.com/r/bigquery/comments/3dg9le/analyzing_50_billion_wikipedia_pageviews_in_5/ https://www.reddit.com/r/bigquery/comments/3dg9le/analyzing_... BigQuery is always ready for your queries. One free terabyte of analysis each month. No credit card needed :)
- BinaryIdiot 10y agoNice sales pitch :) I might take a look a little later. Good info!
- scoot 10y ago/Disclaimer/Disclosure/ (Unless you're suggesting that working for Google Cloud means you're not qualified to comment, which I doubt! :) ) This really is the HN equivalent of /i.e./e.g./!
- fhoffa 10y agoWhoa, you're right. To prove the point to myself I went to search on all reddit comments during 2016-11 (waiting for 2016-12 now). http://imgur.com/a/W1JuE http://imgur.com/a/W1JuE The most popular "disclaimer:" was "Disclaimer: This user cannot verify whether or not this comment has been edited by /u/spez", as /r/the_donald reacted to /u/spez revelation of editing comments. October had more regular disclaimers, like "I am not ..." (most popular, 181), "I'm not a" (88), "I have no" (69), "I do not" (52). Further down, 7th place, "I work for" (30). There were less "disclosures", but when used they said "I am a" (31), "I work for" (29), "I'm not a" (25). So in an absolute count for "I work for" there were virtually the same number for both, but "I work for" is less relevant in the "disclaimer" space than the "disclosure" one. (thanks for the tip) SELECT COUNT(*) c, word, word1, word2, word3 FROM ( SELECT id, offset, word, LEAD(word) OVER(PARTITION BY id ORDER BY offset) word1, LEAD(word, 2) OVER(PARTITION BY id ORDER BY offset) word2, LEAD(word, 3) OVER(PARTITION BY id ORDER BY offset) word3 FROM ( SELECT id, SPLIT(REGEXP_REPLACE(LOWER(body), r'\s', ',')) words FROM `fh-bigquery.reddit_comments.2016_11` WHERE LOWER(body) LIKE '%disclaimer%' ) a, a.words AS word WITH OFFSET offset ) WHERE word='disclaimer:' GROUP BY 2,3,4,5 ORDER BY c DESC LIMIT 30
- StavrosK 10y agoWait, what are fake stars?
- wyclif 10y agoIt's just a handy way of referring to starring activity that doesn't represent how many GitHub users have intentionally starred the repository; for instance, repeatedly starring and unstarring a repo will register them as an event and therefore be counted in the these results.