add 1294

fishercoder1534 · fishercoder1534 · commit cd3d45dd8e36 · 2020-02-01T11:45:46.000-08:00
diff --git a/README.md b/README.md
@@ -847,6 +847,7 @@ _If you like this project, please leave me a star._ &#9733;
 |  #  |      Title     |   Solutions   | Video         | Difficulty    | Tag          
 |-----|----------------|---------------|---------------|---------------|-------------
 |1327|[List the Products Ordered in a Period](https://leetcode.com/problems/list-the-products-ordered-in-a-period/)|[Solution](../master/database/_1327.sql) || Easy |
+|1294|[Weather Type in Each Country](https://leetcode.com/problems/weather-type-in-each-country/)|[Solution](../master/database/_1294.sql) | | Easy |
 |1280|[Students and Examinations](https://leetcode.com/problems/students-and-examinations/)|[Solution](../master/database/_1280.sql) | [:tv:](https://www.youtube.com/watch?v=ThbkV4Fs7iE)| Easy |
 |1251|[Average Selling Price](https://leetcode.com/problems/average-selling-price/)|[Solution](../master/database/_1251.sql) | | Easy |
 |1241|[Number of Comments per Post](https://leetcode.com/problems/number-of-comments-per-post/)|[Solution](../master/database/_1241.sql) | | Easy |
diff --git a/database/_1294.sql b/database/_1294.sql
@@ -0,0 +1,98 @@
+--1294. Weather Type in Each Country
+--
+--Table: Countries
+--
+--+---------------+---------+
+--| Column Name   | Type    |
+--+---------------+---------+
+--| country_id    | int     |
+--| country_name  | varchar |
+--+---------------+---------+
+--country_id is the primary key for this table.
+--Each row of this table contains the ID and the name of one country.
+--
+--
+--Table: Weather
+--
+--+---------------+---------+
+--| Column Name   | Type    |
+--+---------------+---------+
+--| country_id    | int     |
+--| weather_state | varchar |
+--| day           | date    |
+--+---------------+---------+
+--(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 query result format is in the following example:
+--
+--Countries table:
+--+------------+--------------+
+--| country_id | country_name |
+--+------------+--------------+
+--| 2          | USA          |
+--| 3          | Australia    |
+--| 7          | Peru         |
+--| 5          | China        |
+--| 8          | Morocco      |
+--| 9          | Spain        |
+--+------------+--------------+
+--Weather table:
+--+------------+---------------+------------+
+--| country_id | weather_state | day        |
+--+------------+---------------+------------+
+--| 2          | 15            | 2019-11-01 |
+--| 2          | 12            | 2019-10-28 |
+--| 2          | 12            | 2019-10-27 |
+--| 3          | -2            | 2019-11-10 |
+--| 3          | 0             | 2019-11-11 |
+--| 3          | 3             | 2019-11-12 |
+--| 5          | 16            | 2019-11-07 |
+--| 5          | 18            | 2019-11-09 |
+--| 5          | 21            | 2019-11-23 |
+--| 7          | 25            | 2019-11-28 |
+--| 7          | 22            | 2019-12-01 |
+--| 7          | 20            | 2019-12-02 |
+--| 8          | 25            | 2019-11-05 |
+--| 8          | 27            | 2019-11-15 |
+--| 8          | 31            | 2019-11-25 |
+--| 9          | 7             | 2019-10-23 |
+--| 9          | 3             | 2019-12-23 |
+--+------------+---------------+------------+
+--Result table:
+--+--------------+--------------+
+--| country_name | weather_type |
+--+--------------+--------------+
+--| USA          | Cold         |
+--| Austraila    | Cold         |
+--| Peru         | Hot          |
+--| China        | Warm         |
+--| Morocco      | Hot          |
+--+--------------+--------------+
+--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.
+
+--# Write your MySQL query statement below
+select c.country_name as country_name,
+case
+when avg(weather_state) <= 15 then "Cold"
+when avg(weather_state) >= 25 then "Hot"
+else "Warm"
+end
+as weather_type
+from Countries c
+join Weather w
+on c.country_id = w.country_id
+and w.day between "2019-11-01" and "2019-11-30"
+group by c.country_id
