[MySQL] The Report [HackerRank Medium]

https://www.hackerrank.com/challenges/the-report/problem?isFullScreen=true 

The Report | HackerRank

You are given two tables: Students and Grades. Students contains three columns ID, Name and Marks.

Grades contains the following data:

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.

Write a query to help Eve.

Sample Input

Sample Output

Maria 10 99
Jane 9 81
Julia 9 88 
Scarlet 8 78
NULL 7 63
NULL 7 68

해석:

grade가 8 이상이면 이름을 출력하고, 7 이하는 NULL을 출력한다.

grade 기준으로 내림차순 정렬하고, GRADE가 같다면 MARKS 기준으로 오름차순 정렬한다

 

풀이:

SELECT NAME, CASE 
                WHEN MARKS >= 90 THEN 10
                WHEN MARKS >= 80 THEN 9
                ELSE 8
                END AS GRADE ,MARKS 
FROM STUDENTS
WHERE MARKS >= 70
ORDER BY GRADE DESC, NAME ASC;
SELECT NULL, CASE 
                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 1
                END AS GRADE ,MARKS 
FROM STUDENTS
WHERE MARKS < 70
ORDER BY GRADE DESC, MARKS ASC;

조건이 길지 않아서 모든 경우의 수를 표시했다. 이해하기 직관적이나 코드가 너무 길다

 

개선된 풀이 1:

SELECT IF(G.GRADE < 8, NULL, S.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 ASC, S.MARKS ASC

If를 사용해서 NAME출력과 NULL출력을 분리 하였다.

코드가 굉장히 줄어들었으나 다른 방법은 없을까?

 

개선된 풀이 2:

SELECT IF(G.GRADE < 8, NULL, S.NAME), G.GRADE, S.MARKS
FROM STUDENTS AS S, GRADES AS G
WHERE S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
ORDER BY G.GRADE DESC, S.NAME ASC, S.MARKS ASC

GRADE를 묶는데 꼭 GROUP을 쓸 필요는 없다

WHERE로도 GRADE를 GROUP에 묶을 수 있다