SQL 코테 연습 프로그래머스 Lv.4 문제풀이 1

매일 프로그래머스 SQL 코테 2개씩 풀어보기 도전! Lv.4부터 시작해서 정답률 정렬로 제일 낮은거 하나, 높은거 하나 이렇게 풀어볼 예정이다.

 

1. 식품분류별 가장 비싼 식품의 정보 조회하기(GROUP BY)  정답률 : 84%

프로그래머스

 

 

문제

FOOD_PRODUCT 테이블에서 식품분류별로 가격이 제일 비싼 식품의 분류, 가격, 이름을 조회하는 SQL문을 작성해주세요. 이때 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력시켜 주시고 결과는 식품 가격을 기준으로 내림차순 정렬해주세요.

 

우선 원하는 식품분류만 WEHRE절로 구분한 후 GROUP BY의 기준이 될 CATEGORY로 제일 비싼 식품을 찾고, 해당 서브쿼리로 찾은 값에 동일한 가격과 분류에 맞는 이름을 join으로 붙이는 코드로 작성했다. 

-- 1. 나중에 필요 항목 join 하기
SELECT A.*, B.PRODUCT_NAME
FROM (
    SELECT CATEGORY, MAX(PRICE) AS MAX_PRICE
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY 1) A
LEFT JOIN FOOD_PRODUCT B
ON A.MAX_PRICE=B.PRICE AND A.CATEGORY=B.CATEGORY
ORDER BY MAX_PRICE DESC

 

위와 같은 방법 말고도 제일 비싼 식품에 flag를 세워 해당 flag에 해당하는 항목을 가져오는 방법 또한 생각해보았다. WINDOW 함수를 복습할 겸 ROW_NUMBER로 순위를 매겼다.

-- 2. WINDOW 함수로 순위 flag 세워서 확인
SELECT CATEGORY, PRICE, PRODUCT_NAME
FROM(
    SELECT CATEGORY, PRICE, PRODUCT_NAME
        , ROW_NUMBER() OVER(PARTITION BY CATEGORY ORDER BY PRICE DESC) RNK -- 카테고리별 순서 flag
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN ('과자', '국', '김치', '식용유')
    GROUP BY 1,2,3) A
WHERE RNK=1 -- 카테고리별 가장 비싼 항목 추출
ORDER BY PRICE DESC

 

아 참고로 WHERE문에서 IN으로 서브쿼리로 가져오는 방법도 있으나 이보단 첫번째 방법 처럼 JOIN을 통한 방법이 더 빠르다고 한다. 

 

 

 

2. 언어별 개발자 분류하기(GROUP BY)  정답률 : 37%

프로그래머스

 

문제

DEVELOPERS 테이블에서 GRADE별 개발자의 정보를 조회하려 합니다. GRADE는 다음과 같이 정해집니다.

 - A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자

 - B : C# 스킬을 가진 개발자

 - C : 그 외의 Front End 개발자

GRADE가 존재하는 개발자의 GRADE, ID, EMAIL을 조회하는 SQL 문을 작성해 주세요.

결과는 GRADE와 ID를 기준으로 오름차순 정렬해 주세요.

 

 

우선 GRADE를 정의하기 위해서 SKILLCODES 테이블에서 스킬명을 DELVELOPERS에 JOIN 시켰다. 이 문제에서도 WHERE문에 서브쿼리를 통해 붙일 수 있으나 앞서 말한 Run Time 이슈로 JOIN으로 진행했다. 

SKILL_CODE와 CODE는 2진법으로 이루어져있으며,  어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미한다고한다. 따라서 이전에 포스팅한 비트 연산법을 이용하여 테이블을 완성해준다. 아래 코드는 개발자들에 CODE를 모두 붙여준것을 확인하는 코드이다. 

-- 1. 개발자들에게 CODE를 JOIN한 결과 확인
SELECT *
FROM (SELECT D.*
            , CONV(SKILL_CODE,10,2) AS S_2, CODE, CONV(CODE,10,2) AS C_2, NAME, CATEGORY
        FROM DEVELOPERS D  
            LEFT JOIN SKILLCODES S
            ON D.SKILL_CODE>=S.CODE) A
WHERE SKILL_CODE & CODE = CODE

 

 

그 다음은 두번의 서브쿼리가 맘에 안 들지만 아래와 같이 CASE WHEN 문을 통해 GRADE를 매겨주었는데 A등급이 없어서 문제를 다시 잘 확인해보니 Front End 스킬과 Python 스킬을 '함께'  그제야 함께라는 단어가 눈에 들어왔다. 

-- 2. 실패
SELECT *
FROM (SELECT CASE WHEN NAME='Python' AND CATEGORY='Front End' THEN 'A'
            WHEN NAME='C#' THEN 'B'
            WHEN CATEGORY='Front End' THEN 'C'
            END AS GRADE
            , ID, EMAIL
        FROM (SELECT D.*, CODE, NAME, CATEGORY
                FROM DEVELOPERS D  
                LEFT JOIN SKILLCODES S
                ON D.SKILL_CODE>=S.CODE) A
        WHERE SKILL_CODE & CODE = CODE) B
WHERE GRADE IS NOT NULL
ORDER BY GRADE, ID

 

 

우선 확인되어야할 부분이 1)동시에 확인해야하는 항목이 서로 다른 칼럼에 존재하고있음 2)ID별로 확인되어야하기 때문에 처음에는 case when 문으로 두 칼럼에 존재하는 정보를 하나의 칼럼으로 합쳐넣으려고 했었다. 합친후에 ID별로 flag 값을 합하여 해당 합계가 grade에 부합하면 grade를 주는 방식으로 진행했다. 

아래의 코드는 코드실행으로 예시는 정답이나 다른 데이터로 채점했을 때는 실패하였다.

-- 3. 실패2
WITH AA AS( -- 1.두 테이블 join하여 비트 연산으로 스킬 확인
SELECT * 
FROM (SELECT ID, EMAIL, SKILL_CODE, CODE, NAME, CATEGORY
        FROM DEVELOPERS D  
        LEFT JOIN (SELECT * FROM SKILLCODES WHERE NAME IN ('Python', 'C#') OR CATEGORY ='Front End')S
        ON D.SKILL_CODE>=S.CODE) A 
WHERE SKILL_CODE & CODE = CODE
), BB AS ( -- 2. 서로 다른 칼럼에 있던 'NAME'과 'CATEGORY'를 하나의 칼럼에 모아서 flag
SELECT *, SUM(CHK) OVER(PARTITION BY ID,EMAIL)AS SUM_CHK
FROM (SELECT *, CASE WHEN NAME='Python' THEN 1
                WHEN NAME='C#' THEN 2
                WHEN CATEGORY='Front End' THEN 10
            END AS CHK
    FROM AA) A
) -- 3.가능한 case를 모두 나눠서 GRADE 
SELECT DISTINCT CASE WHEN SUM_CHK=11 THEN 'A'
            WHEN SUM_CHK=2 THEN 'B' 
            WHEN SUM_CHK=10 THEN 'C'
        END AS GRADE
    , ID, EMAIL
FROM BB
ORDER BY GRADE, ID

 

실패한 원인을 분석해보니 예시에는 한 사람당 가능한 Front End 언어가 1개씩이었지만 예시에는 그렇지 않을수도 있다는것을 확인했다. 왜냐하면 아래와 같이 하나의 CATEGORY에 여러 NAME이 있을수 있어서 위와 같은 방법으로 하면 Front End 스킬이 2개 있다면 SUM_CHK가 20으로 나오기 때문이다.

 

해서 하나의 칼럼으로 확인하지 않고, GROUP BY를 이용하여 python, c#, front end를 각각 다른 칼럼으로 flag처리 하였다. flag 처리 후 '해당 스킬이 1개 이상으로 있다면'을 조건으로 등급을 매겼다. 그리고 해당 스킬이 하나도 없는 ID는 모두 where 조건으로 날렸다. (초반에 GRADE 조건에서 한 사람이 A,B 또는 B,C등급을 한번에 받을 수있다고 착각하여 문제를 엄청 어렵게 풀고 있었던것은 안 비밀이다ㅜ)

-- 4.성공
WITH AA AS( -- 1.두 테이블 join하여 비트 연산으로 스킬 확인 & FLAG 
SELECT ID, EMAIL
        , SUM(IF (NAME='Python',1,0)) AS PY
        , SUM(IF (NAME='C#',1,0)) AS C
        , SUM(IF (CATEGORY='Front End',1,0)) AS FE
FROM (SELECT ID, EMAIL, SKILL_CODE, CODE, NAME, CATEGORY
        FROM DEVELOPERS D  
        LEFT JOIN SKILLCODES S
        ON D.SKILL_CODE>=S.CODE) A 
WHERE SKILL_CODE & CODE = CODE
GROUP BY 1,2
) -- 2.FlAG 별로 GRADE
SELECT CASE WHEN PY>0 AND FE>0 THEN 'A'
            WHEN C>0 THEN 'B'
            WHEN FE>0 THEN 'C'
        END AS GRADE
        , ID, EMAIL
FROM AA
WHERE FE!=0 OR C!=0
ORDER BY 1,2

 

 

 

 

-비트 연산법 사용 포스팅-

SQL 코테 연습 프로그래머스 Lv.2 정답률 45% 문제풀이