Problem

Table: Countries

1
2
3
4
5
6

+---------------+---------+
| 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

1
2
3
4
5
6
7

+---------------+------+
| Column Name   | Type |
+---------------+------+
| country_id    | int  |
| weather_state | int  |
| 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 to 25, and Warm otherwise. Return result table in any order.

Solution

Method 1 - Join

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT country_name,
       CASE 
            WHEN AVG(weather_state) <= 15 THEN 'Cold'
            WHEN AVG(weather_state) >= 25 THEN 'Hot'
            ELSE 'Warm' 
        END AS weather_type
FROM Weather w
JOIN Countries c USING (country_id)
WHERE MONTH(day) = 11
GROUP BY country_name;