Country November Climate Banding

Easy

A global analytics team tracks daily climate indicators and needs a monthly country-level classification report. Table: countries country_id: unique country identifier country_name: display name of the country Table: weather country_id: country identifier for the weather reading weather_state: integer weather index for a given day day: date of the reading Task: For each country, compute the average weather_state for November 2019 and classify the country into one of three weather bands. Band rules: Cold: average weather_state <= 15 Hot: average weather_state >= 25 Warm: otherwise Output requirements: Include only countries that have at least one weather row in November 2019. Return exactly two columns: country_name, weather_type. Output order can be arbitrary. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

countries:
| country_id | country_name |
|------------|--------------|
| 2          | USA          |
| 3          | Australia    |
| 7          | Peru         |
| 5          | China        |
| 8          | Morocco      |
| 9          | Spain        |

weather:
| country_id | weather_state | day        |
|------------|---------------|------------|
| 2          | 15            | 2019-11-01 |
| 2          | 12            | 2019-10-28 |
| 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 |
| 9          | 7             | 2019-10-23 |
| 9          | 3             | 2019-12-23 |

Output

[
  {"country_name":"Australia","weather_type":"Cold"},
  {"country_name":"China","weather_type":"Warm"},
  {"country_name":"Morocco","weather_type":"Hot"},
  {"country_name":"Peru","weather_type":"Hot"},
  {"country_name":"USA","weather_type":"Cold"}
]

Explanation

Only November rows are considered. Spain has no November rows, so it is excluded.

Example 2

Input

countries:
| country_id | country_name |
|------------|--------------|
| 1          | Northland    |
| 2          | Suncape      |
| 3          | Mildoria     |

weather:
| country_id | weather_state | day        |
|------------|---------------|------------|
| 1          | 10            | 2019-11-01 |
| 1          | 20            | 2019-11-02 |
| 2          | 24            | 2019-11-01 |
| 2          | 26            | 2019-11-02 |
| 3          | 18            | 2019-11-01 |
| 3          | 20            | 2019-11-02 |

Output

[
  {"country_name":"Mildoria","weather_type":"Warm"},
  {"country_name":"Northland","weather_type":"Cold"},
  {"country_name":"Suncape","weather_type":"Hot"}
]

Explanation

Northland averages 15 (Cold), Suncape averages 25 (Hot), and Mildoria averages 19 (Warm).

Example 3

Input

countries:
| country_id | country_name |
|------------|--------------|
| 10         | Borealis     |
| 20         | Equatoris    |

weather:
| country_id | weather_state | day        |
|------------|---------------|------------|
| 10         | 7             | 2019-10-20 |
| 10         | 8             | 2019-12-05 |
| 20         | 30            | 2019-12-09 |

Output

[]

Explanation

No country has weather rows in November 2019.

Constraints

0 <= number of rows in countries <= 700
0 <= number of rows in weather <= 1800
1 <= country_id <= 1000000000
-100 <= weather_state <= 100
country_id is unique in countries
(country_id, day) is unique in weather
day values are valid dates in YYYY-MM-DD format
Use only rows where day is in November 2019
Return exactly two columns: country_name, weather_type
Return only countries with at least one November row
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: country_name, weather_type

countries: | country_id | country_name | |------------|--------------| | 2 | USA | | 3 | Australia | | 7 | Peru | | 5 | China | | 8 | Morocco | | 9 | Spain | weather: | country_id | weather_state | day | |------------|---------------|------------| | 2 | 15 | 2019-11-01 | | 2 | 12 | 2019-10-28 | | 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 | | 9 | 7 | 2019-10-23 | | 9 | 3 | 2019-12-23 |

[ {"country_name":"Australia","weather_type":"Cold"}, {"country_name":"China","weather_type":"Warm"}, {"country_name":"Morocco","weather_type":"Hot"}, {"country_name":"Peru","weather_type":"Hot"}, {"country_name":"USA","weather_type":"Cold"} ]

countries: | country_id | country_name | |------------|--------------| | 1 | Northland | | 2 | Suncape | | 3 | Mildoria | weather: | country_id | weather_state | day | |------------|---------------|------------| | 1 | 10 | 2019-11-01 | | 1 | 20 | 2019-11-02 | | 2 | 24 | 2019-11-01 | | 2 | 26 | 2019-11-02 | | 3 | 18 | 2019-11-01 | | 3 | 20 | 2019-11-02 |

countries: | country_id | country_name | |------------|--------------| | 10 | Borealis | | 20 | Equatoris | weather: | country_id | weather_state | day | |------------|---------------|------------| | 10 | 7 | 2019-10-20 | | 10 | 8 | 2019-12-05 | | 20 | 30 | 2019-12-09 |