---
sidebar_position: 3
title: Air Quality
description: Sample data from the WHO Ambient Air Quality Database to use with DuckDB and MotherDuck
---

import EmbeddedDive from '@site/src/components/EmbeddedDive';
import SQLExampleEditor from '@site/src/components/SQLExampleEditor';

## Explore the data

Interactive dashboard built on the WHO air quality dataset. Use it as a starting point for your own [Dives](/key-tasks/ai-and-motherduck/dives/).

<EmbeddedDive
  diveId="dd4b9615-d668-4755-b564-880d2509f6b5"
  title="WHO Ambient Air Quality"
  height="700px"
/>

## About the dataset

The [WHO Ambient Air Quality Database](https://www.who.int/publications/m/item/who-ambient-air-quality-database-(update-2023)) (6th edition, released in **May 2023**) compiles annual mean concentrations of nitrogen dioxide (NO2) and particulate matter (PM10, PM2.5) from ground measurements across over 8600 human settlements in more than 120 countries. This data, updated every 2-3 years since **2011**, primarily represents city or town averages and is used to monitor the Sustainable Development Goal Indicator 11.6.2, Air quality in cities.

To read from the `sample_data` database, please refer to [attach the sample datasets database](./datasets.mdx)

## Example queries

### Annual city air quality rating

This query assesses the average annual air quality in different cities per year based on WHO guidelines. It calculates the average concentrations of PM2.5, PM10, and NO2, then assigns an air quality rating of 'Good', 'Moderate', or 'Poor'. 'Good' indicates all pollutants are within WHO recommended levels, 'Poor' indicates all pollutants exceed WHO recommended levels, and 'Moderate' refers to any other scenario. The results are grouped and ordered by city and year.


<SQLExampleEditor>{`
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;
`}</SQLExampleEditor>

### Yearly average pollutant concentrations of a city

This query calculates the yearly average concentrations of PM2.5, PM10, and NO2 in a given city, here `Berlin`.

<SQLExampleEditor>{`
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;
`}</SQLExampleEditor>

## Schema

|    column_name     | column_type | null | key | default | extra |
|--------------------|-------------|------|-----|---------|-------|
| who_region         | VARCHAR     | YES  |     |         |       |
| iso3               | VARCHAR     | YES  |     |         |       |
| country_name       | VARCHAR     | YES  |     |         |       |
| city               | VARCHAR     | YES  |     |         |       |
| year               | BIGINT      | YES  |     |         |       |
| version            | VARCHAR     | YES  |     |         |       |
| pm10_concentration | BIGINT      | YES  |     |         |       |
| pm25_concentration | BIGINT      | YES  |     |         |       |
| no2_concentration  | BIGINT      | YES  |     |         |       |
| pm10_tempcov       | BIGINT      | YES  |     |         |       |
| pm25_tempcov       | BIGINT      | YES  |     |         |       |
| no2_tempcov        | BIGINT      | YES  |     |         |       |
| type_of_stations   | VARCHAR     | YES  |     |         |       |
| reference          | VARCHAR     | YES  |     |         |       |
| web_link           | VARCHAR     | YES  |     |         |       |
| population         | VARCHAR     | YES  |     |         |       |
| population_source  | VARCHAR     | YES  |     |         |       |
| latitude           | FLOAT       | YES  |     |         |       |
| longitude          | FLOAT       | YES  |     |         |       |
| who_ms             | BIGINT      | YES  |     |         |       |


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `POST https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

Payload:

```json
{
  "page_path": "/getting-started/sample-data-queries/air-quality/",
  "page_title": "Air Quality",
  "text": "<the user's feedback, max 2000 characters>",
  "source": "<optional identifier for your interface, for example 'claude.ai' or 'chatgpt'>"
}
```

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed payloads, and `429` when rate-limited.