ntsako.grants

kcv.grants_output_rank_metrics

WITH
    grants_input_metric_hstack AS (
        SELECT
            geography_level,
            iso2_country_code AS country_code,
            CAST(COALESCE(grants_usd_historical_to_date, 0) AS FLOAT) AS grants_usd_historical_to_date,
            CAST(COALESCE(grants_usd_calendar_year, 0) AS FLOAT) AS grants_usd_calendar_year,
            CAST(COALESCE(grants_count_historical_to_date, 0) AS FLOAT) AS grants_count_historical_to_date,
            CAST(COALESCE(grants_count_calendar_year, 0) AS FLOAT) AS grants_count_calendar_year,
            CAST(COALESCE(grants_usd_calendar_year_presence_weighted, 0) AS FLOAT) AS grants_usd_calendar_year_presence_weighted,
            CAST(COALESCE(grants_usd_historical_presence_weighted, 0) AS FLOAT) AS grants_usd_historical_presence_weighted,
            CAST(COALESCE(grants_count_calendar_year_ranked, 0) AS FLOAT) AS grants_count_calendar_year_ranked,
            CAST(COALESCE(grants_five_year_change, 0) AS FLOAT) AS grants_five_year_change,
            CAST(COALESCE(grants_total_ann_five_year_change, 0) AS FLOAT) AS grants_total_ann_five_year_change,
            CAST(COALESCE(grants_total_historical_five_year_change, 0) AS FLOAT) AS grants_total_historical_five_year_change
        FROM
            input_metrics),
            
    grants_input_metric_vstack AS (
        SELECT
            geography_level,
            country_code,
            STACK(10,
                    grants_usd_historical_to_date, 'grants_usd_historical_to_date',
                    grants_usd_calendar_year, 'grants_usd_calendar_year',
                    grants_count_historical_to_date, 'grants_count_historical_to_date',
                    grants_count_calendar_year, 'grants_count_calendar_year',
                    grants_usd_calendar_year_presence_weighted, 'grants_usd_calendar_year_presence_weighted',
                    grants_usd_historical_presence_weighted, 'grants_usd_historical_presence_weighted',
                    grants_count_calendar_year_ranked, 'grants_count_calendar_year_ranked',
                    grants_five_year_change, 'grants_five_year_change',
                    grants_total_ann_five_year_change, 'grants_total_ann_five_year_change',
                    grants_total_historical_five_year_change, 'grants_total_historical_five_year_change') AS (metric_value, metric)
        FROM 
            grants_input_metric_hstack),
    
    grants_input_metrics_ranks AS (
        SELECT
            geography_level,
            country_code,
            metric,
            PERCENT_RANK() OVER (
                PARTITION BY geography_level, metric
                ORDER BY metric_value) AS p_rank
        FROM 
            grants_input_metric_vstack),
    
    presence AS (
        SELECT
            geography_level,
            country_code,
            ROUND(
                PERCENT_RANK() OVER (
                    PARTITION BY geography_level
                    ORDER BY AVG(p_rank))*10, 2) AS metric_value,
            'grantee_presence' AS metric
        FROM
            grants_input_metrics_ranks
        WHERE
            metric IN ('grants_usd_historical_to_date', 'grants_usd_calendar_year', 
                        'grants_usd_calendar_year_presence_weighted', 'grants_usd_historical_presence_weighted', 
                        'grants_count_historical_to_date')
        GROUP BY geography_level, country_code),
        
    growth AS (
        SELECT
            geography_level,
            country_code,
            ROUND(
                PERCENT_RANK() OVER (
                    PARTITION BY geography_level
                    ORDER BY AVG(p_rank))*10, 2) AS metric_value,
            'grantee_growth' AS metric
        FROM
            grants_input_metrics_ranks
        WHERE
            metric IN ('grants_five_year_change')
        GROUP BY geography_level, country_code),

    leadership AS (
        SELECT
            geography_level,
            country_code,
            ROUND(
                PERCENT_RANK() OVER (
                    PARTITION BY geography_level
                    ORDER BY AVG(p_rank))*10, 2) AS metric_value,
            'grants_leadership' AS metric
        FROM
            grants_input_metrics_ranks
        WHERE
            metric IN ('grants_total_ann_five_year_change', 'grants_total_historical_five_year_change', 
                        'grants_count_historical_to_date', 'grants_count_calendar_year')
        GROUP BY geography_level, country_code)
        

SELECT * FROM presence
UNION ALL
SELECT * FROM growth
UNION ALL
SELECT * FROM leadership

SELECT
    geography_level,
    country_code,
    country_name,
    sub_continent,
    continent,
    grantee_presence,
    grantee_growth,
    grants_leadership
FROM
    kcv.all_output_rank_metrics