SQL Interview Question | Solving Complex SQL Questions | WINDOW Functions | CTE

In this video we solve an SQL Interview question using the concept of WINDOW function and CTE.

DDL to create and insert data for the two tables are given below:-

--student table
/*
create table student(
student_id int,
student_name varchar(100)
)
--exam table
create table exam(
exam_id int,
student_id int,
score int
)
*/

/*
insert into student(student_id,student_name)
values(1,'George'),
(2,'Jade'),
(3,'Stella'),
(4,'Jonathan'),
(5,'Will')

insert into exam(exam_id,student_id,score)
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)

*/

#data_project_hub
#dataprojecthub
#sqlinterviewquestionsandanswers
#sqlqueries
#sqlinterviewquestions

[varunas]

Thank you for sharing!

here's my attempt on SQL server:


with max_min as (select *
(select *,
MAX(score) over(partition by exam_id order by exam_id) [Max score],
MIN(score) over(partition by exam_id order by exam_id) [Min score]
exam) s1
where case when score > [Min score] and score < [Max score] then 'Y' else 'N' end = 'Y'),

exam_no as (select student_id, COUNT(student_id) [num exams]
from exam
group by student_id)

select distinct s.student_id, student_name
from max_min m
join exam_no e on m.student_id = e.student_id
join student s on s.student_id = m.student_id
where [num exams] >= 1

[sakshinaik]

Very well explained plz bring some more SQL interview questions

[mattmatt]

WITH exam_scores AS (
SELECT exam_id, MIN(exam_score) as min_score, MAX(exam_score) as max_score
FROM student
WHERE exam_id IS NOT NULL
GROUP BY exam_id
)
SELECT s.student_id, s.student_name
FROM student s
JOIN exam_scores e ON s.exam_id = e.exam_id
WHERE s.exam_score > e.min_score AND s.exam_score < e.max_score
ORDER BY s.student_id;

[dheemanjain]

select distinct(A.student_name), A.student_id from
(select exam_id, student_name, e.student_id, score, max(e.score) over(partition by e.exam_id order by exam_id) as maximum, min(e.score) over(partition by e.exam_id order by exam_id) as minimum from exam e left join student s on e.student_id=s.student_id) as A
where A.score<A.maximum and A.score>A.minimum

[HARSHRAJ-gpve]

with cte as(
select exam_id, MAX(score) as max_scored, MIN(score) as min_scored FROM exam GROUP BY exam_id
), cte1 as(
select exam.*, max_scored, min_scored FROM cte JOIN exam ON cte.exam_id=exam.exam_id
), cte2 as(
select student_id,
COUNT(CASE when score!=max_scored and score!=min_scored THEN 1 ELSE NULL END) AS X1
FROM cte1 GROUP BY student_id HAVING x1>0
)
select cte2.student_id, student_name FROM cte2 JOIN student ON