21 lines
1018 B
SQL
21 lines
1018 B
SQL
-- dbt Gold Industrial Emission Compliance & Load Mart
|
|
SELECT
|
|
region_zone,
|
|
industry_sector,
|
|
COUNT(log_id) AS total_telemetry_events,
|
|
COUNT(DISTINCT source_id) AS active_chimneys_count,
|
|
ROUND(AVG(so2_mg_nm3), 2) AS avg_so2_concentration,
|
|
ROUND(AVG(nox_mg_nm3), 2) AS avg_nox_concentration,
|
|
ROUND(AVG(pm_dust_mg_nm3), 2) AS avg_pm_concentration,
|
|
ROUND(SUM(so2_mass_rate_kg_h) / 1000.0, 3) AS total_so2_emission_ton,
|
|
ROUND(SUM(nox_mass_rate_kg_h) / 1000.0, 3) AS total_nox_emission_ton,
|
|
ROUND(SUM(pm_mass_rate_kg_h) / 1000.0, 3) AS total_pm_emission_ton,
|
|
COUNT(CASE WHEN compliance_status = 'EXCEEDING_LIMIT' THEN 1 END) AS total_violations_count,
|
|
ROUND(
|
|
(COUNT(CASE WHEN compliance_status = 'COMPLIANT' THEN 1 END)::NUMERIC / NULLIF(COUNT(log_id), 0)) * 100.0,
|
|
2
|
|
) AS compliance_rate_percent,
|
|
CURRENT_TIMESTAMP AS mart_refreshed_at
|
|
FROM {{ ref('silver_cems_emission_enriched') }}
|
|
GROUP BY region_zone, industry_sector
|
|
ORDER BY total_violations_count DESC, total_so2_emission_ton DESC |