Your queryable, public, GitHub history.

I wanted to find out what all Open Source projects I had contributed to, to post it in my company’s website.

My GitHub profile said “6,219 contributions in the last year, to X,Y,Z and 34 Other repositories”. But which all? Were there any worth bragging about?

TL;DR Use ClickHouse.

ChatGPT didn’t have any useful suggestions, apart from “Check your GitHub Profile”, “Check your Email”, “Check your local repos”, “Check a defunct aggregation tool”, duh!

But that last one did ring a bell in my head. Few weeks back, when the Open Source community was shook by the backdoor in XZ, I remember seeing a GitHub activity histogram of the bad actor somewhere in HN . I had played with that tool for a bit, and was impressed by it. Thankfully, I had it in my bookmarks, so I started playing around.

Clickhouse

Running this gives all my public activity. Every single comment, PR, issue, like, start, damn!

SELECT *
FROM   github_events
WHERE  actor_login = 'sidharthv96'
ORDER  BY file_time DESC

Looking at the columns, event_type, repo_name and action seems like what I need.

To get all the PRs I’ve ever opened in a public repo, I can use.

SELECT *
FROM   github_events
WHERE  actor_login = 'sidharthv96'
       AND event_type = 'PullRequestEvent'
       AND action <> 'closed'
ORDER  BY file_time DESC

And we can get the count of PRs in each repo with this.

SELECT repo_name,
       Count() AS prs
FROM   github_events
WHERE  actor_login = 'sidharthv96'
       AND event_type = 'PullRequestEvent'
       AND action <> 'closed'
GROUP  BY repo_name
ORDER  BY prs DESC

But there are lots of my private repos in there, which we don’t really need. So we can remove them.

 SELECT repo_name,
       Count() AS prs
FROM   github_events
WHERE  actor_login = 'sidharthv96'
       AND event_type = 'PullRequestEvent'
       AND action <> 'closed'
       AND repo_name NOT LIKE '%sidharthv96%'
GROUP  BY repo_name
ORDER  BY prs DESC

Combining it with a query to get stars of each repo (thanks ChatGPT), we finally get this, that ranks each repo I’ve ever raised a PR to, by star count.

SELECT PR.repo_name,
    IFNULL(W.stars, 0) as stars
FROM
    (
        SELECT repo_name
        FROM github_events
        WHERE actor_login = 'sidharthv96'
            AND event_type = 'PullRequestEvent'
						AND action <> 'closed'
            AND repo_name NOT LIKE '%sidharthv96%'
        GROUP BY repo_name
    ) PR
LEFT JOIN
    (
        SELECT
            repo_name,
            count() AS stars
        FROM github_events
        WHERE event_type = 'WatchEvent'
        GROUP BY repo_name
    ) W ON PR.repo_name = W.repo_name
ORDER BY stars DESC;

Final result

I did notice the star counts are not accurate, but it’s good enough to get a rough idea. At the time of writing, mermaid has 66.9k start, but it shows 41.8k in clickhouse.