Permalink
Find file
Fetching contributors…
Cannot retrieve contributors at this time
50 lines (48 sloc) 2.58 KB
DROP TABLE IF EXISTS language_ranks;
CREATE TABLE language_ranks AS
SELECT t1.*, t2.city_user_count, t3.country_user_count, t4.world_user_count
FROM (
SELECT LOWER(LANGUAGE) AS LANGUAGE, LOWER(country) AS country,
LOWER(city) AS city,
sum(stars) + (1.0 - 1.0/count(repositories.id)) AS score,
row_number() OVER (PARTITION BY repositories.language, users.city ORDER BY (sum(stars) + (1.0 - 1.0/count(repositories.id))) DESC) AS city_rank,
row_number() OVER (PARTITION BY repositories.language, users.country ORDER BY (sum(stars) + (1.0 - 1.0/count(repositories.id))) DESC) AS country_rank,
row_number() OVER (PARTITION BY repositories.language ORDER BY (sum(stars) + (1.0 - 1.0/count(repositories.id))) DESC) AS world_rank,
count(repositories.id) AS repository_count,
sum(stars) AS stars_count,
LOWER(users.id) AS user_id
FROM repositories
INNER JOIN users ON users.login = repositories.user_id
WHERE repositories.language IS NOT NULL AND users.organization=FALSE
GROUP BY repositories.language, city, country, users.id
) t1
LEFT OUTER JOIN (
SELECT count(DISTINCT user_id) AS city_user_count,
LOWER(repositories.language) AS LANGUAGE,
LOWER(city) AS city
FROM repositories
INNER JOIN users ON repositories.user_id = users.login
WHERE repositories.language IS NOT NULL AND users.organization=FALSE
GROUP BY repositories.language, city
) t2 ON t1.language = t2.language AND (t1.city = t2.city)
LEFT OUTER JOIN (
SELECT count(DISTINCT user_id) AS country_user_count,
LOWER(repositories.language) AS LANGUAGE,
LOWER(country) AS country
FROM repositories
INNER JOIN users ON repositories.user_id = users.login
WHERE repositories.language IS NOT NULL AND users.organization=FALSE
GROUP BY repositories.language, country
) t3 ON t1.language = t3.language AND (t1.country = t3.country)
INNER JOIN (
SELECT count(DISTINCT user_id) AS world_user_count,
LOWER(repositories.language) AS LANGUAGE
FROM repositories
INNER JOIN users ON repositories.user_id = users.login
WHERE repositories.language IS NOT NULL AND users.organization=FALSE
GROUP BY repositories.language
) t4 ON t1.language = t4.language;
CREATE INDEX language_ranks_user_id ON language_ranks USING btree (user_id);
CREATE INDEX language_ranks_city ON language_ranks USING btree (LANGUAGE, city_rank, city);
CREATE INDEX language_ranks_country ON language_ranks USING btree (LANGUAGE, country_rank, country);
CREATE INDEX language_ranks_world ON language_ranks USING btree (LANGUAGE, world_rank);