Leetcode. 1294. 不同国家的天气类型(非常经典的order by +where限制分组查询）

select c.country_name,
    case when avg(w.weather_state)<=15 then "Cold"
         when avg(w.weather_state)<25 then "Warm"
         else "Hot" end weather_type
    from countries c
    inner join 
    weather w 
    on c.country_id =w.country_id
    where left(w.day,7)="2019-11"
    group by 1