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_state
is less than or equal15
, - Hot if the average
weather_state
is greater than or equal to25
, and - Warm otherwise.
Return result table in any order.
The query result format is in the following example.



Difficulty Level – Easy
Problem Link – Weather Type
Solution –
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