Weather Type in Each Country - Problem
Given two tables, Countries and Weather, find the type of weather in each country for November 2019.
The weather type classification is based on the average weather_state for the month:
- Cold: Average weather_state ≤ 15
- Hot: Average weather_state ≥ 25
- Warm: Average weather_state between 16 and 24 (inclusive)
Return the result table with each country and its weather type for November 2019.
Table Schema
Countries
| Column Name | Type | Description |
|---|---|---|
country_id
PK
|
int | Unique identifier for each country |
country_name
|
varchar | Name of the country |
Primary Key: country_id
Weather
| Column Name | Type | Description |
|---|---|---|
country_id
PK
|
int | Foreign key referencing Countries table |
weather_state
|
int | Weather state value for the day |
day
PK
|
date | Date of the weather record |
Primary Key: country_id, day
Input & Output
Example 1 — Multiple Countries with Different Weather Types
Input Tables:
Countries
| country_id | country_name |
|---|---|
| 2 | USA |
| 3 | Australia |
| 7 | Peru |
Weather
| country_id | weather_state | day |
|---|---|---|
| 2 | 15 | 2019-11-01 |
| 2 | 13 | 2019-11-10 |
| 2 | 12 | 2019-11-20 |
| 3 | 25 | 2019-11-02 |
| 3 | 25 | 2019-11-15 |
| 7 | 20 | 2019-11-01 |
| 7 | 18 | 2019-11-15 |
Output:
| country_name | weather_type |
|---|---|
| USA | Cold |
| Australia | Hot |
| Peru | Warm |
💡 Note:
USA has weather states [15, 13, 12] with average 13.33 ≤ 15, so it's Cold. Australia has [25, 25] with average 25 ≥ 25, so it's Hot. Peru has [20, 18] with average 19 (between 16-24), so it's Warm.
Example 2 — Country with No November 2019 Data
Input Tables:
Countries
| country_id | country_name |
|---|---|
| 1 | Canada |
| 2 | USA |
Weather
| country_id | weather_state | day |
|---|---|---|
| 1 | 10 | 2019-10-30 |
| 2 | 22 | 2019-11-15 |
Output:
| country_name | weather_type |
|---|---|
| USA | Warm |
💡 Note:
Canada has no weather data for November 2019 (only October), so it's excluded from results. USA has one record with weather_state 22, which falls in the Warm category (16-24).
Constraints
-
1 ≤ Countries.country_id ≤ 1000 -
1 ≤ Weather.weather_state ≤ 100 -
dayis a valid date in YYYY-MM-DD format - Only weather data for November 2019 should be considered
Visualization
Tap to expand
Understanding the Visualization
1
JOIN Tables
Combine Countries and Weather data
2
Filter & Group
November 2019 data, grouped by country
3
Classify
Use averages to determine weather types
Key Takeaway
🎯 Key Insight: Use JOIN with GROUP BY and CASE WHEN to classify data based on aggregated values from multiple tables
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code