Weather Type in Each Country - Solution & Explanation

Q: Is Weather Type in Each Country easy or hard?

Weather Type in Each Country is classified as an Easy database problem. The difficulty comes mainly from understanding SQL aggregation and conditional logic, but the query structure—JOIN, date filter, GROUP BY, and CASE—is straightforward.

Q: Weather Type in Each Country Python/Java solution

This problem is typically solved directly in SQL rather than Python or Java. The logic involves relational joins and aggregation, which SQL handles efficiently with GROUP BY and CASE. On LeetCode and similar platforms, the expected answer is usually written in MySQL or another SQL dialect.

Q: How to solve Weather Type in Each Country in O(n)?

Filter weather records to the November 2019 date range, join them with the Countries table on country_id, and group by country_name. Use AVG(weather_state) to compute the mean temperature per country. A CASE statement maps the average value to Cold (≤15), Warm (>15 and <25), or Hot (≥25).

Q: What is the best approach for Weather Type in Each Country?

The best approach uses a SQL JOIN between Countries and Weather, filters rows to November 2019, then groups by country and computes AVG(weather_state). A CASE expression converts the average temperature into Cold, Warm, or Hot. This solution runs in O(n) time over the weather records and is the standard pattern expected in SQL interviews.

Q: Is Weather Type in Each Country asked at Google/Amazon/Meta?

Problems like this appear in SQL interview rounds at companies that test database fundamentals, including Google, Amazon, and fintech or data-focused teams. The goal is to evaluate your understanding of joins, aggregation, filtering by date, and conditional expressions.

Q: What data structure is used in Weather Type in Each Country?

This is a SQL database problem rather than a traditional data structure question. It relies on relational tables and uses SQL operations such as JOIN, GROUP BY aggregation, and CASE expressions to classify results.

Q: What is the time complexity of Weather Type in Each Country?

The query scans the filtered Weather table and performs aggregation by country. This results in O(n) time complexity where n is the number of weather records considered. Extra space is O(1) beyond the grouped output since aggregation happens during the scan.

EasyPremiumFree on FleetCodeDatabase5 min readAsked at: Point72

Practice this problem

Problem Statement

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:

Cold if the average weather_state is less than or equal 15,
Hot if the average weather_state is greater than or equal to 25, and
Warm otherwise.

Return 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.

Approach Overview

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.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

LeetCode 1294 Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 10,124 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Weather Type in Each Country easy or hard?

Weather Type in Each Country is classified as an Easy database problem. The difficulty comes mainly from understanding SQL aggregation and conditional logic, but the query structure—JOIN, date filter, GROUP BY, and CASE—is straightforward.

Weather Type in Each Country Python/Java solution

This problem is typically solved directly in SQL rather than Python or Java. The logic involves relational joins and aggregation, which SQL handles efficiently with GROUP BY and CASE. On LeetCode and similar platforms, the expected answer is usually written in MySQL or another SQL dialect.

How to solve Weather Type in Each Country in O(n)?

Filter weather records to the November 2019 date range, join them with the Countries table on country_id, and group by country_name. Use AVG(weather_state) to compute the mean temperature per country. A CASE statement maps the average value to Cold (≤15), Warm (>15 and <25), or Hot (≥25).

What is the best approach for Weather Type in Each Country?

The best approach uses a SQL JOIN between Countries and Weather, filters rows to November 2019, then groups by country and computes AVG(weather_state). A CASE expression converts the average temperature into Cold, Warm, or Hot. This solution runs in O(n) time over the weather records and is the standard pattern expected in SQL interviews.

Is Weather Type in Each Country asked at Google/Amazon/Meta?

Problems like this appear in SQL interview rounds at companies that test database fundamentals, including Google, Amazon, and fintech or data-focused teams. The goal is to evaluate your understanding of joins, aggregation, filtering by date, and conditional expressions.

What data structure is used in Weather Type in Each Country?

This is a SQL database problem rather than a traditional data structure question. It relies on relational tables and uses SQL operations such as JOIN, GROUP BY aggregation, and CASE expressions to classify results.

What is the time complexity of Weather Type in Each Country?

The query scans the filtered Weather table and performs aggregation by country. This results in O(n) time complexity where n is the number of weather records considered. Extra space is O(1) beyond the grouped output since aggregation happens during the scan.

Ready to solve this problem?

Practice Weather Type in Each Country with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyEasy

Acceptance66.8%

Approaches1

Reading time5 min

Asked at

Point72