Watch 3 video solutions for Weather Type in Each Country, a easy level problem involving Database. This walkthrough by Everyday Data Science has 9,652 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
| 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 |