[SQL] 프로그래머스 코딩테스트 : Lv4. (2)

 

Lv4. 그룹별 조건에 맞는 식당 목록 출력하기

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

WITH T1 AS (
    SELECT *
    FROM REST_REVIEW AS RR
    WHERE RR.MEMBER_ID = (SELECT MEMBER_ID
                        FROM REST_REVIEW
                        GROUP BY MEMBER_ID
                        ORDER BY COUNT(*) DESC
                        LIMIT 1)
)

SELECT MP.MEMBER_NAME, T1.REVIEW_TEXT
    , DATE_FORMAT(T1.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS MP
    INNER JOIN T1 ON MP.MEMBER_ID = T1.MEMBER_ID
ORDER BY REVIEW_DATE ASC, T1.REVIEW_TEXT ASC

❗DATE_FORMAT() 사용해서 출력 조건 맞추기
❗INNER JOIN 사용해서 테이블 결합
❗ORDER BY 절에 정렬 조건 맞추기

+) WITH 절 안에 WHERE 절 서브쿼리

❗GROUP BY, LIMIT 사용해서 ID 기준으로 가장 많은 카운트에 해당하는 ID 추출

+) WITH 절 서브쿼리

❗WHERE 절에 MEMBER_ID 기준으로 조건 걸기

 

Lv4. 오프라인/온라인 판매 데이터 통합하기

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

WITH T1 AS (
    SELECT SALES_DATE, PRODUCT_ID, USER_ID, SALES_AMOUNT
    FROM ONLINE_SALE AS ON_S
    WHERE SALES_DATE LIKE '2022-03%'

    UNION 

    SELECT SALES_DATE, PRODUCT_ID, NULL AS USER_ID,  SALES_AMOUNT 
    FROM OFFLINE_SALE AS OFF_S
    WHERE SALES_DATE LIKE '2022-03%'
)

SELECT DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DATE
    , PRODUCT_ID, USER_ID, SUM(SALES_AMOUNT) AS SALES_AMOUNT
FROM T1
GROUP BY SALES_DATE, PRODUCT_ID, USER_ID
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

❗DATE_FORMAT() 사용해서 출력 조건 맞추기
❗SUM() 집계함수 사용해서 출력 조건 맞추기
❗GROUP BY 절에 조건 맞추

+) WITH 절 안에 UNION 서브쿼리

❗같은 조건에 테이블만 다른 데이터 UNION으로 결합
❗오프라인 테이블은 USER_ID가 없어서 NULL값으로 넣기

 

Lv4. 입양 시각 구하기(2)

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

WITH RECURSIVE T1 AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR+1
    FROM T1
    WHERE HOUR < 23
)

SELECT T1.HOUR, COUNT(ANIMAL_ID) AS 'COUNT'
FROM T1 
    LEFT JOIN (SELECT *, HOUR(DATETIME) AS HOUR
                FROM ANIMAL_OUTS) AS T2 ON T1.HOUR = T2.HOUR
GROUP BY T1.HOUR
ORDER BY T1.HOUR ASC

❗RECURSIVE 절로 만든 HOUR와 기존 테이블을 결합
❗시간 기준으로 데이터 행 생성
❗참고

- https://school.programmers.co.kr/questions/72319

- https://20240228.tistory.com/274

- https://noaahhh.tistory.com/101

- https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC

- https://horang98.tistory.com/10

+) WITH RECURSIVE 절

+) LEFT JOIN 절 서브쿼리

❗HOUR() 사용해서 입양 시각을 추출

 

-- 실패
WITH T1 AS (
    SELECT *, HOUR(DATETIME) AS HOUR
    FROM ANIMAL_OUTS
)
SELECT HOUR, COUNT(*) AS 'COUNT'
FROM T1
GROUP BY HOUR
ORDER BY HOUR ASC

❗영업시간인 7-19시까지의 입양 시각만 나옴 -> 0시부터 나와야 함으로 고치기

 

Lv4. 특정 세대의 대장균 찾기

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

SELECT T3.ID
    -- T1.ID, T2.PARENT_ID, T2.ID, T3.PARENT_ID, T3.ID
FROM ECOLI_DATA AS T1
    LEFT JOIN ECOLI_DATA AS T2 ON T1.ID = T2.PARENT_ID
    LEFT JOIN ECOLI_DATA AS T3 ON T2.ID = T3.PARENT_ID
WHERE T1.PARENT_ID IS NULL
    AND T3.PARENT_ID IS NOT NULL
ORDER BY T3.ID

❗셀프 조인을 3번 해서 1세대 , 2세대, 3세대를 부모와 자식 관계로 연
❗WHERE 절에 3세대가 비어있지 않은 데이터 조건 걸기

+) LEFT JOIN 절

 

Lv4. 자동차 대여 기록 별 대여 금액 구하기

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

WITH T1 AS (
    SELECT CC.CAR_ID, CC.CAR_TYPE, CC.DAILY_FEE
        , CH.HISTORY_ID
        , DATEDIFF(CH.END_DATE,CH.START_DATE)+1 AS DURING
        , CASE 
            WHEN DATEDIFF(CH.END_DATE,CH.START_DATE)+1 < 7 THEN NULL
            WHEN DATEDIFF(CH.END_DATE,CH.START_DATE)+1 < 30 THEN '7일 이상'
            WHEN DATEDIFF(CH.END_DATE,CH.START_DATE)+1 < 90 THEN '30일 이상'
            ELSE '90일 이상'
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_CAR AS CC
        LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH ON CC.CAR_ID = CH.CAR_ID
            WHERE CAR_TYPE = '트럭'
), T2 AS (
    SELECT DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CP
    WHERE CAR_TYPE = '트럭'
)

SELECT HISTORY_ID
    , ROUND(SUM(T1.DAILY_FEE * (1 - (IFNULL(T2.DISCOUNT_RATE,0)/100)) * DURING),0) AS FEE
FROM T1
    LEFT JOIN T2 ON T1.DURATION_TYPE = T2.DURATION_TYPE
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC

❗ROUND(), SUM() 사용해서 출력 조건 맞추기
❗IFNULL() 사용해서 할인율이 비어있는 경우 0으로 채우고

+) WITH 절 서브쿼리 T1

❗DATEDIFF() 사용해서 DURATION_TYPE 생성
❗대여 시작일, 반납일 +1 해서 빌린 기간 일수 생성 (같은 날에 대여, 반납해도 1일로 취급하기 때문에 +1)
❗LEFT JOIN 사용해서 대여 기록이 있는 차 중에 트럭인 것들만 추출

+) WITH 절 서브쿼리 T2

❗할인 계획 테이블에서 트럭인 것만 추출

❗참고

- https://keepgoin9.tistory.com/7

- https://noaahhh.tistory.com/106

 

-- 실패
WITH T1 AS (
    SELECT CC.CAR_ID, CC.CAR_TYPE, CC.DAILY_FEE
        , CH.HISTORY_ID
        , DATEDIFF(CH.END_DATE,CH.START_DATE)+1 AS DURING
    FROM CAR_RENTAL_COMPANY_CAR AS CC
        LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH ON CC.CAR_ID = CH.CAR_ID
    WHERE CAR_TYPE = '트럭'
)

SELECT *
    , REGEXP '[0-9]'
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CP
WHERE CP.CAR_TYPE = '트럭'

❗DATEDIFF() 사용해서 대여 기간 구하기
❗REGEXP 사용해서 글자에서 숫자 추출하기 -> 실패...

 

Lv4. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

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

-- 참고한 다른 사람 코드
SELECT 
    C.CAR_ID, 
    C.CAR_TYPE, 
    FLOOR(C.DAILY_FEE * 30 * (1 - DP.DISCOUNT_RATE / 100)) AS FEE
FROM 
    CAR_RENTAL_COMPANY_CAR C
LEFT JOIN 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY RH 
    ON C.CAR_ID = RH.CAR_ID 
    AND RH.START_DATE <= '2022-11-30' 
    AND RH.END_DATE >= '2022-11-01'
JOIN 
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN DP 
    ON C.CAR_TYPE = DP.CAR_TYPE 
    AND DP.DURATION_TYPE = '30일 이상'
WHERE 
    C.CAR_TYPE IN ('세단', 'SUV')
    AND RH.HISTORY_ID IS NULL  -- 2022년 11월 1일~30일 사이에 대여 이력이 없는 자동차만 선택
HAVING 
    FEE >= 500000 AND FEE < 2000000
ORDER BY 
    FEE DESC, 
    C.CAR_TYPE ASC, 
    C.CAR_ID DESC;

❗참고
https://20240228.tistory.com/262
https://school.programmers.co.kr/questions/81427
https://noaahhh.tistory.com/107
https://school.programmers.co.kr/questions/79736

 

-- 1차 실패

WITH T1 AS (
    SELECT CAR_ID
        , START_DATE
        , END_DATE
        , CASE 
            WHEN '2022-11-01' BETWEEN START_DATE AND END_DATE THEN '대여중'
            ELSE '대여 가능'
        END AS SA
        , CASE 
            WHEN '2022-11-30' BETWEEN START_DATE AND END_DATE THEN '대여중'
            ELSE '대여 가능'
        END AS EA
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH
), T2 AS (
    SELECT CAR_ID
        , START_DATE, END_DATE
        , MAX(SA) AS SA
        , MAX(EA) AS EA
    FROM T1
    WHERE SA = '대여 가능' AND EA = '대여 가능'
    GROUP BY CAR_ID
    ORDER BY CAR_ID
), T3 AS (
    SELECT CAR_ID, CAR_TYPE, DAILY_FEE
    FROM CAR_RENTAL_COMPANY_CAR AS CC
    WHERE CAR_TYPE IN ('세단','SUV')
        AND CAR_ID IN (SELECT CAR_ID FROM T2)
), T4 AS (
    SELECT * 
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CP
    WHERE CAR_TYPE IN ('세단','SUV')
        AND DURATION_TYPE = '30일 이상'
)

SELECT T3.CAR_ID, T3.CAR_TYPE
    , ROUND((100 - T4.DISCOUNT_RATE) / 100 * 30 * T3.DAILY_FEE) AS FEE
FROM T3 
    LEFT JOIN T4 ON T3.CAR_TYPE = T4.CAR_TYPE
WHERE FEE >=500000 AND FEE  <2000000
ORDER BY FEE DESC, T3.CAR_TYPE ASC, T3.CAR_ID DESC

❗CASE WHEN 을 사용해서 대여 시작일, 반납일이 둘 다 대여 완료인 것만 추출.
❗여러 날짜가 나오는 데, 거기서 최대값만 비교

 

-- 2차 작성 코드
WITH T1 AS (
    SELECT CC.CAR_ID, CC.CAR_TYPE, CC.DAILY_FEE
        , CH.HISTORY_ID, CH.START_DATE, CH.END_DATE
        , CP.DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_CAR AS CC
        INNER JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH ON CC.CAR_ID = CH.CAR_ID
        LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CP ON CC.CAR_TYPE = CP.CAR_TYPE
    WHERE CC.CAR_TYPE IN ('세단','SUV')
        AND CP.DURATION_TYPE = '30일 이상'
        AND '2022-11-01' BETWEEN START_DATE AND END_DATE
        AND '2022-11-30' BETWEEN START_DATE AND END_DATE
)

SELECT CAR_ID, CAR_TYPE, ROUND((DAILY_FEE * (1 - DISCOUNT_RATE/100))* 30) AS FEE
FROM T1
GROUP BY CAR_ID
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

❗대여 시작일과 반납일을 BETWEEN으로 조건
❗조건 잘못 걸었다 (대여중인 CAR_ID 기준으로 결과가 나옴) -> 당연히 오답

 

-- 3차 실패

WITH T1 AS (
    SELECT CAR_ID -- 빌릴 수 없는 CAR_ID
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS CH
    WHERE '2022-11-01' BETWEEN START_DATE AND END_DATE
        AND '2022-11-30' BETWEEN START_DATE AND END_DATE
)

SELECT CC.CAR_ID, CC.CAR_TYPE
    , ROUND(CC.DAILY_FEE * (1-CP.DISCOUNT_RATE/100)*30) AS FEE
-- 전체 보유 차 - 빌릴 수 없는 CAR_ID & CAR_TYPE = 세단, SUV = 세단, SUV이면서 빌릴 수 있는 차 
FROM CAR_RENTAL_COMPANY_CAR AS CC
    INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS CP ON CC.CAR_TYPE = CP.CAR_TYPE
WHERE CC.CAR_ID NOT IN (SELECT * FROM T1)
    AND CC.CAR_TYPE IN ('SUV','세단')
    AND CP.DURATION_TYPE = '30일 이상'
GROUP BY CAR_ID
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CC.CAR_TYPE ASC, CC.CAR_ID DESC

❗기록에 없지만 보유하고 있고, 대여 가능한 차가 있을 수 있다! (보유하고 있는 CAR_ID != 기록이 있는 CAR_ID)

❗기간에 빌릴 수 없는 차 찾기
❗전체 차 명단에서 빼기!

❗정답 코드와 출력값 비교했는데 출력값은 같다... 뭐가 틀린 거지?

 

Lv4. FrontEnd 개발자 찾기

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

WITH T1 AS (
    SELECT SUM(CODE) AS S_C -- 비트 연산자로 계산할 것이라 SUM해도 문제 없음!
    FROM SKILLCODES
    WHERE CATEGORY = 'FRONT END'
)

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME -- * 
FROM DEVELOPERS
WHERE SKILL_CODE & (SELECT * FROM T1) != 0
ORDER BY ID ASC

❗WHERE 절에 WITH 절로 만든 서브쿼리 결과와 비교하기

+) WITH 절 서브쿼리 T1

❗SUM() 사용해서 CODE의 합 구하기
❗비트 연산자로 계산할 것이라 SUM해도 문제 없음!

❗참고

• https://noaahhh.tistory.com/108
• https://20240228.tistory.com/261

 

-- 실패
WITH T1 AS (
    SELECT *
        , SKILL_CODE & 16 AS S1
        , SKILL_CODE & 2048 AS S2
        , SKILL_CODE & 8192 AS S3
    FROM DEVELOPERS AS D
)
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM T1
WHERE S1!= 0 OR S2 != 0 OR S3 != 0
ORDER BY ID ASC

❗WITH 절에 각 조건별로 비트 연산 시도 -> 실패
❗CODE 테이블이 바뀌면 반영이 안 됨

 

Lv4. 언어별 개발자 분류하기

WITH T1 AS (
    SELECT SUM(CODE)
    FROM SKILLCODES
    WHERE CATEGORY = 'FRONT END'
), T2 AS (
    SELECT *
        , SKILL_CODE & (SELECT * FROM T1) AS FE
        , SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'PYTHON') AS P
        , SKILL_CODE & (SELECT CODE FROM SKILLCODES WHERE NAME = 'C#') AS C
    FROM DEVELOPERS
), T3 AS (
    SELECT *
        , CASE
            WHEN FE != 0 AND P != 0 THEN 'A'
            WHEN C != 0 THEN 'B'
            WHEN FE != 0 THEN 'C'
        END AS GRADE
    FROM T2
)

SELECT GRADE, ID, EMAIL
FROM T3
WHERE GRADE IS NOT NULL
ORDER BY GRADE, ID

❗WHERE 절에 IS NOT NULL 사용해서 GRADE가 존재하는 행만 추출

+) WITH 절 서브쿼리 T1

❗프론트엔드에 해당하는 기술을 모두 더함
❗비트 연산자로 계산할 것이라 SUM해도 문제 없음!

+) WITH 절 서브쿼리 T2

❗프론트엔드 기술 코드, 파이썬 코드, C#코드에 해당하는지 비트 연산

+) WITH 절 서브쿼리 T3

❗T2에서 만든 테이블 칼럼 기준으로 GRADE 생성
❗모든 조건에 해당하지 않으면 GRADE가 NULL값으로 생성됨