The Number of Seniors and Juniors to Join the Company — LeetCode 2004 Python Solution

Table: Candidates +-------------+------+ | Column Name | Type | +-------------+------+ | employee_id | int | | experience | enum | | salary | int | +-------------+------+ employee_id is the column with unique values for this table. experience is an ENUM (category) type of values ('Senior', 'Junior').

+-------------+------+
| Column Name | Type |
+-------------+------+
| employee_id | int  |
| experience  | enum |
| salary      | int  |
+-------------+------+
employee_id is the column with unique values for this table.
experience is an ENUM (category) type of values ('Senior', 'Junior').
Each row of this table indicates the id of a candidate, their monthly salary, and their experience.

import duckdb
import pandas as pd

def solution(candidates: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Candidates", candidates)
    return con.execute("""WITH
    s AS (
        SELECT
            employee_id,
            SUM(salary) OVER (ORDER BY salary) AS cur
        FROM Candidates
        WHERE experience = 'Senior'
    ),
    j AS (
        SELECT
            employee_id,
            IFNULL(
                SELECT
                    MAX(cur)
                FROM s
                WHERE cur <= 70000,
                0
            ) + SUM(salary) OVER (ORDER BY salary) AS cur
        FROM Candidates
        WHERE experience = 'Junior'
    )
SELECT
    'Senior' AS experience,
    COUNT(employee_id) AS accepted_candidates
FROM s
WHERE cur <= 70000
UNION ALL
SELECT
    'Junior' AS experience,
    COUNT(employee_id) AS accepted_candidates
FROM j
WHERE cur <= 70000;""").df()