LeetCode 1294 - Weather Type in Each Country

This problem asks us to determine the weather type for each country during November 2019, based on the average weatherst

leetcodeeasydatabase

LeetCode Problem 1294

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to determine the weather type for each country during November 2019, based on the average weather_state recorded in that month.

We are given two database tables:

The Countries table stores information about countries. Each row contains a unique country_id and its corresponding country_name.

The Weather table stores daily weather information for countries. Each row records a weather_state value for a specific country_id on a specific day. The combination of (country_id, day) is guaranteed to be unique, meaning there is at most one weather record per country per day.

Our task is to:

  1. Consider only weather records from November 2019.
  2. Compute the average weather state for each country during that month.
  3. Classify each country into one of three categories:
  • "Cold" if the average is less than or equal to 15
  • "Hot" if the average is greater than or equal to 25
  • "Warm" otherwise
  1. Return the country name together with its weather type.

An important detail is that countries with no weather records in November 2019 should not appear in the output. For example, Spain in the sample data has weather entries only in October and December, so it is excluded.

Since this is a database problem, we are expected to write an SQL query rather than implement an algorithm in a traditional programming language. The core challenge is correctly filtering the date range, aggregating weather values, joining with country names, and classifying the average using conditional logic.

The problem size is relatively small, but even for larger datasets, SQL aggregation functions are optimized for this type of computation. We should therefore avoid inefficient approaches that repeatedly scan tables.

Some important edge cases to keep in mind include countries with exactly one November record, countries whose average falls exactly on threshold boundaries such as 15 or 25, and countries that have no November entries at all.

Approaches

Brute Force Approach

A brute force strategy would conceptually iterate through every country and then scan the entire Weather table to collect all weather records that belong to that country and fall within November 2019. After gathering these rows, we would manually compute the average and classify the result.

This approach is correct because every country's November weather data is explicitly examined before classification. However, it is inefficient because for each country we repeatedly scan the weather table.

If there are C countries and W weather records, this leads to a time complexity of O(C × W).

Optimal Approach

The key observation is that SQL databases are designed to efficiently perform filtering, grouping, and aggregation.

Instead of repeatedly scanning the weather table, we can:

  1. Filter weather rows to only include November 2019.
  2. Group records by country_id.
  3. Compute the average weather_state using AVG().
  4. Join the grouped result with Countries to retrieve country names.
  5. Use a CASE expression to classify the average weather.

This approach is much more efficient because the weather table is scanned once, aggregated once, and then joined.

Approach Time Complexity Space Complexity Notes
Brute Force O(C × W) O(W) Repeatedly scans the weather table for each country
Optimal O(W + C) O(C) Single pass aggregation with grouping and join

Algorithm Walkthrough

  1. Filter weather records to November 2019

First, we select only rows whose day falls within November 2019. A clean way to do this is by using a date range:

day BETWEEN '2019-11-01' AND '2019-11-30'

This ensures that October and December records are excluded. 2. Group weather records by country

After filtering, we group rows by country_id.

This allows us to compute one average weather value per country. 3. Calculate the average weather state

For each country group, we compute:

AVG(weather_state)

SQL automatically handles averaging, including decimal results. 4. Classify the weather type

We use a CASE expression to map the average value into a category:

  • <= 15 → "Cold"
  • >= 25 → "Hot"
  • Otherwise → "Warm"
  1. Join with the Countries table

Since the grouped data only contains country_id, we join with Countries to retrieve the country_name. 6. Return the final result

The final output contains two columns:

  • country_name
  • weather_type

Why it works

The correctness comes from the fact that we first isolate exactly the weather records belonging to November 2019, then compute each country's average weather state over those records. Because grouping guarantees one aggregated result per country and the CASE statement exactly matches the problem's classification rules, every country with November data is assigned the correct weather type. Countries without November data naturally disappear because they produce no grouped result.

SQL Solution

SELECT
    c.country_name,
    CASE
        WHEN AVG(w.weather_state) <= 15 THEN 'Cold'
        WHEN AVG(w.weather_state) >= 25 THEN 'Hot'
        ELSE 'Warm'
    END AS weather_type
FROM Countries c
JOIN Weather w
    ON c.country_id = w.country_id
WHERE w.day BETWEEN '2019-11-01' AND '2019-11-30'
GROUP BY c.country_id, c.country_name;

After filtering to November 2019, the query joins Countries and Weather using country_id. The GROUP BY clause ensures that all November weather records for the same country are aggregated together. The AVG(weather_state) function computes the average weather value, and the CASE statement converts that numeric result into the required weather type.

An inner JOIN is used intentionally because countries without matching November weather data should not appear in the result.

Worked Examples

Example 1

Consider the November weather records only.

Country ID Weather States in November Average Classification
2 15 15.0 Cold
3 -2, 0, 3 0.333 Cold
5 16, 18, 21 18.333 Warm
7 25 25.0 Hot
8 25, 27, 31 27.667 Hot

Let us walk through the process step by step.

Step 1: Filter November records

We remove all rows outside November 2019.

Remaining rows:

country_id weather_state day
2 15 2019-11-01
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
8 25 2019-11-05
8 27 2019-11-15
8 31 2019-11-25

Step 2: Group by country

We collect values by country_id.

country_id grouped values
2 [15]
3 [-2, 0, 3]
5 [16, 18, 21]
7 [25]
8 [25, 27, 31]

Step 3: Compute averages and classify

  • USA → 15 / 1 = 15 → "Cold"
  • Australia → (-2 + 0 + 3) / 3 = 0.333 → "Cold"
  • China → (16 + 18 + 21) / 3 = 18.333 → "Warm"
  • Peru → 25 / 1 = 25 → "Hot"
  • Morocco → (25 + 27 + 31) / 3 = 27.667 → "Hot"

Spain is excluded because it has no November records.

Final output:

country_name weather_type
USA Cold
Australia Cold
China Warm
Peru Hot
Morocco Hot

Complexity Analysis

Measure Complexity Explanation
Time O(W + C) One pass over weather records for filtering and grouping, plus join cost
Space O(C) Stores grouped aggregation per country

The dominant cost comes from scanning the Weather table once and grouping by country_id. The database engine internally maintains aggregate state for each country, which scales with the number of unique countries appearing in November.

Test Cases

# Example case
# USA -> Cold, Australia -> Cold, China -> Warm,
# Peru -> Hot, Morocco -> Hot

# Country with exactly boundary average = 15 -> Cold
# weather_state = [15]
# Expected: Cold

# Country with exactly boundary average = 25 -> Hot
# weather_state = [25]
# Expected: Hot

# Country with average between 15 and 25 -> Warm
# weather_state = [16, 20]
# Average = 18
# Expected: Warm

# Country with no November records
# Should not appear in result

# Country with only one November entry
# weather_state = [10]
# Expected: Cold

# Negative temperatures
# weather_state = [-5, 0, 5]
# Average = 0
# Expected: Cold
Test Why
Provided example Validates normal functionality
Average exactly 15 Confirms lower boundary is inclusive
Average exactly 25 Confirms upper boundary is inclusive
Average between thresholds Verifies "Warm" classification
No November records Ensures country is excluded
Single November entry Confirms averaging works with one row
Negative weather values Ensures averages handle negative numbers

Edge Cases

Countries with no November weather records

A common mistake is accidentally including countries that do not have weather data during November 2019. For example, Spain in the sample only has October and December entries. If a LEFT JOIN is used carelessly, Spain could appear with a NULL average.

This implementation avoids that issue by using an inner JOIN and filtering on November dates before grouping. Countries without matching November rows are automatically excluded.

Averages exactly equal to threshold values

The classification boundaries are inclusive. An average of exactly 15 must be classified as "Cold", while an average of exactly 25 must be classified as "Hot".

A buggy implementation might use strict comparisons (< 15 or > 25), producing incorrect classifications. The CASE statement explicitly uses <= 15 and >= 25, ensuring correct handling.

Countries with a single weather record

Some countries may only have one weather entry in November. A naive implementation could incorrectly assume multiple rows are required for averaging.

The AVG() function naturally handles single-row groups. For example, a country with one weather value of 25 correctly produces an average of 25, resulting in "Hot".

Negative weather values

Weather states can be negative, as shown in the Australia example. Incorrect assumptions about positivity could lead to bugs in average calculations.

SQL aggregation functions correctly handle negative numbers, so averages remain mathematically accurate without any special handling.