> For the complete documentation index, see [llms.txt](https://jaywin.gitbook.io/leetcode/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://jaywin.gitbook.io/leetcode/solutions/1294-weather-type-in-each-country.md).

# 1294. Weather Type in Each Country

<https://leetcode.com/problems/weather-type-in-each-country>

## Description

Table: `Countries`

```

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country\_id    | int     |
| country\_name  | varchar |
+---------------+---------+
country\_id is the primary key for this table.
Each row of this table contains the ID and the name of one country.
```

Table: `Weather`

```

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country\_id    | int     |
| weather\_state | varchar |
| day           | date    |
+---------------+---------+
(country\_id, day) is the primary key for this table.
Each row of this table indicates the weather state in a country for one day.
```

Write an SQL query to find the type of weather in each country for November 2019.

The type of weather is **Cold** if the average `weather_state` is less than or equal 15, **Hot** if the average `weather_state` is greater than or equal 25 and **Warm** otherwise.

Return result table in any order.

The query result format is in the following example:

```

Countries table:
+------------+--------------+
| country\_id | country\_name |
+------------+--------------+
| 2          | USA          |
| 3          | Australia    |
| 7          | Peru         |
| 5          | China        |
| 8          | Morocco      |
| 9          | Spain        |
+------------+--------------+
Weather table:
+------------+---------------+------------+
| country\_id | weather\_state | day        |
+------------+---------------+------------+
| 2          | 15            | 2019-11-01 |
| 2          | 12            | 2019-10-28 |
| 2          | 12            | 2019-10-27 |
| 3          | -2            | 2019-11-10 |
| 3          | 0             | 2019-11-11 |
| 3          | 3             | 2019-11-12 |
| 5          | 16            | 2019-11-07 |
| 5          | 18            | 2019-11-09 |
| 5          | 21            | 2019-11-23 |
| 7          | 25            | 2019-11-28 |
| 7          | 22            | 2019-12-01 |
| 7          | 20            | 2019-12-02 |
| 8          | 25            | 2019-11-05 |
| 8          | 27            | 2019-11-15 |
| 8          | 31            | 2019-11-25 |
| 9          | 7             | 2019-10-23 |
| 9          | 3             | 2019-12-23 |
+------------+---------------+------------+
Result table:
+--------------+--------------+
| country\_name | weather\_type |
+--------------+--------------+
| USA          | Cold         |
| Austraila    | Cold         |
| Peru         | Hot          |
| China        | Warm         |
| Morocco      | Hot          |
+--------------+--------------+
Average weather\_state in USA in November is (15) / 1 = 15 so weather type is Cold.
Average weather\_state in Austraila in November is (-2 + 0 + 3) / 3 = 0.333 so weather type is Cold.
Average weather\_state in Peru in November is (25) / 1 = 25 so weather type is Hot.
Average weather\_state in China in November is (16 + 18 + 21) / 3 = 18.333 so weather type is Warm.
Average weather\_state in Morocco in November is (25 + 27 + 31) / 3 = 27.667 so weather type is Hot.
We know nothing about average weather\_state in Spain in November so we don't include it in the result table. 
```

## ac

```java
```