How to query GHTorrent's (SQL-like language) for country/city/users number/repositories number?

Question

I've read a lot of documentation on GitHub data retrieval and viewed Github user commits/pushes data via BiGQuery but failed to find city, country fields and failed to find any description on such queries. Though there is information that such queries are theoretically possible - https://gist.github.com/kelewis/4bc942f9433c41b1ddb6/. But this requires Azure lake (??) . Does anyone know how to query Github data via /My_SQL/Postgres/BigQuery/, or any python (Jupiter) SQL like functions - for example pandas DataFrames with "country" and "city" fields?

Smth like this example .......................

SELECT city, country, SUM(number) AS total FROM ghtorrent-bq.ght.commits GROUP BY country, region ORDER BY total DESC LIMIT 10000 ....... Thanx!

Smth like this example .......................

SELECT city, country, SUM(number) AS total FROM ghtorrent-bq.ght.commits GROUP BY country, region ORDER BY total DESC LIMIT 10000 ....... Thanx!

user_5221944 · Answer

below should be good starting point for you (BigQuery Standard SQL)

SELECT city, country_code, count(*) AS total 
FROM `ghtorrent-bq.ght.commits` AS c
JOIN `ghtorrent-bq.ght.users` AS u
ON c.Committer_Id = u.id
WHERE NOT u.fake 
GROUP BY country_code, city 
ORDER BY total DESC 
LIMIT 10000

How to query GHTorrent's (SQL-like language) for country/city/users number/repositories number?

1 Answers