Student Performance - SQL Interview Query 6 | SQL Problem Level 'EASY'

30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the SIXTH video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.

Let's follow the below routine to make the best use of it:
1. Watch the YouTube video (first half) to understand the problem statement.

2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.

3. Share your solution on Discord and discuss different solutions and issues on my Discord server.

4. Watch the second half of my YouTube video to find my solution to the problem.

5. Share it with your contacts and spread the knowledge.

DOWNLOAD the Dataset from below:

Timeline:
00:00 Intro
00:16 Understanding Problem Statement
02:55 Solution - Ouput 1
07:48 Solution - Ouput 2

Thanks for participating in this challenge!

Good luck and Happy Learning!

[sahilgogna]

Thanks, good refresher for window function.

[sammail]

Thank you bro it is really helping me big time

[prakritigupta]

with cte as (select test_id, marks as current_performance, lag(marks) over() as previous_performance from student_tests)
select* from cte where

[devtripathi]

In problem 2, instead of making it same value we can use the functionality that comparison with NULL will result in False for the test_id 100

[sohinibanerjee]

;with cteM as
(select *, LAG(marks) over (order by testid) as PrevMarks, (Marks-LAG(marks) over (order by testid)) as Var
from StudentInput6)
select TestID, Marks from cteM where Var >0 or TestID=100

This works too

[parmoddhiman]

with cte as (SELECT test_id, marks,
lag(marks, 1, marks) over(order by TEST_ID) as pre_mark
FROM testscores)
select test_id, marks from cte
where marks>pre_mark

[ajaykrishnanj]

with cte as(
select *, LAG(marks) over(order by test_id) as previous_mark from student_tests)
select * from cte where marks>previous_mark or previous_mark is null

[vensandy_Data_Analyst]

I used the lead function to solve it:

(select *
from student_tests
order by test_id Limit 1 )

union all

-- without union, second output is derived

(select test_id_1, marks_1
from

(select test_id, marks,
lead(test_id) over(order by test_id) as test_id_1, lead(marks) over(order by test_id) marks_1
from
student_tests)
as A
where
marks_1>marks)

[SriKanth-mzhr]

select * from (select *,
lag(marks) over(order by test_id) as cumm_diff,
case when lag(marks) over(order by test_id) < marks then marks else null end as final_marks
from student_tests)
where final_marks is not null or cumm_diff is null and final_marks is null

[soulrider]

----output-1
with cte as
(select *,
lag(marks, 1, 0) over() as prev_marks
from input)
select test_id, marks from cte
where marks>prev_marks

----output-2
with cte as
(select *,
lag(marks, 1) over() as prev_marks
from input)
select test_id, marks from cte
where marks>prev_marks

[brownwolf]

solved it using lag and row_number functions, attaching solution
--- query 1
with cte as (
select *, marks-lag(marks) over(ORDER BY test_id) as flag, ROW_NUMBER() over(order by test_id) as rn from student_tests
)
select test_id, marks from cte where flag > 0 or rn = 1;

---- query 2
with cte as (
select *, marks-lag(marks) over(ORDER BY test_id) as flag from student_tests
)
select test_id, marks from cte where flag > 0 ;

[digitaltechconnect]

Please bring some depth use case of self query

[malcorub]

Since I did it in SAS using PROC SQL, I had to use SELF JOIN and WHERE.
PROC SQL;
SELECT
A.*
FROM STUDENT_TESTS AS A
LEFT JOIN STUDENT_TESTS AS B
ON A.TEST_ID=B.TEST_ID+1
WHERE (A.MARKS-B.MARKS >0 OR A.MARKS-B.MARKS IS NULL) AND A.TEST_ID IS NOT NULL;
QUIT;

[gouthamstar]

recently I got rejected because I did not solve a problem similar to this one
where I am supposed to calculate the total time spent by an employee in the office where his swipe in and swipe out details have been mentioned

[jayavinayak]

Can we use OFFSET 1 for the second solution ?

[sagarsaini]

-- output 1
with cte as(select *,
lag(marks, 1, 0) over(order by test_id) as lag_marks
from student_tests)
select test_id, marks from cte
where marks>lag_marks;

-- output
with cte as(select *,
lag(marks) over(order by test_id) as lag_marks
from student_tests)
select test_id, marks from cte
where marks>lag_marks;

[griffingalatta]

with cte as (select test_id, marks, marks- lag(marks, 1) over (order by test_id) as diff
from student_tests)

select test_id, marks from cte where diff > 0

with cte as (select test_id, marks, marks- lag(marks, 1, 0) over (order by test_id) as diff
from student_tests)

select test_id, marks from cte where diff > 0

[naveenvjdandhrudu]

Hi Taufeeq. I have one doubt from 30 days playlist sql challange, i observed that some queries were missing the day5 sql query was missing in your playlist (EMP_TRANSACTIONS based on their SALARY, INCOME, DEDUCTIONS tables) plz post it if missed or guide me where it was listed thank you in advance

[verdigin.kadar.alirsin]

HOCAMBEN BÖYLE YAPTIM SIKINTI OLUR MU

select * from student_tests;

select test_id, marks from
(
select test_id, marks,
case
when marks > lag(marks, 1) over(order by test_id) then 1
else 0
end as flag
from student_tests
) a
where flag = 1

[Satish_____Sharma]

Solved using join
select s2.test_id, s2.marks
from student_tests s1
left join student_tests s2 on s1.test_id+1=s2.test_id
where s1.marks<=s2.marks;

Solved using lag window function
select min(test_id) as test_id, marks from (select *, lag(marks, 1, marks) over (order by test_id) as pre
from student_tests) a where marks>=pre group by marks