대장균의 크기에 따라 분류하기 2

https://school.programmers.co.kr/learn/courses/30/lessons/301649

프로그래머스

WITH TEMP AS (
SELECT
    ID,
    RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS ranking,
    COUNT(*) OVER() AS count
FROM 
    ECOLI_DATA
)
SELECT
    ED.ID,
    CASE
    WHEN (ranking / count) * 100 <= 25 THEN "CRITICAL"
    WHEN (ranking / count) * 100 <= 50 THEN "HIGH"
    WHEN (ranking / count) * 100 <= 75 THEN "MEDIUM"
    WHEN (ranking / count) * 100 <= 100 THEN "LOW"
    END AS COLONY_NAME
FROM 
    ECOLI_DATA ED
JOIN TEMP ON ED.ID = TEMP.ID

PERCENT_RANK()를 이용한 방식

WITH TEMP AS(
    SELECT
        ID,
        PERCENT_RANK() OVER(ORDER BY SIZE_OF_COLONY DESC) AS Percent
    FROM
        ECOLI_DATA
)
SELECT
        ED.ID,
        CASE
        WHEN Percent <= 0.25 THEN "CRITICAL"
        WHEN Percent <= 0.5 THEN "HIGH"
        WHEN Percent <= 0.75 THEN "MEDIUM"
        ELSE "LOW"
        END AS COLONY_NAME
FROM    ECOLI_DATA ED
JOIN    TEMP ON ED.ID = TEMP.ID
ORDER BY ED.ID ASC;

NTILE을 이용한 풀이

WITH TEMP AS(
    SELECT
        ID,
        NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC) AS N
    FROM
        ECOLI_DATA
)
SELECT
        ED.ID,
        CASE
        WHEN TEMP.N = 1 THEN "CRITICAL"
        WHEN TEMP.N = 2 THEN "HIGH"
        WHEN TEMP.N = 3 THEN "MEDIUM"
        ELSE "LOW"
        END AS COLONY_NAME
FROM    ECOLI_DATA ED
JOIN    TEMP ON ED.ID = TEMP.ID
ORDER BY ED.ID ASC;