Weather Type Analysis
ASKED IN INTERVIEWWeather App
Problem Statement
You are analyzing global weather trends for the month of November 2026. Your task is to find the weather type for each country.
The weather type is determined by the average temperature of the country in that month:
- Cold: Average temperature <= 15
- Warm: 15 < Average temperature < 25
- Hot: Average temperature >= 25
Rules:
- Only consider records from November 2026 (2026-11-01 to 2026-11-30).
- Output columns: country_name, weather_type.
- Results must be sorted by country_name in ascending order.
Tests your understanding of
Joins, CASE Statement and Grouping
Input Tables
Countries
| country_id(INTEGER) | country_name(VARCHAR) |
|---|---|
| 1 | USA |
| 2 | Australia |
| 3 | Norway |
Weather
| country_id(INTEGER) | weather_state(INTEGER) | day(DATE) |
|---|---|---|
| 1 | 10 | 2026-11-01 |
| 1 | 20 | 2026-11-15 |
| 2 | 30 | 2026-11-01 |
| 3 | 5 | 2026-11-10 |
| 3 | 15 | 2026-12-01 |
Forecast_Partners
| partner_id(INTEGER) | country_id(INTEGER) | source_name(VARCHAR) |
|---|---|---|
| 101 | 1 | NOAA |
| 102 | 2 | BOM |
Expected Output
| country_name(VARCHAR) | weather_type(VARCHAR) |
|---|---|
| Australia | Hot |
| Norway | Cold |
| USA | Cold |
Tags
EasyASKED IN INTERVIEWJoinsCASE StatementGrouping
15-20 min
68%