Students and Examinations — LeetCode 1280 Python Solution

Table: Students +---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ student_id is the primary key (column with unique values) for this table. Each row of this table contains the ID and the name of one student in the school.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.

import duckdb
import pandas as pd

def solution(students: pd.DataFrame, subjects: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Students", students)
    con.register("Subjects", subjects)
    con.register("Examinations", examinations)
    return con.execute("""SELECT student_id, student_name, subject_name, COUNT(e.student_id) AS attended_exams
FROM
    Students
    JOIN Subjects
    LEFT JOIN Examinations AS e USING (student_id, subject_name)
GROUP BY 1, 3
ORDER BY 1, 3;""").df()