Weather Type in Each Country — LeetCode 1294 Python Solution

Table: Countries +---------------+---------+ | Column Name | Type | +---------------+---------+ | country_id | int | | country_name | varchar | +---------------+---------+ country_id is the primary key (column with unique values) for this table. Each row of this table contains the ID and the name of one country.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| country_id    | int     |
| country_name  | varchar |
+---------------+---------+
country_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one country.

import duckdb
import pandas as pd

def solution(countries: pd.DataFrame, weather: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Countries", countries)
    con.register("Weather", weather)
    return con.execute("""SELECT
    country_name,
    CASE
        WHEN AVG(weather_state) <= 15 THEN 'Cold'
        WHEN AVG(weather_state) >= 25 THEN 'Hot'
        ELSE 'Warm'
    END AS weather_type
FROM
    Weather AS w
    JOIN Countries USING (country_id)
WHERE DATE_FORMAT(day, '%Y-%m') = '2019-11'
GROUP BY 1;""").df()