Students and Examinations

[문제]

https://leetcode.com/problems/students-and-examinations/description/?source=submission-ac

 

[풀이]

cross로 조인한 테이블과 미리 집계한 테이블을 이어 붙일 생각을 할 수 있어야 풀 수 있는 문제다.

판다스로 문제를 풀 때는 테이블을 무작정 이을 생각이 아니라 처리를 먼저 한 다음에 붙일 줄 알아야 한다.

 

Pandas

import pandas as pd

def students_and_examinations(students: pd.DataFrame, subjects: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    merged = pd.merge(students, subjects, how='cross')

    exam_count = examinations.groupby(['student_id', 'subject_name']).agg(attended_exams=('subject_name', 'count')).reset_index()

    result = merged.merge(exam_count, on=['student_id', 'subject_name'], how='left').sort_values(by=['student_id', 'subject_name'])
    result['attended_exams'] = result['attended_exams'].fillna(0)

    return result

 

SQL

# Write your MySQL query statement below
SELECT stu.student_id
    , student_name
    , sub.subject_name
    , COUNT(ex.subject_name) as attended_exams
FROM Students stu
JOIN Subjects sub
LEFT JOIN Examinations ex
    ON stu.student_id = ex.student_id
    AND sub.subject_name = ex.subject_name
GROUP BY 1, 2, 3
ORDER BY 1, 3