프로그래머스_Lv3_대장균의 크기에 따라 분류하기 2

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

출처 : 프로그래머스 https://school.programmers.co.kr/learn/courses/30/lessons/301649

 

데이터 준비

-- 데이터베이스 생성 --

create database if not exists programmers_test
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

-- 테이블 생성 --
create table ECOLI_DATA(
	id integer not null,
    parent_id int,
    size_of_colony int not null,
    differentiation_date date,
    genotype int
);

-- 데이터 삽입 --
INSERT INTO ECOLI_DATA values ('1',NULL,'10','2019/01/01','5');
INSERT INTO ECOLI_DATA values ('2',NULL,'2','2019/01/01','3');
INSERT INTO ECOLI_DATA values ('3','1','100','2020/01/01','4');
INSERT INTO ECOLI_DATA values ('4','2','16','2020/01/01','4');
INSERT INTO ECOLI_DATA values ('5','2','17','2020/01/01','6');
INSERT INTO ECOLI_DATA values ('6','4','101','2021/01/01','22');
INSERT INTO ECOLI_DATA values ('7','6','101','2022/01/01','23');
INSERT INTO ECOLI_DATA values ('8','6','1','2022/01/01','27');

 

문제풀이

SELECT Percent_ED.id as ID,
		CASE 
        		WHEN Percent_ED.size_rank <= 0.25 THEN 'CRITICAL'
            		WHEN (Percent_ED.size_rank > 0.25) and (Percent_ED.size_rank<=0.5) THEN 'HIGH'
            		WHEN (Percent_ED.size_rank > 0.5) and (Percent_ED.size_rank<=0.75) THEN 'MEDIUM'
		ELSE 'LOW' END AS COLONY_NAME
FROM 
(SELECT id,
		Percent_RANK() over (order by size_of_colony desc) as 'size_rank'
FROM ECOLI_DATA) as `Percent_ED`
order by Percent_ED.id;