Team Scores in Football Tournament — LeetCode 1212 Python Solution

Table: Teams +---------------+----------+ | Column Name | Type | +---------------+----------+ | team_id | int | | team_name | varchar | +---------------+----------+ team_id is the column with unique values of this table. Each row of this table represents a single football team.

+---------------+----------+
| Column Name   | Type     |
+---------------+----------+
| team_id       | int      |
| team_name     | varchar  |
+---------------+----------+
team_id is the column with unique values of this table.
Each row of this table represents a single football team.

import duckdb
import pandas as pd

def solution(teams: pd.DataFrame, matches: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Teams", teams)
    con.register("Matches", matches)
    return con.execute("""SELECT
    team_id,
    team_name,
    SUM(
        CASE
            WHEN team_id = host_team
            AND host_goals > guest_goals THEN 3
            WHEN team_id = guest_team
            AND guest_goals > host_goals THEN 3
            WHEN host_goals = guest_goals THEN 1
            ELSE 0
        END
    ) AS num_points
FROM
    Teams
    LEFT JOIN Matches ON team_id = host_team OR team_id = guest_team
GROUP BY 1
ORDER BY 3 DESC, 1;""").df()