Weather Type in Each Country - Problem
Imagine you're working for a global weather analytics company that needs to classify countries based on their November 2019 weather patterns. You have access to two databases:
📊 Countries Table: Contains country information
🌡️ Weather Table: Contains daily weather measurements
Your task is to determine the weather type for each country based on their average weather state during November 2019:
- ❄️ Cold: Average weather_state ≤ 15
- 🔥 Hot: Average weather_state ≥ 25
- 🌤️ Warm: Everything in between (15 < average < 25)
Input Tables:
| Countries | |
|---|---|
| country_id (int) | country_name (varchar) |
| Weather | ||
|---|---|---|
| country_id (int) | weather_state (int) | day (date) |
Goal: Return each country with its weather classification for November 2019.
Input & Output
example_1.sql — Basic Weather Classification
$
Input:
Countries: [(1,'USA'), (2,'Canada'), (3,'India')]
Weather: [(1,15,'2019-11-01'), (1,25,'2019-11-02'), (2,10,'2019-11-01'), (2,12,'2019-11-02'), (3,30,'2019-11-01'), (3,35,'2019-11-02')]
›
Output:
[('USA','Warm'), ('Canada','Cold'), ('India','Hot')]
💡 Note:
USA: avg(15,25) = 20 → Warm (15 < 20 < 25). Canada: avg(10,12) = 11 → Cold (11 ≤ 15). India: avg(30,35) = 32.5 → Hot (32.5 ≥ 25).
example_2.sql — Edge Case Averages
$
Input:
Countries: [(1,'Country1'), (2,'Country2')]
Weather: [(1,15,'2019-11-01'), (1,15,'2019-11-02'), (2,25,'2019-11-01'), (2,25,'2019-11-02')]
›
Output:
[('Country1','Cold'), ('Country2','Hot')]
💡 Note:
Country1: avg(15,15) = 15 → Cold (exactly 15 ≤ 15). Country2: avg(25,25) = 25 → Hot (exactly 25 ≥ 25). Boundary values are inclusive.
example_3.sql — Missing November Data
$
Input:
Countries: [(1,'USA'), (2,'Canada')]
Weather: [(1,20,'2019-10-31'), (1,20,'2019-12-01'), (2,15,'2019-11-01')]
›
Output:
[('Canada','Cold')]
💡 Note:
Only Canada has November 2019 data. USA has October and December data but no November data, so it's excluded from results. Only countries with November 2019 weather data appear in output.
Visualization
Tap to expand
Understanding the Visualization
1
📊 Join the Data
Combine country information with their weather measurements, like matching students with their test scores
2
📅 Filter Time Period
Focus only on November 2019 data, like considering only final exam scores
3
📈 Calculate Averages
Group by country and calculate average weather_state, like computing each student's GPA
4
🏷️ Apply Classification
Use CASE WHEN to assign Cold/Warm/Hot labels based on average ranges, like assigning A/B/C grades based on GPA
Key Takeaway
🎯 Key Insight: SQL's GROUP BY with aggregate functions (AVG) combined with conditional logic (CASE WHEN) allows us to classify data efficiently in a single query, avoiding the need for multiple passes or complex subqueries.
Time & Space Complexity
Time Complexity
O(n log n + m)
Where n is countries, m is weather records. JOIN and GROUP BY operations with index usage
⚡ Linearithmic
Space Complexity
O(k)
Where k is number of distinct countries, for grouping and result storage
✓ Linear Space
Constraints
- 1 ≤ Countries table rows ≤ 100
- 1 ≤ Weather table rows ≤ 1000
- weather_state values are integers between 1 and 100
- All dates are in November 2019 format: 2019-11-XX
- Each country may have multiple weather readings per day
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code