City air quality insights based on WHO data

Editor's note: Mehdi's database share includes air quality data from the World Health Organization (WHO). Use his example queries to understand pollution in particular areas. You might even try combining with the spatial extension discussed in other snippets.

Attach and select MotherDuck database
Data shared/available on MotherDuck

ATTACH 'md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6' AS sample_data;
USE sample_data;
Copy code

Annual city air quality rating based on WHO dataSQL

SELECT
    city,
    year,
    CASE
        WHEN
            AVG(pm25_concentration) <= 10
            AND AVG(pm10_concentration) <= 20
            AND AVG(no2_concentration) <= 40
            THEN 'Good'
        WHEN
            AVG(pm25_concentration) > 10
            AND AVG(pm10_concentration) > 20
            AND AVG(no2_concentration) > 40
            THEN 'Poor'
        ELSE 'Moderate'
    END AS airqualityrating
FROM
    sample_data.who.ambient_air_quality
GROUP BY
    city,
    year
ORDER BY
    city,
    year;
Copy code

Yearly average pollutant concentrations of a city (Berlin)SQL

SELECT
    year,
    AVG(pm25_concentration) AS avg_pm25,
    AVG(pm10_concentration) AS avg_pm10,
    AVG(no2_concentration) AS avg_no2
FROM sample_data.who.ambient_air_quality 
WHERE city = 'Berlin'
GROUP BY year
ORDER BY year DESC;
Copy code

Mehdi Ouazza

Edited 02/23/24

City air quality insights based on WHO data

Attach and select MotherDuck databaseData shared/available on MotherDuck

Annual city air quality rating based on WHO dataSQL

Yearly average pollutant concentrations of a city (Berlin)SQL

Attach and select MotherDuck database
Data shared/available on MotherDuck