Project Employees III — LeetCode 1077 Python Solution

Table: Project +-------------+---------+ | Column Name | Type | +-------------+---------+ | project_id | int | | employee_id | int | +-------------+---------+ (project_id, employee_id) is the primary key (combination of columns with unique values) of this table. employee_id is a foreign key (reference column) to Employee table.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| project_id  | int     |
| employee_id | int     |
+-------------+---------+
(project_id, employee_id) is the primary key (combination of columns with unique values) of this table.
employee_id is a foreign key (reference column) to Employee table.
Each row of this table indicates that the employee with employee_id is working on the project with project_id.

import duckdb
import pandas as pd

def solution(project: pd.DataFrame, employee: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Project", project)
    con.register("Employee", employee)
    return con.execute("""WITH
    T AS (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY project_id
                ORDER BY experience_years DESC
            ) AS rk
        FROM
            Project
            JOIN Employee USING (employee_id)
    )
SELECT project_id, employee_id
FROM T
WHERE rk = 1;""").df()