We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
with need as (
SELECT name,case when marks>=90 then 10
when marks>=80 and marks <90 then 9
when marks>=70 and marks<80 then 8
when marks>=60 and marks<70 then 7
when marks>=50 and marks <60 then 6
when marks>=40 and marks<50 then 5
when marks>=30 and marks<40 then 4
when marks>=20 and marks <30 then 3
when marks>=10 and marks<20 then 2
when marks>=0 and marks<10 then 1 end as rnk,marks
from
students
order by 2
)
,need2 as (
select rnk,marks,name
from need where rnk>7
order by 3,2 desc)
select distinct n2.name,n1.rnk,n1.marks
from need n1
left join need2 n2 using(rnk,marks)
order by 2 desc,1
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
The Report
You are viewing a single comment's thread. Return to all comments →
easy understandable solution
with need as ( SELECT name,case when marks>=90 then 10 when marks>=80 and marks <90 then 9 when marks>=70 and marks<80 then 8 when marks>=60 and marks<70 then 7 when marks>=50 and marks <60 then 6 when marks>=40 and marks<50 then 5 when marks>=30 and marks<40 then 4 when marks>=20 and marks <30 then 3 when marks>=10 and marks<20 then 2 when marks>=0 and marks<10 then 1 end as rnk,marks from students order by 2 ) ,need2 as ( select rnk,marks,name from need where rnk>7 order by 3,2 desc)
select distinct n2.name,n1.rnk,n1.marks from need n1 left join need2 n2 using(rnk,marks) order by 2 desc,1