Weather Type in Each Country

Weather Type in Each Country

What is this problem about?

This is a database-focused problem where you are given two tables: Countries and Weather. You need to find the "Weather Type" for each country for a specific month (usually November 2019). The weather type is determined by the average weather state: if the average is ≤ 15, it's "Cold"; if it's ≥ 25, it's "Hot"; otherwise, it's "Warm".

Why is this asked in interviews?

SQL and Database questions are common for data-centric roles or full-stack positions. This specific problem tests your ability to perform Joins, filter data by date ranges using functions like BETWEEN or LEFT(), and use conditional logic like CASE statements or IF() within an aggregation function like AVG(). It evaluates how you handle relational data mapping.

Algorithmic pattern used

The pattern here is Relational Algebra and Aggregation. You must:

1. Join the Countries and Weather tables on the country ID.
2. Filter the weather records to include only those from the target month.
3. Group by the country name.
4. Aggregate the weather state using AVG().
5. Classify the result using a CASE statement.

Example explanation

Country: "Iceland" (ID: 1). Weather records for Nov: [5, 10, 15].

• Average = (5+10+15) / 3 = 10.
• Since 10 ≤ 15, the weather type is "Cold". Country: "India" (ID: 2). Weather records for Nov: [30, 28, 32].
• Average = 30.
• Since 30 ≥ 25, the weather type is "Hot".

Common mistakes candidates make

• Incorrect Date Filtering: Using the wrong date format or missing some days in the month.
• Null Handling: Forgetting what happens if a country has no weather records for that month (though usually the problem implies they exist).
• Rounding Errors: Not realizing that the comparison (≤ 15, ≥ 25) should be done on the calculated average, not individual records.

Interview preparation tip

Master the CASE WHEN statement in SQL. It is the equivalent of an if-else block in programming and is essential for categorizing data based on calculated metrics in reports.