# $1412. 查找成绩处于中游的学生
# https://leetcode-cn.com/problems/find-the-quiet-students-in-all-exams/

# SQL架构
Create table If Not Exists Student (student_id int, student_name varchar(30));
Create table If Not Exists Exam (exam_id int, student_id int, score int);
Truncate table Student;
insert into Student (student_id, student_name) values ('1', 'Daniel');
insert into Student (student_id, student_name) values ('2', 'Jade');
insert into Student (student_id, student_name) values ('3', 'Stella');
insert into Student (student_id, student_name) values ('4', 'Jonathan');
insert into Student (student_id, student_name) values ('5', 'Will');
Truncate table Exam;
insert into Exam (exam_id, student_id, score) values ('10', '1', '70');
insert into Exam (exam_id, student_id, score) values ('10', '2', '80');
insert into Exam (exam_id, student_id, score) values ('10', '3', '90');
insert into Exam (exam_id, student_id, score) values ('20', '1', '80');
insert into Exam (exam_id, student_id, score) values ('30', '1', '70');
insert into Exam (exam_id, student_id, score) values ('30', '3', '80');
insert into Exam (exam_id, student_id, score) values ('30', '4', '90');
insert into Exam (exam_id, student_id, score) values ('40', '1', '60');
insert into Exam (exam_id, student_id, score) values ('40', '2', '70');
insert into Exam (exam_id, student_id, score) values ('40', '4', '80');

# Write your MySQL query statement below
select
    tmp.student_id,
    s.student_name
from
    (
        select
            exam_id,
            student_id,
            score,
            if(
                dense_rank() over(
                    partition by exam_id
                    order by
                        score desc
                ) = 1,
                1,
                0
            ) as rk_desc,
            if(
                dense_rank() over(
                    partition by exam_id
                    order by
                        score
                ) = 1,
                1,
                0
            ) as rk_asc
        from
            Exam
    ) tmp
    left join Student s on tmp.student_id = s.student_id
group by
    tmp.student_id,
    # sql_mode=only_full_group_by
    s.student_name
having
    sum(tmp.rk_desc) = 0
    and sum(tmp.rk_asc) = 0
order by
    tmp.student_id;

# clean-up
drop table Student;
drop table Exam;