SELECT *
  FROM ntsako.population_data
WHERE year = 2021

-- Run in Hue

WITH country_data AS (
   SELECT distinct iso3_country_code,
          first_value(country_area_label) over(PARTITION BY iso3_country_code)  country_name,
          iso2_country_code,
          un_continent_description      as continent,
          un_subcontinent_description   as sub_content          
     FROM ntsako.country_meta_data country
    WHERE iso3_country_code IS NOT NULL
), wb_gdp_per_capita_current AS (
     SELECT economy,
            value 
       FROM ntsako.world_bank_data_input_metrics
      WHERE series = 'NY.GDP.PCAP.PP.CD' 
        AND time = 'YR'||'2020'
), imf_gdp_per_capita_current AS (
     SELECT iso3_country_code,
            value    as gdp_per_capita_current
       FROM ntsako.imf_input_metrics
      WHERE year = '2020'
        AND weo_subject_code = 'PPPPC'
), wb_gdp_per_capita_2007 AS (
     SELECT economy,
            value
      FROM ntsako.world_bank_data_input_metrics
     WHERE series = 'NY.GDP.PCAP.PP.KD' 
       AND time = 'YR'||'2020'

), imf_gdp_per_capita_2007 AS (
     SELECT iso3_country_code,
            value as gdp_per_capita_constant
       FROM ntsako.imf_input_metrics
      WHERE year = '2020'
        AND weo_subject_code = 'NGDPRPPPPC'
), wb_population_growth AS (
     -- World Bank population growth
    SELECT economy,
           value 
      FROM ntsako.world_bank_data_input_metrics
     WHERE series = 'SP.POP.GROW'
       AND time = 'YR'||'2020'

), un_population_growth AS (
     -- UN population growth
     SELECT country_or_region   country, 
            abs(value )         value
       FROM ntsako.un_data_pop_input_metrics
      WHERE metric = 'pop_growth'
        AND 2020 BETWEEN cast(split(year, '-')[0] as int) AND cast(split(year, '-')[1] as int) - 1
), wb_population_current AS (
     -- World Bank population current
SELECT economy, 
       value
  FROM ntsako.world_bank_data_input_metrics
 WHERE series = 'SP.POP.TOTL'
   AND time = 'YR'||'2020'
), imf_input_metrics_current AS (
     -- IMF population current
SELECT iso3_country_code, 
       value * 1000000    AS population
  FROM ntsako.imf_input_metrics
 WHERE year = '2020'
   AND weo_subject_code = 'LP'
), imf_input_metrics_previous AS (
     -- IMF population previous
SELECT iso3_country_code, 
       value * 1000000  AS population
  FROM ntsako.imf_input_metrics
 WHERE year = '2019'
   AND weo_subject_code = 'LP'
), penn_world_table_input_metrics_current AS (
     -- Penn World Table population current
  SELECT country_code,
         population * 1000000                          AS population,
         current_gdp_output/(population * 1000000)     AS gdp_per_capita_current,
         real_gdp_output / (population * 1000000)      AS gdp_per_capita_constant
    FROM ntsako.penn_world_table_input_metrics
   WHERE year = '2020'
), penn_world_table_input_metrics_previous AS (
     -- Penn World Table population previous
  SELECT country_code,
         population  * 1000000 AS population
    FROM ntsako.penn_world_table_input_metrics
   WHERE year = '2019'
), un_population_current AS (
     -- UN population current
  SELECT country_or_region    AS country,
         value * 1000000      AS value
    FROM ntsako.un_data_pop_input_metrics un_data 
   WHERE year = '2020'
     AND variant = 'Medium'
     AND metric = 'population'
), un_population_previous AS (
     -- UN population previous
  SELECT country_or_region    AS country,
         value * 1000000      AS value
    FROM ntsako.un_data_pop_input_metrics un_data 
   WHERE year = '2019'
     AND variant = 'Medium'
     AND metric = 'population'
),wb_percentage_using_internet AS (
     -- World Bank percentage using internet
   SELECT economy,
          value
    FROM ntsako.world_bank_data_input_metrics
     WHERE series = 'IT.NET.USER.ZS'
       AND time = 'YR'||'2020'
), wb_population AS (
     -- World Bank population current - previous     
     SELECT curr.economy,
          curr.value                as population_thousands,
          growth.value              as population_growth_rate,
          internet.value            as percent_using_internet,
          gdp_current.value         as gdp_per_capita_current,
          gdp_2007.value            as gdp_per_capita_constant
     FROM wb_population_current curr,
          wb_population_growth growth,
          wb_percentage_using_internet internet,
          wb_gdp_per_capita_2007 gdp_2007,
          wb_gdp_per_capita_current gdp_current
     WHERE curr.economy = growth.economy
       AND growth.economy = internet.economy
       AND growth.economy = gdp_2007.economy
       AND growth.economy = gdp_current.economy
), un_population AS (
     -- UN population current - previous
     SELECT curr.country,
            curr.value    as population_thousands,
            growth.value  as population_growth_rate
       FROM un_population_current curr,
            un_population_growth  growth
      WHERE curr.country = growth.country
), imf_population AS (
     -- IMF population current - previous
     SELECT curr.iso3_country_code,
            curr.population * 1000000                  as population_thousands,
           (curr.population/prev.population) * 1000000 as population_growth_rate,
            gdp_current.gdp_per_capita_current,
            gdp_2007.gdp_per_capita_constant
     FROM imf_input_metrics_current curr,
          imf_input_metrics_previous prev,
          imf_gdp_per_capita_2007 gdp_2007,
          imf_gdp_per_capita_current gdp_current
     WHERE curr.iso3_country_code = prev.iso3_country_code
       AND prev.iso3_country_code = gdp_2007.iso3_country_code
       AND prev.iso3_country_code = gdp_current.iso3_country_code
), penn_world_table_population AS (
     -- Penn World Table population current - previous
     SELECT curr.country_code,
          curr.population                         as population_thousands,
          (curr.population/prev.population)       as population_growth_rate,
          curr.gdp_per_capita_current,
          curr.gdp_per_capita_constant
     FROM penn_world_table_input_metrics_current curr,
          penn_world_table_input_metrics_previous prev
     WHERE curr.country_code = prev.country_code
), wb_population_rank AS (

  SELECT economy                                             AS country_code,
         population_thousands,
         population_growth_rate,
         population_thousands * (1 + population_growth_rate) AS population_presence,
         percent_using_internet,
         gdp_per_capita_current,
         gdp_per_capita_constant
    FROM wb_population

), un_population_rank AS (
  SELECT country,
         population_thousands,
         population_growth_rate,
         population_thousands * (1 + population_growth_rate) AS population_presence
    FROM un_population
), imf_population_rank AS (
  SELECT iso3_country_code,
         population_thousands,
         population_growth_rate,
         population_thousands * (1 + population_growth_rate) AS population_presence,
         gdp_per_capita_current,
         gdp_per_capita_constant
    FROM imf_population
), penn_world_table_population_rank AS (
  SELECT country_code,
         population_thousands,
         population_growth_rate,
         population_thousands * (1 + population_growth_rate) AS population_presence,
         gdp_per_capita_current,
         gdp_per_capita_constant
    FROM penn_world_table_population
), wikipedia_editors AS (
 SELECT country_code,wikipedia
  FROM ntsako.geoeditor_input_metrics_pivot
 WHERE metric = 'monthly_bins'
   AND year = 2021
), average_editors AS (
  SELECT country_code,
         editorship_monthly,
         monthly_distinct_editors
    FROM ntsako.geoeditor_online_input_metrics
   WHERE year = 2021
)
--INSERT OVERWRITE TABLE ${destination_table} PARTITION (year='2021')
SELECT country.iso3_country_code,
       coalesce(pop.population_thousands,un_pop.population_thousands, pwt_pop.population_thousands, imf_pop.population_thousands)            AS population_thousands,
       coalesce(pop.population_growth_rate, un_pop.population_growth_rate, pwt_pop.population_growth_rate, imf_pop.population_growth_rate)   AS population_growth_rate, 
       wedit.wikipedia,
       avg_cal_year.editorship_monthly,
       avg_cal_year.monthly_distinct_editors,
       coalesce(pop.population_presence, un_pop.population_presence, pwt_pop.population_presence, imf_pop.population_presence)               AS population_presence,
       pop.percent_using_internet,
       coalesce(pop.gdp_per_capita_current, pwt_pop.gdp_per_capita_current, imf_pop.gdp_per_capita_current)                                        AS gdp_per_capita_current,
       coalesce(pop.gdp_per_capita_constant, pwt_pop.gdp_per_capita_constant, imf_pop.gdp_per_capita_constant)                                           AS gdp_per_capita_constant
  FROM country_data country
  LEFT JOIN wb_population_rank pop ON (country.iso3_country_code = pop.country_code)
  LEFT JOIN wikipedia_editors wedit ON (country.iso2_country_code = wedit.country_code)
  LEFT JOIN average_editors avg_cal_year ON (country.iso2_country_code = avg_cal_year.country_code)
  LEFT JOIN un_population_rank un_pop ON (lower(country.country_name) = lower(un_pop.country))
  LEFT JOIN imf_population_rank imf_pop ON (country.iso3_country_code = imf_pop.iso3_country_code)
  LEFT JOIN penn_world_table_population_rank pwt_pop ON (country.iso3_country_code = pwt_pop.country_code);

SELECT *
  FROM ntsako.population_data_input_metrics
 WHERE year = 2021