Solving 4 Tricky SQL Problems

[@parikgrover2281]

question2 -alternative approach
with cte as
(select studentid,studentname from subject
where marks>90
group by studentid,studentname)
select count(distinct c.studentid)/count(distinct(s.studentid))*100 as percentage from subject s
left outer join cte c
on s.studentid=c.studentid;

[@arjodh.singh1]

3- Solved by NTH_VALUE():
with cte as
(
select *, NTH_VALUE(marks,2) over
(partition by subject order by marks desc rows between unbounded preceding and unbounded following) as second_highest,
NTH_VALUE(marks,2) over
(partition by subject order by marks asc rows between unbounded preceding and unbounded following) as second_lowest
from students)
select subject,max(second_highest) as second_highest,max(second_lowest) as second_lowest
from cte group by subject
;

[@harishkanta3711]

Making use of rank function will be wrong here Ankit bhai, we need to use dense_rank here Ankit bhai. As one subject if has same two marks, then on rank will be skipped. thats the reason we need to make use of dense_rank

[@shubhamagrawal7068]

for Q4. --> it is always better to avoid or use less no. of temporary tables in ur queries. For beginners, solution in the video was good but for experts try to avoid making temporary table. Below is the approach.
select studentid, studentname, subject, marks, testdate,
(case
when lag(marks,1) over(partition by studentid order by testdate) is null then 'N/A'
else (case
when marks - lag(marks,1) over(partition by studentid order by testdate) > 0 then 'Increased'
else 'Decreased'
end)
end) status
from students;
NOTE : - in place of "case when" one can also use if() function to perform similar task

[@vandanaK-mh9zo]

Ques 4 -
select *,
case when (marks > lag(marks,1) over (partition by studentid , testid order by testid, testdate)) then 'Increased'
when (marks < lag(marks,1) over (partition by studentid order by testdate, subject)) then 'Decreased' else 'No Result' end as output
from students
order by studentid , testid ;

[@indradiptochatterjee2628]

Great Video! Here are my solutions:
select studentname, subject from (select *,
avg(marks) over (partition by subject order by subject) as avg_marks
from students) a
where marks>avg_marks
-- --------
select
round(1.0*count(distinct case when marks>90 then studentid end)/
count(distinct studentid),2)*100 as perc
from students
-- --------
with cte as(select *,
rank() over(partition by subject order by marks desc) as sec_highest,
rank() over(partition by subject order by marks) as sec_lowest
from students
order by subject)
select subject,
max(case when sec_highest=2 then marks end) as second_highest_marks,
max(case when sec_lowest=2 then marks end) as second_lowest_marks
from cte
where case when sec_highest=2 then marks end is not null
or case when sec_lowest=2 then marks end is not null
group by 1
-- -------
select *, case when marks>prev_marks then 'INC' when marks

[@sathwikdhanya5400]

3.
select subject,max(marks) as shc ,min(marks) as slc from (select *,
rank() over(partition by subject order by marks asc) as ascc,
rank() over(partition by subject order by marks desc) as dscc
from students) a
where ascc=2 OR dscc=2
group by subject

[@shekharagarwal1004]

Again full star for you Ankit ! Your questions and solutions are very interesting .

[@pranavtaparia7711]

Q1
with cte as(
select studentname, subject, marks, avg(marks) over(partition by subject) as "Average marks"
from tricky
)
,jte as(
select * from cte)
select * from jte where marks>"Average marks"

[@007SAMRATROY]

question 4 alternate approach:
if it is the first test then let's make it NA:
with
cte as (
select *, lag(marks) over(partition by studentname order by subject asc) as prev_marks
from students
)
select *,
(case when prev_marks is null then 'NA' when prev_marks is not null then
(case when (prev_marks - marks) > 0 then 'Decreased' else 'Increased' end)
end) as comparison
from cte order by studentid;

[@chenchuladileep7410]

Great video sir
my approach to Q3
with cte as (
select *, RANK() over (partition by [subject] order by marks desc ) as by_dsc,
RANK() over (partition by [subject] order by marks asc ) as by_asc from students),
cte1 as(
select subject,marks from cte where by_dsc=2
),cte2 as(
select subject,marks from cte where by_asc=2 )
select c1.subject,c1.marks as second_highest,c2.marks as second_lowest
from cte1 c1
join cte2 c2 on c1.subject=c2.subject

[@riyaz8072]

3 QUESTION'S ANSWER IN MY APPROACH.. ALMOST SIMILAR..
with cte as (select* , case when rank() over(partition by subject order by marks desc) = 2 then marks else null end as second_heighest,
case when rank() over(partition by subject order by marks ) = 2 then marks else null end as second_lowest
from students)
select subject, sum(second_heighest) as second_heighest, sum(second_lowest) as second_lowest
from cte
group by subject

[@sathwikdhanya5400]

1.
select * from (select *,
avg(marks) over(partition by subject) as avgg
from students) a
where marks>avgg

[@Jaipreksha]

Very good explanation Ankit. God bless you. You are helping many people

[@ankitbansal6]

🙏🙏

[@MixedUploader]

question 3 tried with below way:
with cte as(select * from
(select *,
rank() over(partition by subject order by marks desc) as highest_rnk,
rank() over(partition by subject order by marks) as lowest_rnk
from students) A)
select c1.subject,c1.marks as second_highest_mark,c2.marks as second_lowest_mark
from cte c1
inner join cte c2 on c1.subject=c2.subject and c1.highest_rnk=2 and c2.lowest_rnk=2
order by c1.subject

[@rashmidutta7151]

Q1)
select * from students;
with cte as(
select *,avg(marks) over(partition by subject) as av from students)
select * from cte where marks>av

[@loveleen0091]

Question 3 -
with a as
(select subject, marks,
rank() over (partition by subject order by marks) as low,
rank() over (partition by subject order by marks desc) as high
from students)
select subject, min(marks) second_lowest, max(marks) second_highest from a where low = 2 or high = 2
group by subject;

[@rashmidutta7151]

Q3)with cte3 as(
with cte2 as(
with cte as (
select *,row_number() over(partition by subject order by marks desc) as rw,
count(1) over(partition by subject ) as cn
from students)
select subject,rw,cn,marks from cte where rw=2 or cn-rw=1)
select subject as subject ,case when rw=2 then marks end as high,
case when cn-rw=1 then marks end as low from cte2)
select subject,max(high) as highscore,max(low) as lowscore from cte3 group by subject

[@007SAMRATROY]

question 3 alternate approach:
with
second_lowest as (
select subject, marks, rank() over(partition by subject order by marks asc) as asc_marks from students s
),
second_highest as (
select subject, marks, rank() over(partition by subject order by marks desc) as desc_marks from students s
)
select sh.subject, sh.marks, sl.marks from (
(select subject, marks from second_lowest where asc_marks = 2) sl inner join (select subject, marks from second_highest where desc_marks = 2) sh on sl.subject = sh.subject)
order by sh.subject, sh.marks desc;

[@LogicQuest]

I follow your channel and techtfq very sincerely..
Please keep uploading..
I always pray mentors like you get every thing they dream of..

[@ankitbansal6]

Thank you 💓

[@tupaiadhikari]

In 13:07, second highest marks come as 29 for subject 3 and second lowest comes as 98 for subject 3, which actually should be vice versa.
Please see my code which gives the correct result:
SELECT
subject,
MAX(A.marks) AS secondHighest,
MIN(A.marks) AS secondLowest
FROM
(SELECT
subject,
marks,
RANK() OVER(PARTITION BY subject ORDER BY marks ASC) AS rnk_asc,
RANK() OVER(PARTITION BY subject ORDER BY marks DESC) AS rnk_desc
FROM
students) AS A
WHERE
A.rnk_asc=2 OR
A.rnk_desc=2
GROUP BY
subject;
Thanks for these great contents anyways Ankit.

[@ankitbansal6]

I think second highest will be 29 only . Think about it.

[@tupaiadhikari]

@@ankitbansal6 Yes You are correct. Sorry for the Confusion.

[@aamirsohail2576]

Q3
with cte as(
select subject,marks,row_number() over(partition by subject order by marks asc) as rn
from students
group by subject,marks)
select cte.subject,cte.marks as second_lowest_marks,cte1.marks second_highest_marks from cte inner join(
select subject,marks,row_number() over(partition by subject order by marks desc) as rn1
from students
group by subject,marks) cte1
on cte.subject = cte1.subject and cte.rn = cte1.rn1
where cte.rn = 2 and cte1.rn1 = 2

[@dharmeshdutt4129]

with Oracle SQL
1. select * from (select s.*, avg(marks) over(partition by subject) avg_marks from students s) where marks>avg_marks order by studentid;
2. select count(distinct case when marks>90 then studentid else null end)/count(distinct studentid)*100||'%' top10 from students;
3. with cte as (select subject,marks, dense_rank() over(partition by subject order by marks) lowest1st, dense_rank() over(partition by subject order by marks desc) higtest1st from students)
select c1.subject , c1.marks second_lowest, c2.marks second_higest from cte c1,cte c2 where c1.lowest1st=2 and c2.higtest1st=2 and c1.subject=c2.subject;
4. with cte as (select s.* , lag(marks) over(partition by studentid order by subject) previousmarks from students s order by studentid, testid, subject)
select c.*, case when marks>previousmarks then 'inc' when previousmarks>marks then 'dec' else null end status from cte c;

[@Dhanushts-g7x]

#1
with cte1 as
(select subject,avg(marks) mar from students
group by subject)
select studentid,studentname,subject,marks,mar from students join cte1 using (subject)
where mar90
group by studentid) a)
select round(c*100/count(distinct students.studentid),2) pct from cte1,students
#3
with cte1 as
(select subject,marks sec_lowest,dense_rank() over(partition by subject order by marks) min_rank
from students),
cte2 as
(select subject,marks sec_highest,dense_rank() over(partition by subject order by marks desc) max_rank
from students)
select subject,sec_highest,sec_lowest from cte1 join cte2 using (subject)
where min_rank=2 and max_rank=2
#4
with cte1 as
(select studentid,studentname,subject,marks,
lag(marks,1,0) over(partition by studentid order by subject) m2 from students)
select *,case when m2=0 then null when diff>0 then "inc" else "dec" end inc_dec from
(select *,marks-m2 diff from cte1) c
(easy ans check ones try to understand then ull)
all the best!
happy learning

[@vaibhavtiwari8670]

Solution for seecond highest and lowest
with cte as
(SELECT marks,subject, row_number() over(PARTITION by subject order by marks desc ) as high,
row_number() over(PARTITION by subject order by marks ASC) as low
FROM students)
select subject,
Max(case when high=2 then marks end) as SECOND_high,
Max(case when low=2 then marks end)as SECOND_low
from cte
GROUP by subject

[@utkarshtrivedi9949]

For 3rd question , here is my approach
with asce as (
select
subject,
marks,
dense_rank() over( partition by subject order by marks asc) as arnk
from students
),
desce as (
select
subject,
marks,
dense_rank() over( partition by subject order by marks desc) as drnk
from students
)
select
a.subject,
second_highest,
second_lowest
from (select subject, marks as second_lowest from asce where arnk=2) as a
JOIN (select subject, marks as second_highest from desce where drnk=2) as b
ON a.subject=b.subject

[@ankitbansal6]

This is also good. Thanks for posting 👏

[@AjithShetty-u7e]

For 3 rd question
with cte as(
Select subject,marks,
rank() over(partition by subject order by marks ) asc_mark,
rank() over(partition by subject order by marks desc ) des_mark
from students)
select subject,
max(case when des_mark=2 then marks else null end) as second_highest ,
max(case when asc_mark=2 then marks else null end) as second_lowest
from cte
group by subject

[@ZeeshanSyed-x5i]

--1st solution:
SELECT * FROM (SELECT *,avg(marks) over(PARTITION BY subject) AS avgg FROM students) WHERE marks>avgg
--2nd solution
WITH cte AS (SELECT *,count(1) FROM students WHERE marks>=90 GROUP BY studentname),
cte2 AS(SELECT *,count(1) AS cnt ,max(studentid) AS maxx FROM cte)
SELECT cnt*100/maxx AS percentage FROM cte2

[@reshmashaik6606]

Q1:
with cte as(
select subject,
avg(marks) as marks
from student
group by subject)
select a.studentid, a.subject from student a
join cte b on a.subject=b.subject
where a.marks>b.marks
order by studentid
Q2:
with cte as(
select * from student
where marks>90)
select COUNT(distinct a.studentid)*1.00/COUNT(distinct b.studentid) as per from cte a
left join student b on a.subject=b.subject
Q3:
with cte as(
select *,
dense_rank() over(partition by subject order by marks desc) as ranks_h,
dense_rank() over(partition by subject order by marks) as ranks_l
from student)
select subject,
max(case when ranks_h=2 then marks end) as second_highest,
max(case when ranks_l=2 then marks end) as second_lowest
from cte
group by subject

[@AshutoshVerma-d4z]

Q3. using join
select t1.subject, t1.marks as sec_highest, t2.sec_lowest from
(select *,
rank() over(partition by subject order by marks desc) as second_highest from students) t1
join
(select marks as sec_lowest, subject,
rank() over(partition by subject order by marks asc) as second_lowest from students) t2
on t1.second_highest=t2.second_lowest and t1.subject=t2.subject where second_highest=2

[@motiali6855]

Q3 ans
select distinct subject
, nth_value(marks,2) over(partition by subject order by marks desc ROWS BETWEEN unbounded preceding AND UNBOUNDED following ) as second_highest_marks
,nth_value(marks,2) over(partition by subject order by marks asc ROWS BETWEEN unbounded preceding AND UNBOUNDED following) as second_lowest_marks
from students;

[@dfkgjdflkg]

Thank for providing very challenging problems along with the solution. Thank you again for your time shared with the problems. Thanks

[@enisertem9738]

with base as (select LAG(marks) over (partition by studentid,testid order by marks,testdate) prev_mark ,marks, studentname,subject from students order by studentid,testid )
select case when marks>prev_mark then 'up' when prev_mark is null then 'n/a' when prev_mark>marks then 'down' end trend, prev_mark ,marks, studentname,subject from base

[@miraculous6007]

problem 1 solutions without cte
select * from (select *, AVG(marks) OVER(partition by subject) a from students) where marks> a

[@ankitbansal6]

Thanks for posting 👏

[@pranavtaparia7711]

Q3
with cte as(
select subject, studentid, marks, rank() over(partition by subject order by marks desc) as rn1, rank() over(partition by subject order by marks asc) as rn2
from tricky
group by subject, studentid, marks
order by subject, studentid, marks
)
select cte1.subject as Subject, cte1.marks as second_highest_marks, cte2.marks as second_lowest_marks
from cte cte1 join cte cte2
on cte1.subject=cte2.subject and cte1.rn1=2 and cte2.rn2=2

[@mahesh97413]

Hi Ankit. Thanks for sharing valuable content. Your videos are really helpful!
I tried below one:
%sql
with cte as
(
select * from
(
select studentid,marks,subject,dense_rank() over(partition by subject order by marks desc) as max_rank from students)res where res.max_rank=2
)
,cte1 as
(
select * from(
select studentid,marks,subject,dense_rank() over(partition by subject order by marks) as min_rank from students)res where res.min_rank=2
)
select c.subject,c.marks as second_highest_marks,c1.marks as second_lowest_marks
from cte c
inner join cte1 c1
ON c.subject=c1.subject

[@rashmidutta7151]

Q2)select count(distinct studentid)*100.00/(select count(distinct studentid) from students) from students where marks>90

[@enisertem9738]

with base as (
select subject, rank() over (partition by subject order by marks) lowest ,
rank() over (partition by subject order by marks desc) highest,marks from students)
select b1.subject , b1.marks as second_highest, b2.marks as second_lowest from base b1 inner join base b2
on b1.highest = 2 and b1.subject = b2.subject and b2.lowest = 2

[@msd7451]

Question1:Using window function
select studentname,subject
from(
select *,avg(marks) over(partition by subject) as avg_marks
from students) as t
where t.marks>t.avg_marks;

[@mohdtoufique7446]

Hi Ankit...Thanks for the content...My approach
1) WITH CTE AS(SELECT *,avg(marks)OVER(PARTITION BY subject)AS avg_marks
FROM students)
SELECT * FROM CTE
WHERE marks>avg_marks;
2)SELECT COUNT(DISTINCT studentid)*1.0*100/(SELECT COUNT(DISTINCT studentid) FROM students)
FROM students
WHERE marks>90;
3) WITH CTE AS(SELECT *,DENSE_RANK()OVER(PARTITION BY subject ORDER BY marks DESC) AS higherst_rn
,DENSE_RANK()OVER(PARTITION BY subject ORDER BY marks ASC) AS lowest_rn
FROM students)
SELECT subject,SUM(CASE WHEN higherst_rn=2 THEN marks ELSE NULL END )AS second_highest_marks,
SUM(CASE WHEN lowest_rn=2 THEN marks ELSE NULL END)AS second_lowest_marks
FROM CTE
GROUP BY subject;
4)
WITH CTE AS(SELECT *,
LAG(marks)OVER(PARTITION BY studentname ORDER BY testdate) AS prev_marks
FROM students)
SELECT studentid,testid,(CASE WHEN marks>prev_marks THEN 'Increased'
WHEN marks

[@ankitbansal6]

Very good. In second question you don't have to call the table twice. Check my solution. Thanks for posting 👏

[@mohdtoufique7446]

@@ankitbansal6 .. Yes,you are correct.. Thanks for pointing it out..

[@faizraina7946]

bro for the first answer why are you not mentioning order by

[@meghnasoni]

1. SELECT * from
(SELECT subject, avg(marks) as av
from students
GROUP by 1)x
inner join students s on s.subject=x.subject
where s.marks > x.av
2.SELECT count(distinct flag)*100/count(DISTINCT studentid) as perc
from
(SELECT *,
case when marks> 90 then studentid else null end as flag
from students)x

[@AmanRaj-uf7wx]

mysql code for Question 3: with cte as (
select subject, marks,
rank()over(partition by subject order by marks) as desc_rank,
rank()over(partition by subject order by marks desc) inc_rank
from students
)
select subject,
max(case when desc_rank = 2 then marks end) as second_lowest_marks,
max(case when inc_rank = 2 then marks end) as second_highest_marks
from cte
group by subject

[@vandanaK-mh9zo]

My Approach to Ques3 -
with high as (
select *,row_number() over (partition by subject order by marks desc) as high_check
from students qualify high_check = 2 ),
low as (
select *,row_number() over (partition by subject order by marks asc) as low_check
from students qualify low_check = 2 )
select h.subject, l.marks as second_lowest, h.marks as second_highest
from high h inner join low l on h.subject = l.subject ;

[@mayankbhardwaj1487]

--ques 3
with one as (
select *,row_number() over(partition by subject order by marks) as rn,
row_number() over(partition by subject order by marks desc) as rnk,
row_number() over(partition by subject order by marks) -row_number() over(partition by subject order by marks desc) as diff
from students)
select subject,
sum(case when diff =1 or diff=2 then marks end) as second_highest_marks,
sum(case when diff = -1 or diff =-2 then marks end) as second_lowest_marks
from one
group by subject
-- ques 4
with one as (
select *,
lag(marks,1) over(partition by studentid order by testdate,subject) as previous_test_marks
from students)
select *,
case
when marks< previous_test_marks then 'descreased'
when marks > previous_test_marks then 'increased' else null end as status
from one

[@abb_raj1107]

My apporach for all 4ques:
--Q1
with avg_score as (
select subject,floor(avg(marks)) avg_marks
from students
group by subject)
select subject,group_concat(studentname) list_of_student from students join avg_score using(subject)
where marks>avg_marks
group by subject
order by subject,studentid;
-- Q2
select count(distinct case when marks > 90 then studentid end)/ count(distinct studentid) * 100 from students;
-- Q3
with low_high as (
select *
,dense_rank() over(partition by subject order by subject asc, marks desc) highest
,dense_rank() over(partition by subject order by subject asc, marks asc) lowest
from students
order by subject asc ,marks desc)
select subject
,sum(case when highest = 2 then marks end) H_score
,sum(case when lowest = 2 then marks end) L_score
from low_high
group by subject ;
-- Q4
select *
,case when lag(marks) over(partition by studentid order by testdate) > marks then 'decrease'
when lag(marks) over(partition by studentid order by testdate) < marks then 'increase' end result
from students

[@karangupta_DE]

A. with cte as (
select *,
avg(marks)over(partition by subject) as avg_marks from students)
select studentid, studentname, subject, marks, testid, testdate from cte where marks > avg_marks
B. with cte as (
select count(distinct studentid) as total_students from students),
cte1 as (
select count(distinct studentid) as above_ninty from students where marks >=90)
select div0(above_ninty, total_students) * 100 as percentage from cte, cte1;
C. with cte as (
select *,
dense_rank()over(partition by subject order by marks desc) as dr_max ,
dense_rank()over(partition by subject order by marks asc) as dr_min from students),
cte1 as (
select * from cte where dr_max = 2),
cte2 as (
select * from cte where dr_min = 2)
select cte1.subject, cte1.marks as second_highest, cte2.marks as second_lowest from
cte1 join cte2
on
cte1.subject = cte2.subject
order by cte1.subject;
D. with cte as (
select *,
lag(marks)over(partition by studentname order by testdate, subject) as next_marks
from students)
select *,
case when
marks > next_marks then 'increased'
when marks < next_marks then 'decreased'
else NULL
end as status from cte;

[@ankitbansal6]

Awesome. Thanks for posting 👏

[@manojyadav-yc5db]

with high as (select *
from (select
subject,
marks as second_highest_marks,
rank() over(partition by subject order by marks desc) as second_highest
from students)
where second_highest = 2),
low as (select *
from (select
subject,
marks as second_lowest_marks,
rank() over(partition by subject order by marks ) as second_lowest
from students)
where second_lowest = 2)
select h.subject,
second_highest_marks,
second_lowest_marks
from high as h
left join low as l on h.subject = l.subject
This was my approach

[@Datapassenger_prashant]

this should not comes into the category of complex problems..LOL..
new me After paracticing sql problems from your channel and your course.

[@apurvasaraf5828]

select d ,min(marks) as min_marks,
max(marks) as max_marks
from (select * from (select subject as d ,marks,rank() over(partition by subject order by marks) as r ,rank() over(partition by subject order by marks desc ) as g from students)p
where r=2 or g=2)i
group by d

[@kunalkumar-hl6gv]

ans-3 approach
with second_highest as(select subject,marks as second_highest_marks from (select *,rank() over(partition by subject order by marks desc) as rnk
from students) t
where t.rnk = 2),
second_lowest as (select subject,marks as second_lowest_marks from
(select *,rank() over(partition by subject order by marks asc) as rnk
from students) t
where t.rnk = 2)
select sh.subject , second_highest_marks,second_lowest_marks
from second_highest sh
join second_lowest sl
on sh.subject = sl.subject
###
last question approach
with previous_check as(select *,
lag(marks,1) over(partition by studentid order by testdate) as previous_marks
from students)
select studentid,studentname,subject,marks,testid,testdate,
case
when marks>previous_marks then 'incresed'
when marks

[@taniyasaini6830]

For question 2: Alternate approach (I think its not as optimised as your solution):
select (select count(distinct studentid) from students where marks>90)/(select count(distinct studentid) from students)*100 as percentage;

[@ankitbansal6]

You are right. You are calling the table twice so not good for performance. Thanks for posting 👏

[@Artouple]

select subject, max(marks) as second_highest, min(marks) as second_lowest from
(select * ,
rank() over(partition by subject order by marks desc) as d,
rank() over(partition by subject order by marks) as a
from students) a
where d = 2 or a = 2
group by subject;

[@sivasrimakurthi206]

smart, smart approach for 3rd query... loved it Sir @AnkitBansal

[@rajunaik8803]

**Solved question 3 little different using self join**
--Question1:
/*write the sql query to get the list of students who scored above the average marks in each subject*/
with cte1 as(
select subject,avg(marks) avg_marks from students group by subject
)select * from cte1 c join students s
on c.subject=s.subject and
s.marks> c.avg_marks
--Question 2
--write the sql query to get the % of students who score more than 90 in any subject amongst the total students
with cte1 as(
select *,case when marks>90 then studentid else null end as std_id from students
)
select count(distinct std_id)*1.0/count(distinct studentid) from cte1;
--Question3
--write a query to get the second highest and second lowest marks for each subject
with cte1 as(
select studentid,subject,marks,dense_rank() over(partition by subject order by marks) as rnk1,
dense_rank() over(partition by subject order by marks desc) as rnk2
from students
)
select cte1.subject,cte2.marks as second_highest,cte1.marks as second_lowest from cte1 cte1
join
cte1 cte2
on cte1.subject=cte2.subject
where cte1.rnk1=2 and cte2.rnk2=2
--Question 4:
--for each student and test,identify if their marks increased or decreased from their previous test
with cte1 as(
select *,lag(marks) over(partition by studentid order by testdate , subject) as prev_marks from students
)
select *,
case when marksprev_marks then 'increased'
else null end as status from cte1

[@praveensinghrathore4542]

Simply WOW, the approach for 3rd question was so intuitive and simple, you are amazing, I couldn't think of that good solution 😅 so came up with something a bit lengthy, here is my solution:
/*SOLUTION : 1. First CTE simply ranks marks
2. Second CTE uses total_count in each window to get second_lowest marks
3. Uses IF statment to conditionally select marks as for 2 subject count marks critieria inverses*/
WITH CTE1 as (SELECT s.*, COUNT(*) OVER(PARTITION BY subject) as total_student_count, DENSE_RANK() OVER(PARTITION BY subject ORDER BY marks DESC) as rnk
FROM students s)
, CTE2 AS (SELECT * FROM CTE1
WHERE ((total_student_count - 1) = rnk) OR rnk = 2)
SELECT subject, IF(total_student_count 2,MAX(marks), MIN(marks)) AS second_highest_marks, IF(total_student_count 2,MIN(marks),MAX(marks)) AS second_lowest_marks
FROM CTE2
GROUP BY subject;
Thank you for the wonderful videos, kudos to you.👍

[@ankitbansal6]

Thank you. Great effort from you 😊

[@Krishnaverma-xw7oe]

with cte as
(select studentid, marks
from students
where marks>90)
select (cast(count(distinct c.studentid) as float)/count(distinct s.studentid)*100) as student_percentage
from cte c, students s

[@ankitbansal6]

This might work but not a good solution from performance point of view. Thanks for posting.

[@subhadarshisethy1680]

Tq for lucid explanation...always keep it attach relevent question table creation query in description...like you always do

[@ankitbansal6]

Sure 😊

[@Mind_hit]

with cte as(
select avg(marks) as avma,subject from students group by subject)
select * from students s inner join cte c on c.subject=s.subject where s.marks>c.avma;

[@bharatkumarboddepalli6095]

hi sir we can solve the first query with out joins like this /
with cte as (
select *,avg(marks) over(partition by subject) as avg
from students)
select studentid,studentname,marks,subject
from cte
where marks>avg
order by studentid

[@srinubathina7191]

Thank you sir
i am learning very good content from you

[@SuperMohit95]

-- Question 1
-- write a sql query to get the lits of tsudents who scored above the average marks in each subject
WITH cte AS (
SELECT
*,
AVG(marks) OVER(PARTITION BY sub_name) AS sub_wise_avg
FROM
students3_Sep
)
SELECT
studentname,
sub_name
FROM
cte
WHERE
marks > sub_wise_avg
;
-- Q2
-- Write a SQL query to get the % of students who score more than 90 in any subject amongst the total students
WITH cte AS (
SELECT
DISTINCT studentname,
SUM(CASE
WHEN marks > 90 THEN 1 ELSE 0 END) AS mark_flag
FROM
students3_Sep
GROUP BY
1
HAVING
SUM(CASE
WHEN marks > 90 THEN 1 ELSE 0 END) >=1
)
SELECT
COUNT(studentname) / (SELECT COUNT(DISTINCT studentname) FROM students3_Sep )*100 AS pct_of_students
FROM
cte
;
-- Alternative solution
-- WITH cte AS (
WITH cte AS(
SELECT
studentid,
CASE
WHEN marks > 90 THEN 1 ELSE 0 END AS flag
FROM
students3_sep
GROUP BY
1
HAVING
COUNT(DISTINCT studentid) = 1
ORDER BY
studentid
)
SELECT
SUM(flag)/COUNT(studentid)
FROM
cte
;
-- Ankits solution
SELECT
COUNT(DISTINCT CASE WHEN marks > 90 THEN studentID END) / COUNT(DISTINCT studentid)
FROM
students3_Sep
;
-- Q3
-- write a query to get the 2nd highest and 2nd lowest marks for each subject
WITH cte AS (
SELECT
*,
RANK() OVER(PARTITION BY sub_name ORDER BY marks DESC) AS rank_dsc,
RANK() OVER(PARTITION BY sub_name ORDER BY marks ASC) AS rank_asc
FROM
students3_sep
)
SELECT
sub_name,
SUM(CASE
WHEN rank_dsc = 2 THEN marks ELSE 0
END) AS second_highest_marks,
SUM(CASE
WHEN rank_asc = 2 THEN marks ELSE 0
END) AS second_lowest_marks
FROM
cte
GROUP BY
1
;
-- Q4
-- For each student and test identify their marks increased or decrease from the previous test
WITH cte AS (
SELECT
*,
LAG(marks,1) OVER(PARTITION BY studentid ORDER BY studentid,testdate) AS prev_test_marks
FROM students3_Sep
)
SELECT
studentid,
studentname,
marks,
prev_test_marks,
CASE
WHEN marks > prev_test_marks THEN 'increased'
WHEN marks < prev_test_marks THEN 'decreased'
ELSE NULL
END AS status
FROM
cte
;

[@vaibhavverma1340]

Q-1
with avg_cte as
(select subject , avg(marks)as avg_marks from students s1 group by subject)
, table_name as
(select * from students)
select * from table_name tn , avg_cte ac
where ac.subject = tn.subject and ac.avg_marks < tn.marks

[@ankitbansal6]

You don't need tablename cte. Otherwise good.

[@Chathur732]

Q4. SOLUTION
select *, case when lag(marks) over(partition by studentid order by studentid) < marks then 'inc' else 'dec' end
from students

[@sandeeppanda9323]

with CTE as
(select * from
(select *,dense_rank()over(partition by subject order by marks desc)rn from students)
where rn=2),
CTE_1 as
(select * from
(select *,dense_rank()over(partition by subject order by marks)rn from students)
where rn=2)
select CTE.subject,CTE.marks as second_highest, CTE_1.marks as second_lowest
from CTE
join CTE_1
on CTE.subject = CTE_1.subject

[@ashpoghosh7645]

-- Question 3 Soln
with high as
(select subjects,marks,dense_rank() over(partition by subjects order by marks desc) as rnk_high from students)
,low as
(select subjects,marks ,dense_rank() over(partition by subjects order by marks) as rnk_low from students)
select distinct high.subjects, high.marks as second_highest,low.marks as second_lowest from high join low on high.subjects=low.subjects
where high.rnk_high=2 and low.rnk_low=2

[@ankitbansal6]

Looks good 👍

[@sumantkumarsuman7473]

Thankyou for such content!
Please provide the dataset creation query if possible here in comment section.

[@ankitbansal6]

It's there in description box .

[@sumantkumarsuman7473]

@@ankitbansal6 I am not able to find it sir, it only shows transcripts in description

[@ankitbansal6]

@@sumantkumarsuman7473 it's there in description box of you go down 😊

[@sumantkumarsuman7473]

@@ankitbansal6 got it! Thankyou.

[@BengaluruGuy08]

Great video... Thanks

[@ankitbansal6]

Thank you 😊

[@TheVaibhavdang]

Hi Ankit,
Can we do the first query as below?
/*list of students who scored above average marks in each subject*/
with CTE as(
select *,AVG(marks) over (partition by Subject) as avg_marks,
Case when marks>AVG(marks) over (partition by Subject) then 1 else 0 end as flag
from students)
Select * from CTE
where flag =1;

[@shankrukulkarni3234]

select * from students;
select * from
(select *,
avg(marks) over (partition by subject) as avg_marks from students) a
where marks>avg_marks;
select
(select count(distinct studentid) from students
where marks>90)/count(distinct studentid)*100 as percent from students;
select count(distinct case when marks>90 then studentid else null end )/count(distinct studentid)*100 as percent_marks;
select subject,
sum(case when rn1=2 then marks else null end) as second_lowest,
sum(case when rn2=2 then marks else null end) as second_lowest
from
(select *,
rank() over(partition by subject order by marks asc ) as rn1,
rank() over(partition by subject order by marks desc ) as rn2
from students)a
group by subject ;
select *,
case when previous_marks>marks then 'inc'
when previous_marks

[@mohit231]

Great video

[@kanchankumar3355]

Great video !!

[@siddheshkalgaonkar2752]

Problem-3 Solution: select subject, max(case when second_highest_rank=2 then marks else 0 end) as second_highest_marks,
max(case when second_lowest_rank=2 then marks else 0 end) as second_lowest_marks from
(select subject, marks, dense_rank() over(partition by subject order by marks desc) as second_highest_rank,dense_rank() over(partition by subject order by marks) as second_lowest_rank from students) group by subject;

[@sandippalit8990]

In the 4th question, why we are doing order by subject?

[@ithamarhardy3055]

hi, for the first problem why not:
with cte as
(select *, avg(marks) over(partition by subject) as avg_subject
from students)
select * from cte
where marks>avg_subject
this my solution

[@sakchamsaxena9895]

Query for showing Top 10 cities with maximum number of Customers
Can you please help me with this question from superstore data

[@ethyria7685]

Q3: WITH cte AS
(SELECT subject, marks,
RANK()OVER(PARTITION BY subject ORDER BY marks desc) as second_h,
RANK()OVER(PARTITION BY subject ORDER BY marks ) as second_l
FROM students)
SELECT a.subject, a.marks, b.marks
FROM cte a
JOIN cte b on
a.second_l = b.second_h
and a.subject = b.subject
where b.second_l = 2

[@harishkanta3711]

Ankit bhai will it be okay if i do not provide the subject column in the order by

[@sohailaihab1533]

Question 3 alternative:
WITH updated_ranks as (
SELECT subject, marks,
RANK() OVER (PARTITION BY subject ORDER BY marks ASC) AS low_high,
RANK() OVER (PARTITION BY subject ORDER BY marks DESC) AS high_low
FROM students
ORDER by subject, marks
)
SELECT subject, marks--*
FROM updated_ranks
WHERE updated_ranks.low_high = 2 Or updated_ranks.high_low = 2;

[@karangupta_DE]

Great video as always 😊

[@ankitbansal6]

Thank you 😊

[@ririraman7]

awesome!

[@ankitbansal6]

Thank you 😊

[@faizraina7946]

select studentid from students2
where marks >
(select studentid, avg(marks) from students2 )
group by studentid
can someone please tell me why this wont work

[@divyasallagundla2324]

Alternative for Q3. Please do let me know if that works as well.
WITH SECOND_HIGH_LOW AS (SEL SUBJECT ,MARKS ,DENSE_RANK() OVER (PARTITION BY SUBJECT ,ORDER BY MARKS DESC ) AS HIGH_MARK_RANKING
DENSE_RANK() OVER (PARTITION BY SUBJECT ,ORDER BY MARKS ASC ) AS LOW_MARK_RANKING
FROM STUDENTS)
SELECT DISTNCT SUBJECT ,MARKS AS SECOND_HIGHEST FROM SECOND_HIGH_LOW WHERE HIGH_MARK_RANKING= 2
UNION
SELECT DISTNCT SUBJECT ,MARKS AS SECOND_HIGHEST FROM SECOND_HIGH_LOW WHERE LOW_MARK_RANKING=2 /** USED DISTINCT SINCE THEY CAN BE RECORDS WITH SAME RANK .FOR TEH DEDUP **/

[@parth_pm16]

Without Join
select studentid, studentname,subject, Avg_Marks, marks
from (
select *
,avg(marks) over(partition by subject) as Avg_Marks
from students_27)a
group by studentid, studentname,subject, Avg_Marks, marks
having Marks>max(Avg_Marks)
order by subject

[@utkarshtrivedi9949]

Also is there any way where we make windows of 30 mins time as aggregation in the data of running timestamps?

[@ankitbansal6]

Yes that will be better

[@utkarshtrivedi9949]

@@ankitbansal6 Do you have any approach for that?

[@omkarm7865]

Very helpful...

[@ankitbansal6]

🙏

[@omkarm7865]

@@ankitbansal6 is it possible to paste those solutions in description

[@Chathur732]

But for subject 3 , how can 98 be second lowest mark and 29 be second highest mark by logic. Isn't that wrong??
Shouldn't it be the other way around??

[@dataworksstudio]

Bhaiyya, pls let me know if my solution to 3rd problem will work?
SELECT
subject,
NTH_VALUE(marks, 2) OVER (PARTITION BY subject ORDER BY marks DESC RANGE BETWEEN unbounded preceding AND unbounded following) AS sec_high_marks,
NTH_VALUE(marks, 2) OVER (PARTITION BY subject ORDER BY marks ASC RANGE BETWEEN unbounded preceding AND unbounded following) AS sec_low_marks
FROM students;
Thank you...your videos are really helpful❤️

[@ankitbansal6]

This is good. I didn't know about this function. I guess this is only available in MySQL. Thanks for posting 👏

[@ZeeshanSyed-x5i]

---1st solution
WITH cte as(
SELECT s.*,e.* FROM students s INNER join
(SELECT subject,avg(marks) AS avgg FROM students GROUP BY subject) e
ON s.subject=e.subject)
SELECT studentid ,studentname,subject,avgg, marks FROM cte WHERE marks>avgg
------3rd
WITH cte AS(SELECT *,rank() over(PARTITION BY subject ORDER BY marks asc) AS asc_order,rank() over(PARTITION BY subject ORDER BY marks desc)
AS dsc_order FROM students)
SELECT subject,
min(CASE WHEN asc_order=2 THEN marks END) AS Second_lowest,
max(CASE WHEN dsc_order=2 THEN marks END) AS Second_highest
FROM cte GROUP BY subject
-------4th
SELECT studentid,studentname,subject,testid,marks,CASE WHEN marks>lag(marks) over(PARTITION BY studentid) THEN 'inc' ELSE 'decr' END AS status
FROM students order BY studentid,subject,testid

[@vijayv500]

In the third problem, shouldn’t we use dense_rank() so that the script works when say subject 1 has same marks for two students?

[@ankitbansal6]

You are right. Thanks for pointing out 👍

[@shubhamagrawal7068]

for Q3. below is the better approach. No need to use rank() function and then making it cte. Instead use nth_value() function.
select distinct subject,
nth_value(marks, 2) over(partition by subject order by marks desc range between unbounded preceding and unbounded following) second_highest_marks,
nth_value(marks, 2) over(partition by subject order by marks range between unbounded preceding and unbounded following) second_lowest_marks
from students

[@jb4928]

I like it how in every video you write Parttion and add 'i' later to make it partition :) . JK amazing content buddy. Keep up the good work . Learning a lot from here.

[@ankitbansal6]

I don't know everytime i make that mistake 😃

[@GautamKumar-ci4rz]

.

[@DRahulRajuYadav]

Q3 by nth-value
with cte as (
select subject
, nth_value(marks,2) over(partition by subject order by marks desc) as second_highest_marks
,nth_value(marks,2) over(partition by subject order by marks asc) as second_lowest_marks
from students
)
select distinct subject, max(second_highest_marks),min(second_lowest_marks) from cte
group by subject