Leetcode-1412 Hard SQL Problem | Find the Quiet Students in All Exams | Data Analytics

In this video we will discuss leetcode problem 1412 : Find the Quiet Students in All Exams. We will discuss a step by step solution. scroll down for scripts.

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

scripts:
create table students
(
student_id int,
student_name varchar(20)
);
insert into students values
(1,'Daniel'),(2,'Jade'),(3,'Stella'),(4,'Jonathan'),(5,'Will');

create table exams
(
exam_id int,
student_id int,
score int);

insert into exams values
(10,1,70),(10,2,80),(10,3,90),(20,1,80),(30,1,70),(30,3,80),(30,4,90),(40,1,60)
,(40,2,70),(40,4,80);

#sql #leetcode #dataengineer

[perumala]

I have tried like below

with hig_low_score as ( Select *,
Row_number() over (Partition by exam_id order by score desc) as High_rank,
Row_number() over (Partition by exam_id order by score ) as low_rank from exams)


select Distinct e.Student_id from exams e
where e.student_id not in (Select student_id from hig_low_score where High_rank=1 or low_rank=1)
order by e.Student_id asc

[damoncaffrey]

My approach to this question

select *,
max(score) over(partition by exam_id) as max_score,
min(score) over(partition by exam_id) as min_score,
count(*) over(partition by student_id) as cnt from exams)

select cte.student_id, student_name from cte join students
on cte.student_id = students.student_id
where min_score < score and score < max_score
group by student_id having max(cnt) = count(*)

[shivammishra-mkjp]

Hey Ankit
I attempted this ques. like this please check

with cte as (
select e.*, s.student_name,
min(score) over(partition by exam_id order by score asc) as min_marks,
max(score) over(partition by exam_id order by score desc) as max_marks
from exams e inner join students s on e.student_id = s.student_id
)
select distinct student_id, student_name from cte
where student_id not in (
select student_id from cte
where score = min_marks or score = max_marks )

[propel]

Simple Solution

with temp1 as (

select *, row_number()over(partition by exam_id order by score) as rankingg from exams

),
temp2 as (
select exam_id, student_id, rankingg, min(rankingg)over(partition by exam_id) as minn, max(rankingg)over(partition by exam_id) as maxx

from temp1
group by exam_id, rankingg, student_id
), temp3 as (
select * from temp2 where rankingg = minn or rankingg = maxx
), temp4 as (
select distinct student_id from exams where student_id not in (select student_id from temp3)
)
select a.student_name from students a join temp4 b on a.student_id = b.student_id

[mantisbrains]

select distinct student_id from exams where student_id not in
(select distinct student_id from
(select * from exams)p
join
(select exam_id, min(score) over (partition by exam_id) as min_marks,
max(score) over (partition by exam_id) as max_marks from
(select s.student_id, student_name, exam_id, score from
(select * from students )s
join
(select * from exams )e
on s.student_id = e.student_id)k)u
on p.exam_id = u.exam_id
where score in (min_marks, max_marks))

Thanks, Ankit!

[akashgupta]

Simple approach in PgSQL

with ak as(select *,
dense_rank() over(partition by exam_id order by score desc) low
, dense_rank() over(partition by exam_id order by score) high,
count(student_id) over(partition by exam_id) cn
from exams
)
, bk as(
select exam_id, student_id
from ak where low=cn or high=cn)

select student_id, student_name from
students
where student_id in (select distinct student_id from exams)
and student_id not in (select distinct student_id from bk)

[neelanshunisingh]

select student_id from (
select *,
count(case when score>l_score and score <h_score then student_id else null end)over(partition by student_id) as Quite
from (
select *, min(score)over(partition by exam_id) as l_score
, max(score)over(partition by exam_id) as h_score
, count(exam_id)over(partition by student_id) as cnt
from exams
) as t ) as t2
where Quite=cnt
group by student_id

[Artouple]

with cte as (
select a.*, b.student_name,
dense_rank() over(partition by exam_id order by score asc) as low,
dense_rank() over(partition by exam_id order by score desc) as high
from exams a
inner join students b
on a.student_id = b.student_id)

select student_name from cte
group by student_name
having min(low) <> 1 and min(high) <> 1;

[raghuveersangashetty]

My approach to the problem:
with min_max_scores
as(
select exam_id, max(score) as max_score, min(score) as min_score
from exams
group by exam_id
)
select e.exam_id, e.student_id, e.score
from min_max_scores mm
join exams e
on mm.exam_id = e.exam_id
and (e.score > mm.min_score and e.score < mm.max_score);

[saralavasudevan]

Hi Ankit great solve! My approach was the following:
with mycte as
(
select x.student_id, s.student_name, count(x.student_id) as total_count
from
(
select *,
max(score) over(partition by exam_id ) as max_score,
min(score) over(partition by exam_id ) as min_score
from exams
)as x
join students as s on x.student_id = s.student_id
where x.score!= x.max_score and x.score!= x.min_score
group by x.student_id, s.student_name
having count(x.student_id) > 1
)

select student_id, student_name from mycte

[anshumansrivastava]

Hi Ankit,
This is my approach

with cte1 as(
select *, min(score) over(partition by exam_id order by score) as first_val,
max(score) over(partition by exam_id order by score desc) as last_val

from exams)
, cte2 as(
select *, case when (score <>first_val and score <> last_val) then 1 else null end as final_count from cte1
order by 2)

select student_id from cte2
group by student_id
having

[vanshhans]

Nice question ankit😄.
Here is my solution:
with cte1 as(
select *, DENSE_RANK() over(partition by exam_id order by score) as rn,
count(student_id) over(partition by exam_id) as cnt
from exam),
cte2 as (
select student_id, case when rn!=1 and rn !=cnt then 1 else 0 end as eligible_students
from cte1
)
select cte2.student_id, min(student.student_name) as student_name
from cte2
inner join student on
group by cte2.student_id
having

[rishavvv]

select * from exams;
with base as(
select *,
rank() over(partition by exam_id order by score) as rnk
from exams), base1 as(
select *, case when rnk=2 then 1 else 0 end as quiet_flag from base), base2 as(
select student_id, sum(quiet_flag) as cnt
from base1
group by student_id), base3 as(
select count(distinct exam_id) as exam_count, student_id from exams
group by student_id )
select distinct b.student_id, s.student_name from base2 as b join base3 as c on b.cnt=c.exam_count join students as s on b.student_id=s.student_id

[apurvasaraf]

select student_id
from (select exam_id, student_id, score,
max(score) over(partition by exam_id )as x,
min(score) over(partition by exam_id)as m
from exams) h group by student_id
having sum(case when score=x or score =m then 1 else 0 end)=0

[RishamHarsimranSingh]

with cte1 as (
select *, min(score) over(partition by exam_id) as min_score,
max(score) over(partition by exam_id) as max_score
from exams
), minmax as(
select exam_id, student_id, score,
case when score in (min_score, max_score) then 0 else 1 end as score_flag
from cte1
), students as (
select student_id
from minmax
group by student_id
having sum(score_flag) = count(student_id)
)select s.student_id, st.student_name
from students s
inner join students_table st on s.student_id = st.student_id

[siddheshkalgaonkar]

Hi Ankit, just a suggestion. When we are dealing with a larger dataset we need to filter out the data that we aren't going to deal with like in this case student_id = 3 is not required. We will remove it using join and then we can proceed ahead with our logic.

with filter_data as (
select
a.exam_id,
a.student_id,
b.student_name,
a.score
from
exams a
join students b on a.student_id = b.student_id
),
display_results as (
select
exam_id,
student_id,
student_name,
score
from
filter_data
order by
exam_id desc
),
min_score as (
select
exam_id,
student_id,
student_name,
score,
min(score) over(
partition by exam_id
order by
score
) min_score
from
display_results
order by
exam_id desc
),
max_score as (
select
exam_id,
student_id,
student_name,
score,
min_score,
max(score) over(
partition by exam_id
order by
score desc
) max_score
from
min_score
order by
exam_id
),
flag as (
select
*,
case
when score = min_score
or score = max_score then 1
else 0
end as flag
from
max_score
),
group_by_id as (
select
student_id
from
flag
group by
student_id
having
sum(flag) = 0
)
select
*
from
group_by_id -- ignore select * for now. Was in hurry to complete it.

[addhyasumitra]

My approach:
with CTE as (
select *,
max(score) Over(Partition by exam_id order by score desc) as max_score,
min(score) Over(Partition by exam_id order by score) as min_score
from exams)
select CTE.student_id
from CTE left JOIN students as s
on CTE.student_id=s.student_id
GROUP BY CTE.student_id
HAVING MAX(case when score=max_score OR score=min_score then 1 else 0 end) =0

[ananyaarangarajan]

with cte as
(
select student_name, e.*, count(exam_id) over(partition by student_id) as no_of_exams_given,
max(score) over(partition by exam_id) as highest_score, min(score) over(partition by exam_id) as lowest_score
from students s inner join exams e on s.student_id = e.student_id
order by student_id),
ctee as (
select student_id, student_name, count(student_id) as c
from cte
where score <> highest_score and score <> lowest_score
group by student_id, student_name)
select student_id, student_name from ctee where c>1

[praveensinghrathore]

MYSQL SOLUTION:
with students_atleast_1_exam as (select e.student_id, student_name, exam_id, score from exams e left join students s on e.student_id = s.student_id)
, table_with_minMaxScore as (select *, min(score) over(partition by exam_id) as min_score,
max(score) over(partition by exam_id) as max_score from students_atleast_1_exam)
, final_table as (select STUDENT_ID, STUDENT_NAME, sum(case when score = min_score or score = max_score then 1 else 0 end) as quiet_flag from table_with_minMaxScore GROUP BY 1, 2)
SELECT STUDENT_ID, STUDENT_NAME FROM final_table WHERE QUIET_FLAG = 0;

[pandeyRaman]

pgsql-

with c as
(select exam_id, max(score), min(score)
from exams
group by exam_id)

select * from students where student_id not in(
select student_id from exams e full join
c on e.exam_id=c.exam_id
and ( e.score=c.min or e.score=c.max)
where max is not null)
and student_id in (
select distinct student_id from exams)