Find Interview Candidates — LeetCode 1811 Python Solution

Table: Contests +--------------+------+ | Column Name | Type | +--------------+------+ | contest_id | int | | gold_medal | int | | silver_medal | int | | bronze_medal | int | +--------------+------+ contest_id is the column with unique values for this table. This table contains the LeetCode contest ID and the user IDs of the gold, silver, and bronze medalists.

+--------------+------+
| Column Name  | Type |
+--------------+------+
| contest_id   | int  |
| gold_medal   | int  |
| silver_medal | int  |
| bronze_medal | int  |
+--------------+------+
contest_id is the column with unique values for this table.
This table contains the LeetCode contest ID and the user IDs of the gold, silver, and bronze medalists.
It is guaranteed that any consecutive contests have consecutive IDs and that no ID is skipped.

import duckdb
import pandas as pd

def solution(contests: pd.DataFrame, users: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Contests", contests)
    con.register("Users", users)
    return con.execute("""WITH
    S AS (
        SELECT contest_id, gold_medal AS user_id, 1 AS type
        FROM Contests
        UNION
        SELECT contest_id, silver_medal AS user_id, 2 AS type
        FROM Contests
        UNION
        SELECT contest_id, bronze_medal AS user_id, 3 AS type
        FROM Contests
    ),
    T AS (
        SELECT
            user_id,
            (
                contest_id - ROW_NUMBER() OVER (
                    PARTITION BY user_id
                    ORDER BY contest_id
                )
            ) AS diff
        FROM S
    ),
    P AS (
        SELECT user_id
        FROM S
        WHERE type = 1
        GROUP BY user_id
        HAVING COUNT(1) >= 3
        UNION
        SELECT DISTINCT user_id
        FROM T
        GROUP BY user_id, diff
        HAVING COUNT(1) >= 3
    )
SELECT name, mail
FROM
    P AS p
    LEFT JOIN Users AS u ON p.user_id = u.user_id;""").df()