1294 Weather Type in Each Country

Description

Table: Countries

• country_id is the primary key (column with unique values) for this table.
• Each row of this table contains the ID and the name of one country.

Table: Weather

• (country_id, day) is the primary key (combination of columns with unique values) for this table.
• Each row of this table indicates the weather state in a country for one day.

Problem Statement

Write a solution 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 the result table in any order.

The result format is in the following example.

Example 1:

Input:

• Countries table:

• Weather table:

Output:

Explanation:

• 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 the 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 the average weather_state in Spain in November so we do not include it in the result table.

Solution

In this problem, the output requires us to return country_name with average weather in string format based on criteria defined in the problem. So, we need information from both tables Weather and Countries. Therefore, we do need to join by country_id field.

We also need to filter the weather results for 2019-11 which you can do using various methods but I have used DATE_FORMAT(day, '%Y-%m') = '2019-11'. Once you perform these two things, you can have a query like this.

1SELECT c.country_name, w.weather_state
2    FROM Weather w
3    JOIN Countries c
4    ON c.country_id = w.country_id
5    WHERE DATE_FORMAT(w.day, '%Y-%m') = '2019-11'

This query returns all rows where date is from November 2019.

The next task is to find the average temperature by country. This is a grouping operation with AVG() function. Once you’ve found the average per country for November 2019, you need to compare that weather_state with given conditions to define the weather in three categories as Hot, Warm or Cold. This can be done using CASE WHEN clause like this.

1CASE
2    WHEN AVG(w.weather_state) <= 15 THEN 'Cold'
3    WHEN AVG(w.weather_state) >= 25 THEN 'Hot'
4    ELSE 'Warm' END AS weather_type

The final solution looks like this.

 1SELECT 
 2    c.country_name, 
 3    CASE
 4        WHEN AVG(w.weather_state) <= 15 THEN 'Cold'
 5        WHEN AVG(w.weather_state) >= 25 THEN 'Hot'
 6        ELSE 'Warm' END AS weather_type
 7    FROM Weather w
 8    JOIN Countries c
 9    ON c.country_id = w.country_id
10    WHERE DATE_FORMAT(w.day, '%Y-%m') = '2019-11'
11    GROUP BY c.country_name;