Skip to content

1294. Weather Type in Each Country 👍

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT
  country_name,
  (
    CASE
      WHEN AVG(Weather.weather_state * 1.0) <= 15.0 THEN 'Cold'
      WHEN AVG(Weather.weather_state * 1.0) >= 25.0 THEN 'Hot'
      ELSE 'Warm'
    END
  ) AS weather_type
FROM Countries
INNER JOIN Weather
  USING (country_id)
WHERE day BETWEEN '2019-11-01' AND '2019-11-30'
GROUP BY 1;