Table: Countries
+---------------+---------+ | Column Name | Type | +---------------+---------+ | country_id | int | | country_name | varchar | +---------------+---------+ country_id is the primary key (column with unique values) 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 | int | | day | date | +---------------+------+ (country_id, day) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the weather state in a country for one day.
Write a solution to find the type of weather in each country for November 2019.
The type of weather is:
weather_state is less than or equal 15,weather_state is greater than or equal to 25, andReturn the result table in any order.
The result format is in the following example.
Example 1:
Input: 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 | +------------+---------------+------------+ Output: +--------------+--------------+ | country_name | weather_type | +--------------+--------------+ | USA | Cold | | Australia | Cold | | Peru | Hot | | Morocco | Hot | | China | Warm | +--------------+--------------+ 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 the 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 the average weather_state in Spain in November so we do not include it in the result table.
Problem Overview: You need to classify each country’s weather in November 2019 as Cold, Warm, or Hot. The classification depends on the average weather_state recorded in the Weather table. Join the Countries table with weather records, compute the monthly average per country, then map the result to a weather type.
Approach 1: JOIN + GROUP BY + CASE (O(n) time, O(1) extra space)
This is the most direct SQL approach. Join Countries with Weather on country_id, filter rows to the November 2019 date range, then compute AVG(weather_state) per country using GROUP BY. A CASE expression converts the average value into Cold (≤ 15), Warm (> 15 and < 25), or Hot (≥ 25). The query performs a single pass over the filtered dataset and aggregates results efficiently. This pattern combines database joins with SQL conditional logic and aggregation.
Approach 2: Subquery Aggregation then JOIN (O(n) time, O(1) extra space)
Another option aggregates weather data first. A subquery filters November rows and groups by country_id to compute the average temperature. The outer query joins this aggregated result with Countries to fetch the country name. The same CASE logic converts the average value into a weather type. This structure can improve readability when you want to isolate aggregation logic before joining additional tables.
Approach 3: Window Function Classification (O(n) time, O(1) extra space)
Using window functions, calculate AVG(weather_state) OVER (PARTITION BY country_id) after filtering for November. Each row gets the country’s average value, and a CASE statement assigns the weather type. A final SELECT DISTINCT returns one row per country. This approach demonstrates analytic functions but usually isn’t necessary for this problem since a simple GROUP BY already provides the aggregated result.
Recommended for interviews: The JOIN + GROUP BY + CASE approach is the expected solution. It shows you understand relational joins, conditional expressions, and aggregate functions. Alternative formulations using subqueries or window functions are valid but mostly stylistic; the grouped aggregation remains the simplest and most readable.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| JOIN + GROUP BY + CASE | O(n) | O(1) | Standard SQL solution for aggregation and classification per group |
| Subquery Aggregation then JOIN | O(n) | O(1) | When you want to isolate aggregation logic before joining additional tables |
| Window Function Classification | O(n) | O(1) | When practicing analytic SQL functions like AVG OVER with partitions |
LeetCode 1294 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 9,652 views views
Watch 2 more video solutions →Practice Weather Type in Each Country with our built-in code editor and test cases.
Practice on FleetCode