SQL Interview Questions – Weather Type in Each Country

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 equal 15,
• Hot if the average weather_state is greater than or equal to 25, 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