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

[@prakritigupta3477]

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

[@sohinibanerjee9617]

;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

[@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=pre group by marks

[@7vensandy_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)

[@devtripathi3434]

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

[@soulrider6822]

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

[@sahilgogna3393]

Thanks, good refresher for window function.

[@ajaykrishnanj5633]

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

[@brownwolf05]

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 ;

[@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;

[@sat9sh]

Thank You!
Waiting for Query #7👍

[@A-ZParenting]

Please find way of writing it in Oracle Sql:-
-- Output 2
Select test_id, marks from
(select test_id, marks,lag(marks,1) over (order by test_id) rn from student_tests) where marks>rn;
-- Output 1
with CTE_FINAL AS (
select test_id,marks,case when marks >NVL(marks_lag,0) then 1 else 0 end flag_marks from
(select s1.test_id,marks,lag(marks,1) over (order by test_id) marks_lag from student_tests s1))
SELECT test_id, marks FROM CTE_FINAL WHERE flag_marks=1;

[@harsh_36]

Sol1:
with cte as (
select test_id, marks,
lag(marks) over(order by test_id) as prev_id
from student_tests)
select test_id, marks from cte where marks >= prev_id
;
Sol2:
with cte as (
select test_id, marks,
lag(marks) over(order by test_id) as prev_id
from student_tests)
select test_id, marks from cte where marks > prev_id
;

[@sagarsaini5447]

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

[@anduamlaktadesse9284]

My solution using union clause :
Solution 1:
select top 1 test_id,marks from student_tests
union
select t.test_id,t.marks from (
select * ,
lag(marks) over( order by test_id) as prev_mark,
case when marks > lag(marks) over( order by test_id) then 1 else 0 end as flag
from student_tests ) as t
where t.flag=1
solution 2
select t.test_id,t.marks from (
select * ,
lag(marks) over( order by test_id) as prev_mark,
case when marks > lag(marks) over( order by test_id) then 1 else 0 end as flag
from student_tests ) as t
where t.flag=1

[@sammail96]

Thank you bro it is really helping me big time

[@parmoddhiman678]

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

[@griffingalatta3619]

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

[@digitaltechconnect1318]

Please bring some depth use case of self query

[@codeduel]

That’s where the real challenge comes in. If you need some questions on self query, I can send some to you.

[@NischitSapkota]

@@codeduelok

[@SriKanth-mz3hr]

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

[@Kirankumar-ml1ro]

(select * from student_tests order by test_id limit 1)
union
(select a.*
from student_tests a join student_tests b on a.test_id=b.test_id+1
where a.marks>b.marks)
order by test_id

[@anushakrishnaa]

with cte as(
select *,Lag
(marks,1,0) over(order by test_id)prev_marks from
test_marks)
select test_id,marks from cte where prev_marksmarks

[@saipavan9600]

Simple solution using lag window functions
Solution 1:
=========
select test_id,marks from (select *, lag(marks,1,0) over() as prev_test_marks,(marks-lag(marks,1,0) over()) as diff from student_tests
)sq1 where diff >0
Solution 2:
=========
select test_id,marks from (select *, lag(marks) over() as prev_test_marks,(marks-lag(marks) over()) as diff from student_tests
)sq1 where diff >0

[@naveenvjdandhrudu5141]

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

[@NEHAKHANZODE-p8p]

Thank You 🙂

[@sivakrishnasriram4782]

Thank you.

[@pavanreddy5724]

--including First Record
with cte as (
select *,coalesce(lag(marks) over(order by test_id),marks)as previous_testScores,row_number() over (order by test_id) as rn
from student_tests)
select test_id,marks from cte where marks >previous_testscores or rn=1;
--without first record
with cte as (
select *,coalesce(lag(marks) over(order by test_id),marks)as previous_testScores,row_number() over (order by test_id) as rn
from student_tests)
select test_id,marks from cte where marks >previous_testscores;

[@hariikrishnan]

Insert Queries:
create table student_table(
test_id int,
marks int
)
insert into student_table values
(100, 55),
(101, 55),
(102, 60),
(103, 58),
(104, 40),
(105, 50)

[@saiteja-gb8ho]

Part 1 :
with cte
as
(SELECT *,

Lag(marks) OVER (ORDER BY test_id) AS prev_value,

marks-Lag(marks) OVER (ORDER BY test_id) AS difference

FROM
test_result
ORDER BY
test_id)
select cte.test_id,cte.marks from cte
where cte.difference>=0
Part 2 :
with cte
as
(SELECT *,

Lag(marks) OVER (ORDER BY test_id) AS prev_value,

marks-Lag(marks) OVER (ORDER BY test_id) AS difference

FROM
test_result
ORDER BY
test_id)
select cte.test_id,cte.marks from cte
where cte.difference>0

[@mrbartuss1]

1)
with lag_cte AS (
SELECT *, lag(marks) OVER() prev_marks
FROM student_tests
)
SELECT test_id, marks
FROM lag_cte
WHERE marks > prev_marks OR prev_marks IS NULL
2)
with lag_cte AS (
SELECT *, lag(marks) OVER() prev_marks
FROM student_tests
)
SELECT test_id, marks
FROM lag_cte
WHERE marks > prev_marks

[@akshaysalve195]

with cte as (
SELECT
testid,
marks,
LAG(marks) OVER (ORDER BY testid) AS previous_value
FROM
test)
Select testid, marks from cte
where marks> coalesce(previous_value,0);
SELECT
testid,
marks,
LAG(marks) OVER (ORDER BY testid) AS previous_value
FROM
test)
Select testid, marks from cte
where marks> previous_value;

[@motiali6855]

👍👍

[@chetanmaurya8557]

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

[@Alexpudow]

Thanks!

[@fathimafarahna2633]

🙏

[@giannizifarelli3060]

A very basic question. Is there a reason not to use the LAG function in a WHERE clause and write a very simple SELECT statement:
SELECT test_id, marks
WHERE LAG(marks) OVER(ORDER BY test_id)

[@jayavinayak2593]

Can we use OFFSET 1 for the second solution ?

[@gouthamstar6558]

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

[@thirumanikumar8544]

@techTFQ for the Input ,Let take 101 - 50,102 -48,then the Solution You gave WOnt Work Right ,Bcz it by default select the First ,Here we are not supposed to select first (50).kindly Clarify me

[@balur2274]

Solution 1 and 2 both using CTE in MS Sql Server WITH Student_Test_Marks
AS (select test_id,
marks,
CASE
WHEN marks > LAG(marks, 1, 0) over (order by test_id) THEN
1
ELSE
0
END AS IsEligible
from student_tests
)
select test_id,
marks
from Student_Test_Marks
where IsEligible = 1
Solution 2 :
WITH Student_Test_Marks
AS (select test_id,
marks,
CASE
WHEN marks > LAG(marks, 1, marks) over (order by test_id) THEN
1
ELSE
0
END AS IsEligible
from student_tests
)
select test_id,
marks
from Student_Test_Marks
where IsEligible = 1

[@verdigin.kadar.alirsin1453]

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

[@likinponnanna8990]

My solutions
My solutions
-- Including the first test score
select test_id,marks from (
select *
,case when marks - lag(marks,1,0) over (order by test_id) > 0 then 1 else 0 end as flag
from "Techtaufiq".student_tests)
where flag = 1;
-- Excluding the first test score
select test_id,marks from (
select *
,case when marks - lag(marks,1,marks) over (order by test_id) > 0 then 1 else 0 end as flag
from "Techtaufiq".student_tests)
where flag = 1

[@splendidabhi]

done Day6 of #30DaySQLQueryChallenge

[@ayaalkasrawi826]

WITH cte_1 AS
(
SELECT
test_id,
marks,
LAG(marks) OVER (ORDER BY test_id) AS previous_mark,
LAG(marks) OVER (ORDER BY test_id) - marks AS validation
FROM student_tests
)
#OUTPUT 1
SELECT test_id, marks
FROM cte_1
WHERE validation < 0 OR previous_mark IS NULL;
#OUTPUT 2
SELECT test_id, marks
FROM cte_1
WHERE validation < 0

[@tornado9689]

Sir how to save the table after completion of our query and how to save in our pc please tell us❤❤❤

[@NischitSapkota]

After executing the query, go the right sidebar of the table/resultset. You'll find the options to save the table in whatever format you like : .xlsx,csv etc

[@saiswaroop3570]

select t1.test_id current_test,t1.marks current_marks,t2.test_id prev_test,t2.marks prev_marks from student_tests t1 left join student_tests t2 on t1.test_id-1=t2.test_id where t1.marks>t2.marks
select test_id,marks,nvl(lag(marks,1)over(order by test_id),0) prev_test_marks from student_tests

[@bassambenidir1729]

Here's the Output 1 using SELF JOIN:
SELECT
s1.TEST_ID,
s1.MARKS
FROM
STUDENT_TESTS s1
LEFT JOIN
STUDENT_TESTS s2 ON s1.TEST_ID = s2.TEST_ID + 1
WHERE
s1.MARKS > COALESCE(s2.MARKS, 0);

[@bassambenidir1729]

Here's the OUTPUT2, using SELF JOIN
SELECT
s1.TEST_ID,
s1.MARKS
FROM
STUDENT_TESTS s1
JOIN
STUDENT_TESTS s2 ON s1.TEST_ID = s2.TEST_ID + 1
WHERE
s1.MARKS > s2.MARKS;