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