Last updated on October 9th, 2024 at 10:03 pm

This Leetcode problem Weather Type in Each Country LeetCode Solution is done in SQL.

List of all LeetCode Solution

Weather Type in Each Country LeetCode Solution

Problem Statement

country_id is the primary key for this table.
Each row of this table contains the ID and the name of one country.

(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 25 and Warm otherwise.

Return result table in any order.

The result format is in the following example.

Example 1:
Input:

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 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 average weather_state in Spain in November so we don’t include it in the result table.

1. Weather Type in Each Country LeetCode Solution MySQL

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 as w 
  left join Countries as c on c.country_id = w.country_id 
where 
  day between '2019-11-01' 
  and '2019-11-30' 
group by 
  w.country_id;