37 ms·
Thanks! The full query joining both result sets gets more interesting: #standardSQL SELECT *, real_rank-rank diff_rank FROM ( SELECT a.id, a.name, a.n
by fhoffa 10y ago
Thanks!
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 :).