materialized view Antognazza

CREATE MATERIALIZED VIEW clrtap_nec_unfccc.running_total_and_target (
    year,
    country_code,
    pollutant_name,
    sector_code,
    emissions,
    target,
    running_total)
AS
SELECT source.year,
    source.country_code,
    source.pollutant_name,
    source.sector_code,
    source.emissions,
    target.target,
    source.running_total
FROM (
    SELECT clrtap_nfr09_gf.year,
            clrtap_nfr09_gf.country_code,
            clrtap_nfr09_gf.pollutant_name,
            clrtap_nfr09_gf.emissions,
            clrtap_nfr09_gf.sector_code,
            sum(clrtap_nfr09_gf.emissions) OVER (PARTITION BY
                clrtap_nfr09_gf.year, clrtap_nfr09_gf.country_code, clrtap_nfr09_gf.pollutant_name
    ORDER BY clrtap_nfr09_gf.emissions DESC) AS running_total
    FROM clrtap_nec_unfccc.clrtap_nfr09_gf
    WHERE clrtap_nfr09_gf.sector_code::text !~~ 'NATIONAL%'::text AND
        clrtap_nfr09_gf.sector_code::text !~~ 'ADJ%'::text AND clrtap_nfr09_gf.emissions > 0::double precision
    ORDER BY clrtap_nfr09_gf.emissions DESC
    ) source,
    (
    SELECT clrtap_nfr09_gf.year,
            clrtap_nfr09_gf.country_code,
            clrtap_nfr09_gf.pollutant_name,
            sum(clrtap_nfr09_gf.emissions) * 0.8::double precision AS target,
            sum(clrtap_nfr09_gf.emissions) AS total
    FROM clrtap_nec_unfccc.clrtap_nfr09_gf
    WHERE clrtap_nfr09_gf.sector_code::text !~~ 'NATIONAL%'::text AND
        clrtap_nfr09_gf.sector_code::text !~~ 'ADJ%'::text AND clrtap_nfr09_gf.emissions > 0::double precision
    GROUP BY clrtap_nfr09_gf.year, clrtap_nfr09_gf.country_code,
        clrtap_nfr09_gf.pollutant_name
    ORDER BY clrtap_nfr09_gf.year, clrtap_nfr09_gf.country_code,
        clrtap_nfr09_gf.pollutant_name
    ) target
WHERE source.year::text = target.year::text AND source.country_code::text =
    target.country_code::text AND source.pollutant_name::text = target.pollutant_name::text
ORDER BY source.year, source.country_code, source.pollutant_name, source.emissions DESC