1280. Students and Examinations.md

1280. Students and Examinations

Question Link

Pandas Solution

import pandas as pd

def students_and_examinations(students: pd.DataFrame, subjects: pd.DataFrame, examinations: pd.DataFrame) -> pd.DataFrame:
    a=pd.merge(
        students,subjects,how='cross'
    )
    a=a.sort_values(['student_id','student_name'])
    
    b=examinations.groupby(['student_id', 'subject_name']).agg(
        attended_exams=('subject_name','count')
    ).reset_index()

    c=pd.merge(
        a,b,how='left',on=['student_id', 'subject_name']
    ).fillna(0).sort_values(['student_id', 'subject_name'])
    return c[['student_id', 'student_name', 'subject_name', 'attended_exams']]

SQL Solution v1

# Write your MySQL query statement below
select s.student_id, s.student_name, s2.subject_name, count(e.student_id) as attended_exams
from Students s
join Subjects s2
left join Examinations e
on s.student_id=e.student_id and s2.subject_name=e.subject_name
group by s.student_id, s2.subject_name
order by s.student_id, s2.subject_name

SQL Solution v2

# Write your MySQL query statement below、
select t1.student_id, t1.student_name, t1.subject_name, ifnull(t2.attended_exams,0) as attended_exams
from (select s1.student_id, s1.student_name, s2.subject_name
from Students s1 join Subjects s2 order by s1.student_id, s2.subject_name)
as t1
left join
(select student_id, subject_name, count(*) as attended_exams
from Examinations group by student_id, subject_name) t2
on t1.student_id = t2.student_id and t1.subject_name = t2.subject_name
order by t1.student_id, t1.subject_name