My main message: Don’t stop where I stopped. If there’s any area that’s interesting for you — please take these queries and tools, and dig deeper into the data. I’ll be happy to help.

Queries

Continent stats:

#standardSQL

SELECT continent, SUM(stars) stars

, ROUND(SUM(stars)/COUNT(DISTINCT country_code), 1) avg_stars_per_country

, ROUND(SUM(users)/COUNT(DISTINCT country_code), 1) users_per_country

FROM (

SELECT country_code, COUNT(*) stars

, COUNT(DISTINCT actor.login) AS users

FROM `githubarchive.year.2016` a

JOIN `ghtorrent-bq.ght_2017_01_19.users` b

ON a.actor.login=b.login

WHERE country_code IS NOT null

AND a.type='WatchEvent'

GROUP BY 1

HAVING stars>5

) a

JOIN `gdelt-bq.extra.countryinfo` c

ON a.country_code=LOWER(c.iso)

GROUP BY 1

ORDER BY stars DESC

African countries stats:

#standardSQL

SELECT country, users, stars, ROUND(stars/users, 1) stars_per_user

FROM (

SELECT country_code, COUNT(*) stars

, COUNT(DISTINCT actor.login) AS users

FROM `githubarchive.year.2016` a

JOIN `ghtorrent-bq.ght_2017_01_19.users` b

ON a.actor.login=b.login

WHERE country_code IS NOT null

AND a.type='WatchEvent'

GROUP BY 1

HAVING stars>5

) a

JOIN `gdelt-bq.extra.countryinfo` c

ON a.country_code=LOWER(c.iso)

WHERE continent='AF'

ORDER BY users DESC

African countries by Stack Overflow users

SELECT country,SUM(users) stackoverflow_users

FROM (

SELECT location, COUNT(*) users

FROM `bigquery-public-data.stackoverflow.users` a

WHERE EXTRACT(YEAR FROM last_access_date)>=2016

GROUP BY 1

) a

CROSS JOIN (SELECT * FROM `gdelt-bq.extra.countryinfo` WHERE continent='AF') b

WHERE ENDS_WITH(LOWER(a.location), LOWER(b.country))

GROUP BY 1

ORDER BY 2 DESC

The main article:

See also:

Note: In the GitHub queries we can only see the country of a GitHub user if GHTorrent has been able to parse it from each user’s profile. Check out yours on https://github.com/settings/profile.