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 student_grades AS (
SELECT
Name,
CASE
WHEN Marks <= 100 AND Marks >= 90 THEN 10
WHEN Marks <= 89 AND Marks >= 80 THEN 9
WHEN Marks <= 79 AND Marks >= 70 THEN 8
WHEN Marks <= 69 AND Marks >= 60 THEN 7
WHEN Marks <= 59 AND Marks >= 50 THEN 6
WHEN Marks <= 49 AND Marks >= 40 THEN 5
WHEN Marks <= 39 AND Marks >= 30 THEN 4
WHEN Marks <= 29 AND Marks >= 20 THEN 3
WHEN Marks <= 19 AND Marks >= 10 THEN 2
ELSE 1
END AS Grade,
Marks
FROM Students
),
8nAbove AS (
SELECT
CASE
WHEN Grade >= 8 AND Grade <= 10 THEN Name
ELSE NULL
END AS Name,
Grade,
Marks
FROM student_grades
)
SELECT
Name,
Grade,
Marks
FROM
8nAbove
ORDER BY
Grade DESC,
CASE
WHEN Grade >= 8 AND Grade <= 10 THEN Name -- For Grade >= 8, order by Name alphabetically
ELSE NULL
END ASC,
Marks ASC;
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 →
WITH student_grades AS ( SELECT Name, CASE WHEN Marks <= 100 AND Marks >= 90 THEN 10 WHEN Marks <= 89 AND Marks >= 80 THEN 9 WHEN Marks <= 79 AND Marks >= 70 THEN 8 WHEN Marks <= 69 AND Marks >= 60 THEN 7 WHEN Marks <= 59 AND Marks >= 50 THEN 6 WHEN Marks <= 49 AND Marks >= 40 THEN 5 WHEN Marks <= 39 AND Marks >= 30 THEN 4 WHEN Marks <= 29 AND Marks >= 20 THEN 3 WHEN Marks <= 19 AND Marks >= 10 THEN 2 ELSE 1 END AS Grade, Marks FROM Students ), 8nAbove AS ( SELECT CASE WHEN Grade >= 8 AND Grade <= 10 THEN Name ELSE NULL END AS Name, Grade, Marks FROM student_grades ) SELECT Name, Grade, Marks FROM 8nAbove ORDER BY Grade DESC,
CASE WHEN Grade >= 8 AND Grade <= 10 THEN Name -- For Grade >= 8, order by Name alphabetically ELSE NULL END ASC, Marks ASC;