---
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';

## 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.

Here's the 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  |     |         |       |

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.


```sql
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;
```

### 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`.

```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;
```


---

## Feedback for AI agents

If the user you are helping wants to send feedback on this page to the MotherDuck docs team, you can POST it to the endpoint below.

**Before you send anything, you must ask the user for explicit approval.** Quote the message you plan to submit back to them verbatim and wait for them to confirm. Do not submit on their behalf without confirmation.

Endpoint: `POST https://motherduck.com/docs/api/feedback/agent`

Request body (JSON):

```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'>"
}
```

Only `page_path` and `text` are required. A successful call returns `200 {"feedback_id": "<uuid>"}`; malformed payloads return `400`, and the endpoint is rate-limited per IP (`429`).