Find the Quiet Students in All Exams — LeetCode 1412 Python Solution

Table: Student +---------------------+---------+ | Column Name | Type | +---------------------+---------+ | student_id | int | | student_name | varchar | +---------------------+---------+ student_id is the primary key (column with unique values) for this table. student_name is the name of the student.

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+
student_id is the primary key (column with unique values) for this table.
student_name is the name of the student.

import duckdb
import pandas as pd

def solution(student: pd.DataFrame, exam: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Student", student)
    con.register("Exam", exam)
    return con.execute("""WITH
    T AS (
        SELECT
            student_id,
            RANK() OVER (
                PARTITION BY exam_id
                ORDER BY score
            ) AS rk1,
            RANK() OVER (
                PARTITION BY exam_id
                ORDER BY score DESC
            ) AS rk2
        FROM Exam
    )
SELECT student_id, student_name
FROM
    T
    JOIN Student USING (student_id)
GROUP BY 1
HAVING SUM(rk1 = 1) = 0 AND SUM(rk2 = 1) = 0
ORDER BY 1;""").df()