written by Felipe Hoffa
Apr 11. 2017
I wish we had more data — in average each African country only has 190.3 active GitHub users. [i.e. computer programming/ graphic designers/ etc] Let’s take a look (or see the original article for context):
Average number of GitHub stars and users per country in each continent 2016.
These are the top countries in Africa by GitHub active users — and the stats from Stack Overflow on BigQuery too:
On the left: Active GitHub users in African countries. On the right: Active Stack Overflow users. 2016
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
link to article @
