with __dbt__cte__base as (
SELECT
*,
country_of_import::varchar || '_'::varchar || commodity::varchar || '_IMPORT_'::varchar || year::varchar
AS sk_context_of_import
FROM supply_chains
WHERE TRUE
AND country_of_import != country_of_production -- no domestic flows
AND volume IS NOT NULL
AND scale IS NOT NULL
AND commodity_deforestation_risk IS NOT NULL
AND commodity_emissions_risk IS NOT NULL
AND exporter_group_id IS NOT NULL
AND importer_group_id IS NOT NULL
), __dbt__cte__metrics_by_context as (
SELECT
sk_context_of_import,
country_of_import,
commodity,
"year",
-- array_agg(
-- DISTINCT country_of_production
-- ) AS countries_of_production,
-- array_agg(DISTINCT product_type) AS product_types,
-- array_agg(DISTINCT scale) AS scales,
-- array_agg(DISTINCT "version") AS "versions",
sum(volume) AS volume,
-- how to distinguish zeros from nulls? other metrics too
sum(commodity_deforestation_risk) AS commodity_deforestation_risk,
sum(commodity_emissions_risk) AS commodity_emissions_risk,
sum(fob) AS fob,
--- ... other metrics here
count(DISTINCT exporter_group_id) AS exporter_groups_count,
count(DISTINCT importer_group_id) AS importer_groups_count
-- metrics about deforestation concentration? how much is concentrated in top 10 exporters? etc.
FROM __dbt__cte__base
GROUP BY 1, 2, 3, 4
ORDER BY 1, 2, 3, 4
), __dbt__cte__metrics_by_exporter as (
SELECT
sk_context_of_import,
exporter_group,
sum(volume) AS volume,
sum(commodity_deforestation_risk) AS commodity_deforestation_risk
FROM __dbt__cte__base
GROUP BY 1, 2
), __dbt__cte__exporter_ranking as (
SELECT
sk_context_of_import,
exporter_group,
volume,
commodity_deforestation_risk,
0::real as volume_pct,
-- volume / nullif(
-- sum(volume) OVER(PARTITION BY sk_context_of_import), 0
-- ) AS volume_pct,
rank() OVER(
PARTITION BY sk_context_of_import ORDER BY volume DESC NULLS LAST
) AS volume_rank,
0::real AS commodity_deforestation_risk_pct,
--commodity_deforestation_risk / nullif(
-- sum(
-- commodity_deforestation_risk
-- ) OVER(PARTITION BY sk_context_of_import),
-- 0
--) AS commodity_deforestation_risk_pct,
rank() OVER(
PARTITION BY
sk_context_of_import
ORDER BY commodity_deforestation_risk DESC NULLS LAST
) AS commodity_deforestation_risk_rank
FROM __dbt__cte__metrics_by_exporter
), __dbt__cte__top_exporters as (
SELECT
sk_context_of_import,
sum(
CASE WHEN volume_rank <= 5 THEN volume_pct ELSE 0 END
) AS volume_top_5_pct,
sum(
CASE
WHEN
commodity_deforestation_risk_rank <= 5 THEN commodity_deforestation_risk_pct
ELSE 0
END
) AS commodity_deforestation_risk_top_5_pct
FROM __dbt__cte__exporter_ranking
GROUP BY 1
)
SELECT
metrics_by_context.*,
top_exporters.volume_top_5_pct,
top_exporters.commodity_deforestation_risk_top_5_pct
FROM __dbt__cte__metrics_by_context metrics_by_context
LEFT JOIN __dbt__cte__top_exporters top_exporters
ON
metrics_by_context.sk_context_of_import = top_exporters.sk_context_of_import;
