text: 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);