Hi @Ankit. Below is my try with CTE as ( select a.exam_id, a.student_id, a.score, max(a.score) over (partition by a.exam_id) as max_score, min(a.score) over (partition by a.exam_id) as min_score from exams a join students b on a.student_id=b.student_id ), --select * from CTE CTE1 as( select *, case when score =max_score or score = min_score then 'Quite' else 'NotQuite' end as 'Status' from CTE ) select student_id from CTE1 where Status='NotQuite' group by student_id having count(*)>1

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 group by student_id having max(cnt) = count(*)

select student_id from ( select *, count(case when score>l_score and score

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 student.student_id=cte2.student_id group by cte2.student_id having count(cte2.student_id)=sum(eligible_students)

You are simply amazing

Hey @Ankit, yours approach was amazing. Mine was a bit long. Below is the following code. with cte as ( select e.student_id, x.exam_id, x.score from ( select exam_id, max(score) as score from exams group by 1 union select exam_id, min(score) as score from exams group by 1) x join exams e on x.exam_id = e.exam_id and x.score = e.score order by exam_id ) select distinct a.student_id, s.student_name from exams a left join students s on s.student_id = a.student_id where a.student_id not in ( select distinct cte.student_id from cte )

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

You've made this look very simple. Thanks a lot

@ankitbansal6

Жыл бұрын

You’re welcome 😊

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 (count(student_id))=(count(final_count))

@NamanSeth1

4 ай бұрын

order by score is redundant in first last val

Awesome... what a great explanation

@ankitbansal6

2 жыл бұрын

Thank you 😊

This looks good Ankit, thanks for enhancing knowledge.

@ankitbansal6

2 жыл бұрын

🙏

Another great video!! 👍

@analyst_adithya

2 жыл бұрын

if you are interested in solving more sql questions.. try this one:kzread.info/dash/bejne/paJ7ydiCdaTclbQ.html

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);

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 )

With base as (Select exam_id, score, s.student_id, student_name, case when score > min(score) over(partition by exam_id) and score else 0 End as flag from exams as e left join students as s on e.student_id=s.student_id ) Select student_id, student_name from base group by 1,2 having min(flag)=1

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;

@rabink.5115

Жыл бұрын

your query has typo in group by, it should be group by student_name

@Artouple

Жыл бұрын

@@rabink.5115 Yup

Thank you so much and much appreciated

@ankitbansal6

Жыл бұрын

You are so welcome

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

with cte as (select *, dense_rank() over(partition by exam_id order by score) as rn from exams) select student_id from cte where rn=2 group by student_id having count(exam_id)>1

Hi Ankit my solution is with a as ( select * , ROW_NUMBER() over(partition by exam_id order by score) srn , count(student_id) over(partition by exam_id) scnt from exams) select distinct a1.student_id, s.student_name from (a a1 full join a a2 on a1.student_id=a2.student_id and a1.exam_ida2.exam_id and a1.srna2.srn) left join students s on a1.student_id=s.student_id where a1.scnt > 2 and a1.srn a1.scnt and a1.srn 1 and a2.exam_id is null

My approach with cte as( select *,rank() over(partition by exam_id order by score) as sc,count(1) over(partition by student_id) as ct from exams),r_cte as( select student_id from cte group by student_id having min(case when sc=2 then sc else 0 end )=2 ) select r.student_id,s.student_name from r_cte r inner join students s on r.student_id=s.student_id group by r.student_id,s.student_name

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

Great explanation bhai💯

@ankitbansal6

2 жыл бұрын

Thank you 😊

hi @ankit, what about the below solution: with cte as( select e.student_id as student_id,s.student_name as student_name ,exam_id, rank() over(partition by exam_id order by score) as rn from exams e inner join students s on e.student_id=s.student_id) ,cte2 as(select student_id,student_name,rn,count(exam_id) as cnt from cte group by student_id,student_name,rn) select student_id,student_name from cte2 where cnt=2 and rn=2

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.

Hi, Here is my approach: with exams_min_max as ( select e.* , max(e.score) over (partition by e.exam_id) as max_score, min(e.score) over (partition by e.exam_id) as min_score from exams e ) select distinct e.student_id from exams e where e.student_id not in (select student_id from exams_min_max emm where emm.max_score=emm.score or emm.min_score=emm.score)

Hi Ankit, select p.exam_id, p.student_id, p.student_name, p.score from (select e.exam_id,s.*,e.score,max(e.score) over (partition by exam_id) max_, min(e.score) over (partition by exam_id) min_ from students s join exams e on s.student_id = e.student_id)p where p.score > p.min_ and p.score < p.max_

with cet as ( select *,case when score=max(score) over(partition by exam_id) or score=min(score) over(partition by exam_id) then 1 else 0 end as red_flag from exams ) select student_id,sum(red_flag) from cet c inner join students s on c.student_id=s.student_id group by student_id having sum(red_flag)=0

select student_id, max(case when score=x or score =m then 1 else 0 end) as c 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

with cte as( Select s.student_id ,s.student_name, max(score) over(partition by exam_id) max_exam_mark, min(score) over(partition by exam_id) min_exam_mark, case when score > min(score) over(partition by exam_id) and score from students s inner join exams e on s.student_id = e.student_id ) Select student_id,student_name from cte group by student_id,student_name having count(flag) = sum(flag)

with cte as ( select a.student_id,score,exam_id,student_name,max(score) over(partition by exam_id) as maximum,min(score) over(partition by exam_id) as minimum from students a inner join exams b on a.student_id=b.student_id ) select student_id,student_name from cte group by student_id,student_name having sum(case when score= minimum or score=maximum then 1 else 0 end)=0

Great content ankit can we try like first finding min n max scores for each subject and then taking score to check that is not in min max table 🙌

@ankitbansal6

2 жыл бұрын

Thats what is done ✅

My approach with window fun: with cte as ( select * ,case when score = max(score) over(partition by exam_id) or score = min(score) over(partition by exam_id) then 0 else 1 end flag from students_3 inner join exams_2 using(student_id) order by student_id ) select * from cte group by flag having count(distinct flag) = 1 and max(flag)=1;

@sarvesht7299

11 ай бұрын

Can you tell me why are you taking max of flag

@ajithshetty1684

6 ай бұрын

To make sure the distinct flag found by count(distinct flag) is 1@@sarvesht7299

select student_id from (select * ,case when score = max(score) over(partition by exam_id) or score = min(score) over(partition by exam_id) then 1 else 0 end flag from exams40 )a group by student_id having max(flag)=0

create temp table uj as ( select distinct student_id from ( select *, min(score) over(partition by exam_id) as min_marks , max(score) over(partition by exam_id) as max_marks from exams ) where score = min_marks or score = max_marks); select distinct b.student_name from exams a join students b on a.student_id =b.student_id where a.student_id not in (select * from uj)

With cte as (select exam_id, Max(Score) high, min(Score) low from exams group by exam_id) ,cte2 as (select e.*,c.high,c.low from exams e inner join cte c on e.exam_id=c.exam_id) select distinct student_id from cte2 where score != high and score != low and student_id not in ( Select student_id from cte2 where score=high or score=low )

with h_scoring_std as (select distinct student_id from exams qualify max(score) over (partition by exam_id) = score ), l_scoring_std as (select distinct student_id from exams qualify max(score) over (partition by exam_id) = score ) select * from students s where exists ( select 1 from exams e where student_id not in ( select * from h_scoring_std union select * from l_scoring_std) and s.student_id = e.student_id )

My solution: with base as( select e.*, s.student_name, max(score) over(partition by e.exam_id ) as max_score, min(score) over(partition by e.exam_id ) as min_score from exams e join students s on e.student_id=s.student_id ) select distinct student_id, student_name from base GROUP BY student_id, student_name having sum(case when score=max_score or score=min_score then 1 else 0 end)=0

with examcte as(select *, max(score) over (partition by exam_id order by score rows between unbounded preceding and unbounded following) as highest, min(score) over (partition by exam_id order by score rows between unbounded preceding and unbounded following) as lowest from exams), finalcte as(select distinct student_id from examcte where score = highest or score = lowest order by student_id) select * from students where student_id in(select e.student_id from examcte e left join finalcte f on e.student_id = f.student_id where f.student_id is null)

@ 5:23 you have used CTE and Inner Join to create a table that contains min & max scores. But this can be done without doing this. We can use below code to achieve this :- select *, min(score) over(partition by exam_id) min_score, max(score) over(partition by exam_id) max_score from exams

@anantsagarjoshi130

Жыл бұрын

Yes,This one would have been simple

--Tried USING DENSE_RANK() go with cte as ( select *,dense_rank() over(partition by exam_id order by score desc ) as dn_desc, dense_rank() over(partition by exam_id order by score asc ) as dn_asc from examsQ ) select distinct s.student_id, s.student_name from cte c inner join studentsQ s on c.student_id = s.student_id where s.student_id not in ( select distinct student_id from cte where dn_desc = 1 or dn_asc = 1 )

with cte as( select *, max(score)oveR(partition by exam_id) as max_Score,min(score)over(partition by exam_id) as min_score from exams left join students using(student_id) ) select student_id from cte where student_id not in (select student_id from cte where score=max_Score or score=min_score) group by student_id

with cte as(select *, max(score) over(partition by exam_id) as max_s, min(score) over(partition by exam_id) as min_s from exams) ,ct2 as (select student_id,student_name from students where student_id not in (select distinct student_id from cte where score = max_s or score = min_s ) ) select * from ct2 where student_id in ( select distinct student_id from exams)

with cte as (select *,min(score) over(partition by exam_id) as min_score,max(score) over(partition by exam_id) as max_score, count(*) over(partition by student_id) as total_exams_appeared from exams order by exam_id,student_id), cte2 as (select * from cte where score != min_score and score!= max_score) select student_id from cte2 group by student_id having count(*) = max(total_exams_appeared) ;

with cte as ( SELECT * ,Max(score) over(partition by exam_id) as Highest ,Min(score) over(partition by exam_id) as Lowest from exams ) ,not_highest_lowest as ( select DISTINCT student_id from cte where student_id not in (select distinct student_id from cte where score=Highest or score=Lowest) ) select s.student_name,hl.student_id from students_1 s inner join not_highest_lowest hl on s.student_id=hl.student_id order by s.student_id

select student_id from ( select *,rank() over(partition by exam_id order by score desc ) as rnk, rank() over(partition by exam_id order by score asc) as rn from exams) a where rnk = rn group by student_id having count(1) >1

@Useracwqbrazy

Жыл бұрын

Mine is similar approach...where I just use row_number=2 in where clause after order by score asc in single row_number func.

with cte1 as ( select exam_id, student_id, score, min(score)over(partition by exam_id order by score) min_score, max(score)over(partition by exam_id order by score desc) max_score, count(distinct exam_id)over(partition by student_id) as total_count from exams order by student_id ) select cte1.student_id --sum(case when score>min_score and scoremin_score and score

hi Ankit, please find my solution, Since student id is already in exam table, we don't need to join even. WITH cte AS (SELECT * , max(score) over(PARTITION BY exam_id) max_score, min(score) over(PARTITION BY exam_id) min_score FROM exams) SELECT student_id -- max(case when score = max_score or score = min_score then 1 else 0 end) as red_flag FROM cte GROUP BY student_id HAVING max(CASE WHEN score = max_score OR score = min_score THEN 1 ELSE 0 END) = 0

with table1 as ( select *,min(score) over(partition by exam_id) as min_marks, max(score) over(partition by exam_id) as max_marks, case when ((score!=min(score) over(partition by exam_id)) and (score!=max(score) over(partition by exam_id))) then 0 else 1 end as red_flag from exams_1 ) select student_id as Quite_student_id,student_name as Quite_student_name from students_1 where student_id in ( select student_id from table1 group by student_id having (min(red_flag)=0 and max(red_flag)=0) )

with temp as (select exams.*, students.student_name, min(score) over(partition by exam_id) as min_marks, max(score) over(partition by exam_id) as max_marks, count(exams.student_id) over(partition by student_id) as cnt from exams join students on exams.student_id=students.student_id) ,temp1 as( select * from temp where score!=min_marks and score!=max_marks) select student_name, student_id from temp1 group by student_id,student_name having count(*)=max(cnt)

EASY Solution with cte_1 as( select *, max(score) over(partition by exam_id order by student_id rows between unbounded PRECEDING and unbounded FOLLOWING ) as max_score, min(score) over(partition by exam_id order by student_id) as min_score from exams) select * from cte_1 WHERE student_id not in (select student_id from cte_1 where score = max_score or score = min_score)

with CTE as (Select students_.student_id as student_id , min(case when (rn_desc!=1 and rn_asc!=1) then 1 else 0 end) as notation from (Select *, rank() over(partition by exam_id order by score DESC) as rn_desc, rank() over(partition by exam_id order by score ASC) as rn_asc from exams) as exam_score Join (Select * from students where student_id in (Select distinct student_id from exams)) as students_ on students_.student_id = exam_score.student_id group by students_.student_id ) Select student_id from CTE where notation =1

unique solution: with cte as (select *, FIRST_VALUE(score) over(partition by exam_id order by score desc) as hig, FIRST_VALUE(score) over(partition by exam_id order by score asc) as low from exams40) select cte.student_id,s.student_name from cte INNER JOIN students40 s on cte.student_id=s.student_id group by cte.student_id,s.student_name having max(case when score = hig OR score = low then 1 else 0 end)=0

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

Hi Ankit I tried this one some what short answer than your query please look at once and give any suggestions WITH A AS( SELECT E_ID,STUDENTS.ID,SCORE, MAX(SCORE) OVER(PARTITION BY E_ID)MAX_SCORE, MIN(SCORE) OVER(PARTITION BY E_ID)MIN_SCORE FROM STUDENTS JOIN EXAM ON STUDENTS.ID=EXAM.ID ) SELECT ID ,MAX(CASE WHEN SCORE IN( MAX_SCORE ,MIN_SCORE)THEN 1 ELSE 0 END) AS AH FROM A GROUP BY ID HAVING MAX(CASE WHEN SCORE IN (MAX_SCORE,MIN_SCORE)THEN 1 ELSE 0 END)=0 ;

@ankitbansal6

2 жыл бұрын

This is also fine

@zaravind4293

2 жыл бұрын

@@ankitbansal6 I just use in operatior to check score in max score and min score ankit so that we can reduce some code and also I find max score and min score along with the join condition Ankit.

@sarvesht7299

11 ай бұрын

Can you pls say why are u using max of case ?

with cte as ( select *, min(score)over(partition by exam_id) as lowest, max(score)over(partition by exam_id) as highest from exam ), cte1 as ( select *, case when score!=lowest and score!=highest then 0 else 1 end as flag from cte ), cte2 as ( select student_id from cte1 where flag = 0 group by 1 minus select student_id from cte1 where flag = 1 group by 1 ) select c.student_id, s.student_name from cte2 c join students s on c.student_id = s.student_id;

In oracle :- WITH cte AS ( SELECT s.student_id, e.exam_id, e.score, CASE WHEN e.score IN ( MIN(e.score) OVER( PARTITION BY e.exam_id ), MAX(e.score) OVER( PARTITION BY e.exam_id ) ) THEN 1 ELSE 0 END flg FROM students_40 s, exams_40 e WHERE s.student_id = e.student_id ) SELECT student_id FROM cte GROUP BY student_id HAVING MAX(flg) = 0;

with exam_max_min as( select exam_id,max(score) as max_marks,min(score) as min_marks from exams1 group by exam_id) select b.student_id,b.student_name from exams1 a inner join students1 b on a.student_id=b.student_id inner join exam_max_min c on a.exam_id=c.exam_id where a.score not in (c.min_marks,c.max_marks) group by b.student_id,b.student_name having count(*)>1

MY SQL solution: with cte as ( SELECT *, MIN(score) OVER (PARTITION BY exam_id) AS min_score, max(score) over (partition by exam_id) as max_score FROM exams ) SELECT distinct (student_id) FROM exams WHERE student_id NOT IN ( SELECT student_id FROM cte WHERE score = min_score or score = max_score )

Sir my approach: WITH CTE_1 AS( Select A.student_id, A.student_name, B.exam_id, score from students A, exams B, (Select exam_id, MIN(score) as min_score, MAX(score) as max_score from exams GROUP BY exam_id HAVING MAX(score) != MIN(score)) C WHERE A.student_id = B.student_id AND B.exam_id = C.exam_id AND score IN (min_score, max_score)) Select A.student_id, A.student_name, B.exam_id, score FROM students A, exams B WHERE A.student_id = B.student_id AND A.student_id NOT IN (Select student_id FROM CTE_1)

Hi ankit my solution is this. Please correct me if Im wrong. Thanks in advance. With cte as(select examid , row_ number() over (partition by exam_id order by score ) min, row_number () over(partition by exam_id order by score desc) max from exams Where min! =1 and max! =1) Select students.student_name from students inner join cte on students.student_id =exams.student_id

Hi Ankit, I think this is a much better solution WITH cte AS( SELECT e.*, s.student_name, DENSE_RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS rank_desc, DENSE_RANK() OVER(PARTITION BY exam_id ORDER BY score ASC) AS rank_asc FROM exams3_sep e JOIN students4_sep s ON e.student_id = s.student_id ) SELECT student_id, student_name FROM cte GROUP BY 1,2 HAVING MIN(rank_desc) 1 AND MIN(rank_asc) 1 ;

@Ankit , what do you think of this approach: 1. Define a CTE which will show all student id, name, flag (flag 0 when score is between min and max of that exam score , else flag 1) 2. In main query , only select those rows/students from CTE for whom ALL flag(s) are 0 (i.e student is quiet for ALL exams s/he appeared for) # MySQL -- STEP -1 : Define a CTE which will show all student id, name, flag WITH cte AS (SELECT s.student_name , e.student_id, (CASE WHEN score > ( Min(score) OVER( partition BY exam_id) ) AND score ELSE 1 END) flag FROM exams e -- flag 0 , score is between min and max , else flag 1 INNER JOIN students s ON e.student_id = s.student_id -- join to get student name ) -- STEP -1 ends -- STEP -2 : In main query , only select those rows/students from CTE for whom ALL flag(s) are 0 SELECT DISTINCT student_name FROM cte c -- select only those students for whom all flags are 0 WHERE 0 = ALL (SELECT flag FROM cte c1 WHERE c.student_id = c1.student_id) -- STEP-2 ends #output Jade

Hi Aniket, Please find my solution :) with quietStudents_1 as ( select sl.student_id, sl.student_name, max(el.score) over(partition by el.exam_id) as max_marks, min(el.score) over(partition by el.exam_id) as min_marks, el.score, el.exam_id from studentsLeet sl inner join examsLeet el on sl.student_id =el.student_id ), quietStudents_2 as ( select distinct student_id, student_name,score from quietStudents_1 where score = max_marks or score = min_marks) select student_id, student_name from quietStudents_1 except select student_id, student_name from quietStudents_2 Happy learning !!!

WITH cte AS( SELECT *, RANK() OVER(PARTITION BY exam_id ORDER BY score) AS rnk1, RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS rnk2 FROM exams ) SELECT * FROM students WHERE student_id not in (SELECT DISTINCT student_id FROM cte WHERE rnk1 = 1 or rnk2=1) AND student_id in (SELECT distinct student_id from exams)

MY SOLUTION: with cte as ( select exam_id ,e.student_id ,student_name ,score ,max(score) over (partition by exam_id) as max_score ,min(score) over (partition by exam_id) as min_score from exams e join students s on e.student_id=s.student_id ), cte2 as ( select * , case when score=max_score or score=min_score then 0 else 1 end as a from cte ) select distinct student_id,student_name from cte2 where student_id not in (select student_id from cte2 where a=0)

WITH CTE AS (SELECT *, COUNT(exam_id) OVER (PARTITION BY student_id) as no_of_exams, MAX(score) OVER (PARTITION BY exam_id) as max_score, MIN(Score) OVER (PARTITION BY exam_id) as min_score FROM exams1 ORDER BY exam_id,student_id) SELECT DISTINCT t.student_id,student_name FROM CTE t LEFT JOIN students1 s1 on t.student_id=s1.student_id WHERE t.student_id NOT IN (SELECT student_id FROM CTE WHERE no_of_exams>=1 AND score=max_score or score=min_score); I used this approach but this is only working for LEFT join or Right join.When I try to use INNER JOIN in this query it is not returning any values. Could you explain me whats the problem?

how to perform null safe inner join? Table A ______- colA 1 2 1 5 null null tableB colB null 2 5 5 output _________ colA colB 2 2 5 5 5 5 null null null null

answer should be 2 and 3 ...having should be min not max

addiction 😃

if student_name is also required: with cte1 as (Select exam_id,max(score) as highest_overall,min(score) as lowest_overall from Exams group by exam_id) ,cte2 as( Select Student_name,exam_id,score from Students s inner join exams e on s.student_id=e.student_id) Select Student_name from( Select Student_name, sum(CASE WHEN score=highest_overall or score=lowest_overall then 1 else 0 end )as flg from cte2 inner join cte1 on cte2.exam_id=cte1.exam_id group by student_name having sum(CASE WHEN score=highest_overall or score=lowest_overall then 1 else 0 end)=0 )

with cte1 as (select exam_id,min(score) mi_s,max(score) ma_s from exams group by exam_id) ,cte2 as (select student_id,count(*) cs from exams group by student_id) select student_id from (select student_id,count(*) c,cs from cte1 join exams using (exam_id) join students using (student_id) join cte2 using (student_id) where score > mi_s and score (simple ans)

with cte as( select stu.student_id,student_name,ex.exam_id,ex.score,FIRST_VALUE(score) over(partition by exam_id order by score desc rows between unbounded preceding and unbounded following) as highest_score, LAST_VALUE(score) over(partition by exam_id order by score desc rows between unbounded preceding and unbounded following) as lowest_score, count(stu.student_id) over(partition by stu.student_id order by stu.student_id) as cnt from students stu left join exams ex on stu.student_id=ex.student_id where score is not null), cte2 as (select *,sum(flag) over(partition by student_id order by student_id rows between unbounded preceding and unbounded following) from( select *,case when scorelowest_score then 1 else 0 end as flag from cte)a) select * from cte2 where cnt=sum;

with cte as (select exams.student_id,score,student_name,max(score) over(partition by student_name order by score desc) as max_score_1, min(score) over(partition by student_name order by score desc) as min_score_2, max(score) over(order by score desc) as max_score, min(score) over(order by score asc) as min_score from exams inner join students on exams.student_id=students.student_id),ab as( select student_id,student_name,sum(case when max_score_1 = max_score or min_score_2 = min_score then 1 else 0 end) as cnt1 from cte group by student_id,student_name) select student_id,student_name from ab where cnt1 = 0

WITH cte AS ( SELECT *, DENSE_RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_to_low, DENSE_RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_to_high FROM exams ) SELECT student_id FROM cte GROUP BY 1 HAVING MIN(high_to_low) 1 AND MIN(low_to_high) 1

What about this one?? with marks_cte as( select exam_id, min(score) as min_mark, max(score) as max_mark from exams group by exam_id), second_cte as( select e.*,m.min_mark,m.max_mark FROM exams e inner join marks_cte m on e.exam_id=m.exam_id), third_cte as( select * from second_cte where score = min_mark or score = max_mark), fourth_cte as( select * from second_cte where score > min_mark and score ,final_cte as( select f.* from fourth_cte f left join third_cte t on f.student_id = t.student_id where t.student_id is null) select distinct s.student_id, s.student_name from students s inner join final_cte f on s.student_id = f.student_id

Unique Sol- with t1 as (select s.*,e.exam_id,e.score from students as s join exams as e on s.student_id=e.student_id) , t2 as ( select *, max(score) over() as m, min(score) over() as l from t1) select student_id , student_name from t2 group by student_id , student_name having count(student_id)= sum(case when score>l and score

with temp as ( select s2.*, e2.exam_id, e2.score from students2 s2 join exams2 e2 on s2.student_id = e2.student_id ), temp2 as( select *, max(score)over(partition by exam_id) as maxi, min(score)over(partition by exam_id) as mini from temp ) select student_id, student_name from temp2 where score != maxi and score != mini except select student_id, student_name from temp2 where score = maxi or score = mini

My solution with MYSQL with exam_high_low as( select exam_id,max(score) as max_score,min(score) as min_score from exams group by exam_id ), quiet_student_data as ( select t1.*,t2.max_score,t2.min_score, case when t1.score = t2.max_score or t1.score = t2.min_score then 1 else 0 end as quiet_flag from exams t1 join exam_high_low t2 on t1.exam_id = t2.exam_id order by t1.exam_id,student_id ), quiet_student_data_flag as ( select student_id, sum(quiet_flag) as quiet_flag from quiet_student_data group by student_id having sum(quiet_flag) = 0) select t3.student_id,t4.student_name from quiet_student_data_flag t3 join students t4 on t3.student_id = t4.student_id;

WITH DATA AS ( SELECT *, FIRST_VALUE(SCORE) OVER (PARTITION BY EXAM_ID) AS MIN_VALUE, LAST_VALUE(SCORE) OVER (PARTITION BY EXAM_ID) AS MAX_VALUE FROM practise."exams"), QUIET_STUDENTS AS ( SELECT STUDENT_ID FROM ( SELECT *, CASE WHEN SCORE = MIN_VALUE OR SCORE = MAX_VALUE THEN 1 ELSE 0 END AS FLAG FROM DATA) GROUP BY 1 HAVING MAX(FLAG) SELECT A.* FROM practise."students" A JOIN QUIET_STUDENTS B ON A.STUDENT_ID = B.STUDENT_ID

WITH TEMP AS( SELECT E.exam_id,E.student_id,E.score,S.student_name,MIN(E.score) OVER(PARTITION BY E.exam_id ORDER BY SCORE) AS M1, MAX(E.score) OVER(PARTITION BY E.exam_id ORDER BY SCORE DESC) AS M2 FROM EXAMS E LEFT JOIN students S ON E.student_id=S.student_id ) SELECT DISTINCT student_name FROM TEMP WHERE score!=M2 AND score!=M1 AND student_id NOT IN (SELECT DISTINCT STUDENT_ID FROM TEMP WHERE score=M2 OR score=M1);

WITH CTE AS ( SELECT EXAM_ID, STUDENT_ID, SCORE, MAX(SCORE) OVER (PARTITION BY EXAM_ID) AS MAX_SCORE, MIN(SCORE) OVER (PARTITION BY EXAM_ID) AS MIN_SCORE FROM EXAMS ) SELECT DISTINCT E.STUDENT_ID,S.STUDENT_NAME FROM EXAMS E LEFT JOIN STUDENTS S ON E.STUDENT_ID=S.STUDENT_ID WHERE E.STUDENT_ID NOT IN ( SELECT STUDENT_ID FROM CTE WHERE SCORE=MAX_SCORE OR SCORE=MIN_SCORE )

A very very simple solution .. With Driver as (select exam_id,stu_id, score , min(score) over (partition by exam_id order by score ) min_score , max(score) over (partition by exam_id order by score desc ) max_score from exams order by stu_id ) select distinct driver.stu_id, stu_name from driver Join Students on driver.stu_id = Students.Stu_id where score Min_score and score Max_score

@ankitbansal6

2 жыл бұрын

This won't work. Try creating data and run the query

with s as (select exam_id, min(score) as min_s, max(score) as max_s from examss group by exam_id), m as (select e.student_id, count(e.exam_id) as total_exams, sum(case when e.score s.min_s and e.score s.max_s then 1 else 0 end) as quite_flag from examss e inner join s on s.exam_id = e.exam_id group by e.student_id) select m.student_id, s.student_name from m inner join students s on m.student_id = s.student_id where total_exams = quite_flag order by student_id;

my solution: MS SQL with cte as ( select * , min(score) over(partition by exam_id) as mn_score, max(score) over(partition by exam_id) as mx_score from exams ), cte2 as ( select *, (case when score=mn_score or score=mx_score then 1 else 0 end) as red_flag from cte ) select cte2.student_id, students.student_name from cte2 join students on cte2.student_id=students.student_id group by cte2.student_id,students.student_name having max(red_flag)=0

Another Way!! ; WITH cte as ( select * , MIN(score) over(partition by exam_id order by exam_id) as MINSCORE , MAX(score) over(partition by exam_id order by exam_id) as MAXSCORE , COUNT(student_id) over(partition by student_id order by student_id) as STDCNT , COUNT(exam_id) over(partition by exam_id order by exam_id) as EXAMCNT from exams123 ) select c.student_id , s.student_name from cte as c LEFT JOIN students123 as s on c.student_id = s.student_id where c.score c.MINSCORE AND c.EXAMCNT > = 1 group by c.student_id , s.student_name , STDCNT having COUNT(c.student_id) = STDCNT order by c.student_id

with cte as (select exam_id, min(score) as mini, max(score) as maxi from exams group by exam_id), cte1 as (select exams.exam_id, exams.student_id as student_id from exams inner join cte on exams.exam_id=cte.exam_id where exams.score>cte.mini and exams.score1

with cte as( select t1.*, rank() over(partition by exam_id order by score) as asc_rank, rank() over(partition by exam_id order by score desc) as dsc_rank from exams1 t1 join students t2 on t1.student_id=t2.student_id ), cte1 as( select * from cte where asc_rank=1 or dsc_rank=1) select distinct t1.student_id,t3.student_name from cte t1 left join cte1 t2 on t1.student_id=t2.student_id join students1 t3 on t1.student_id=t3.student_id where t2.student_id is null ;

MY Solution: SELECT STUDENT_ID,STUDENT_NAME FROM (SELECT S.STUDENT_ID,E.EXAM_ID,S.STUDENT_NAME,E.SCORE,MAX(SCORE) OVER(PARTITION BY EXAM_ID) AS MAX_EXAM,MIN(SCORE) OVER(PARTITION BY EXAM_ID) AS MIN_EXAM FROM STUDENTS S JOIN EXAMS E ON S.STUDENT_ID=E.STUDENT_ID) T1 GROUP BY STUDENT_NAME HAVING MAX(SCORE)MAX(MAX_EXAM) AND MIN(SCORE)MIN(MIN_EXAM);

Hi ankit why is student id 3 not considered? He scored 80 which is between highest 90 and lowest 70 of exam id 30 so he has given atleast one exam along with 2 My code is this:- with t3 as (select t2.student_id from (with t1 as (select e1.exam_id,max(e1.score) as max_score,min(e1.score) as min_score from exams e1 group by e1.exam_id) select e.student_id,e.score,e.exam_id,t1.max_score,t1.min_score from exams e join t1 on e.exam_id=t1.exam_id)t2 where t2.score t2.min_score) select distinct(s.student_name),s.student_id from students s join t3 on s.student_id=t3.student_id;

@ankitbansal6

2 жыл бұрын

Students should have middle marks in all the exams he gave.

WITH cte AS ( SELECT e.exam_id,e.student_id,e.score,eg.[min],eg.[max],s.student_name FROM exams AS e JOIN (SELECT exam_id,MIN(score) AS min,MAX(score) AS max FROM exams GROUP BY exam_id) AS eg on e.exam_id=eg.exam_id JOIN students AS s on s.student_id=e.student_id), cte1 AS ( SELECT student_id,student_name,CASE WHEN score>min and score

USED THIS approach , plz comment WITH X AS ( select S.student_name,E.student_id , COUNT(*) AS TOTAL_EXAM ,MIN(SCORE) AS S1 ,MAX(SCORE) AS S2 from EXAMS E INNER JOIN students S ON E.student_id=S.student_id GROUP BY E.student_id , S.student_name ) SELECT STUDENT_NAME FROM X WHERE S1 > (SELECT MIN(S1) FROM X) AND S2< (SELECT MAX(S2) FROM X)

WITH cte as (SELECT exam_id, e.student_id, score, student_name, DENSE_RANK()OVER(PARTITION BY exam_id ORDER BY score) as rnk_min, DENSE_RANK()OVER(PARTITION BY exam_id ORDER BY score desc) as rnk_max FROM exm e LEFT JOIN stn s ON e.student_id = s.student_id) SELECT student_id, student_name FROM cte GROUP BY student_id, student_name HAVING min(rnk_max) 1 and min(rnk_min) 1

WITH t1 as( SELECT exam_id,min(score) min_score, max(score) max_score FROM exams GROUP BY exam_id) SELECT * FROM students WHERE student_id NOT IN ( SELECT e1.student_id FROM exams e1 INNER JOIN t1 ON e1.exam_id=t1.exam_id AND (e1.score=min_score OR e1.score=max_score)) AND student_id IN (SELECT student_id FROM exams);

Here is my approach, please provide useful comments, with max_min_score_per_exam as ( select exam_id, max(score) as maxscore, min(score) as minscore from exams group by exam_id order by exam_id ) select s.student_id, s.student_name from students s join exams e on s.student_id = e.student_id join max_min_score_per_exam mmse on e.exam_id = mmse.exam_id where e.score mmse.minscore group by s.student_id, s.student_name having count(e.exam_id) > 1;

HI THIS IS MY APPROACH (ORACLE) select * from studentsb; select * from examsb; with cte as( select exam_id,student_id,score,max(score) over(partition by exam_id) mxs, min(score) over(partition by exam_id) mns, count(student_id) over(partition by student_id) no_of_exams_given from examsb ), cte2 as( select e.student_id,s.student_name,no_of_exams_given,count(*) as cnt from examsb e inner join cte c on e.exam_id = c.exam_id and e.student_id = c.student_id inner join studentsb s on e.student_id = s.student_id where e.score not in(mxs) and e.score not in(mns) group by e.student_id,s.student_name,no_of_exams_given ) select student_id,student_name from cte2 where no_of_exams_given = cnt; @AnkitBansal

WITH CTE AS( SELECT *, ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY score) AS high, ROW_NUMBER() OVER(PARTITION BY exam_id ORDER BY score DESC) AS low FROM exams) SELECT DISTINCT student_id, student_name FROM exams LEFT JOIN students USING(student_id) WHERE student_id NOT IN (SELECT DISTINCT student_id FROM CTE WHERE high=1 OR low=1)