[HackerRank] The Report

WITH SL AS (
    SELECT NAME
    , CASE WHEN MARKS >= 90 THEN 10
        WHEN MARKS >= 80 THEN 9
        WHEN MARKS >= 70 THEN 8
        WHEN MARKS >= 60 THEN 7
        WHEN MARKS >= 50 THEN 6
        WHEN MARKS >= 40 THEN 5
        WHEN MARKS >= 30 THEN 4
        WHEN MARKS >= 20 THEN 3
        WHEN MARKS >= 10 THEN 2
        ELSE 0 END GRADE
    , MARKS
    FROM STUDENTS
    ORDER BY GRADE DESC, NAME, MARKS
)

SELECT CASE WHEN GRADE < 8 THEN NULL
        ELSE NAME END NAME
        , GRADE
        , MARKS
FROM SL

앗 Grades 테이블..... 까먹

 

 

SELECT CASE WHEN g.grade < 8 THEN NULL
		ELSE s.name END Name
FROM Students s
	INNER JOIN Grades g
    ON s.marks BETWEEN g.min_mark AND g.max_mark
ORDER BY g.grade DESC, s.name, s.marks