The Report (HackerRank)

The Report | HackerRank

Ketty gives Eve a task to generate a report containing three columns: Name, Grade and Mark. Ketty doesn't want the NAMES of those students who received a grade lower than 8. The report must be in descending order by grade -- i.e. higher grades are entered first. If there is more than one student with the same grade (8-10) assigned to them, order those particular students by their name alphabetically. Finally, if the grade is lower than 8, use "NULL" as their name and list them by their grades in descending order. If there is more than one student with the same grade (1-7) assigned to them, order those particular students by their marks in ascending order.

---

풀이

제출한 답

with sub as(select *,
(case when marks <= 9 then 1
 when marks <= 19 then 2
 when marks <= 29 then 3
 when marks <= 39 then 4
 when marks <= 49 then 5
 when marks <= 59 then 6
 when marks <= 69 then 7
 when marks <= 79 then 8
 when marks <= 89 then 9
 else 10 end) grade
from students)

select 
    (case when grade < 8 then NULL
    else name end) name, 
    grade, marks
from sub
order by grade desc, name asc, marks asc

-> case when 사용하여 student 테이블에 grade 컬럼 정의

다른 답

select 
(case when g.grade < 8 then NULL 
else s.name end) as name, g.grade, s.marks
from students as s join grades as g on s.marks between g.min_mark and g.max_mark
order by g.grade desc, s.name, s.marks

-> join 절에 between 조건 부여하면 grade를 따로 정의하지 않아도 된다.

2022.09.20