[SQL문제다시풀기] 대장균의 크기에 따라 분류하기2 - percent_rank() over

문제

MYSQL

내가 작성한 정답

with sorted as (select id, size_of_colony,
                        percent_rank() over (order by size_of_colony desc) as sort
                from ecoli_data)
select id, case 
            when sort <= 0.25 then 'CRITICAL'
            when sort <= 0.5  then 'HIGH' 
            when sort <= 0.75 then 'MEDIUM'
            else 'LOW'      end   COLONY_NAME
from sorted
order by 1 asc

PERCENT_RANK

// 각 직원의 급여에 대한 상대적 순위를 계산
SELECT 
    id,
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percent_rank
FROM 
    employees;

// 부서별로 직원의 급여 순위를 계산 -> PARTITION BY를 사용
SELECT 
    id,
    name,
    department_id,
    salary,
    PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS salary_percent_rank
FROM 
    employees;