3 ms·
Huh, using ANY_VALUE as a dedupe is pretty clever. :)
by minimaxir 10y ago
Huh, 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 :).