00:00:00

Description

Editorial

Country November Climate Banding

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

weather =

[{"country_id":2,"weather_state":15,"day":"2019-11-01"},{"country_id":2,"weather_state":12,"day":"2019-10-28"},{"country_id":2,"weather_state":12,"day":"2019-10-27"},{"country_id":3,"weather_state":-2,"day":"2019-11-10"},{"country_id":3,"weather_state":0,"day":"2019-11-11"},{"country_id":3,"weather_state":3,"day":"2019-11-12"},{"country_id":5,"weather_state":16,"day":"2019-11-07"},{"country_id":5,"weather_state":18,"day":"2019-11-09"},{"country_id":5,"weather_state":21,"day":"2019-11-23"},{"country_id":7,"weather_state":25,"day":"2019-11-28"},{"country_id":7,"weather_state":22,"day":"2019-12-01"},{"country_id":7,"weather_state":20,"day":"2019-12-02"},{"country_id":8,"weather_state":25,"day":"2019-11-05"},{"country_id":8,"weather_state":27,"day":"2019-11-15"},{"country_id":8,"weather_state":31,"day":"2019-11-25"},{"country_id":9,"weather_state":7,"day":"2019-10-23"},{"country_id":9,"weather_state":3,"day":"2019-12-23"}]

countries =

[{"country_id":2,"country_name":"USA"},{"country_id":3,"country_name":"Australia"},{"country_id":7,"country_name":"Peru"},{"country_id":5,"country_name":"China"},{"country_id":8,"country_name":"Morocco"},{"country_id":9,"country_name":"Spain"}]

Loading problem...

101

00:00:00

Description

Editorial

Country November Climate Banding

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

weather =

countries =

Country November Climate Banding

Hints

Country November Climate Banding

Hints