Problem Description –
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_stateis less than or equal
- Hot if the average
weather_stateis greater than or equal to
- Warm otherwise.
Return result table in any order.
The query result format is in the following example.
Difficulty Level – Easy
Problem Link – Weather Type
SELECT DISTINCT country_name, weather_type FROM ( SELECT w.country_id, CASE WHEN AVG(w.weather_state) <= 15 THEN 'Cold' WHEN AVG(w.weather_state) >= 25 THEN 'Hot' ELSE 'Warm' END as weather_type, c.country_name FROM Weather as w JOIN Countries as c ON w.country_id = c.country_id WHERE DATE_FORMAT(day, '%Y-%m') = '2019-11' GROUP BY 1 ) x