[TIL]_2025.03.11 본캠프 23일차 (2): 코드카타 SQL

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

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서
자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

• 조회하려는 데이터 : 자동차 ID, 자동차 종류, 대여금액(FEE)
• 조건 : 자동차 종류 세단 OR SUV, 2022-11-01 ~ 2022-11-30 대여 가능, 30일 대여금액 50 이상 200미만
• 정렬 : 대여 금액 DESC, 자동차 종류 ASC, 자동차 ID DESC

1. 11월 한 달 동안 내내 대여가 가능한 차량을 찾아 조회해야 함
2. 30일간의 대여 금액 50만원 이상 200만원 미만
   CAR_RENTAL_COMPANY_DISCOUNT_PLAN에 따라, 각 차량은 7일, 30일, 90일 이상 대여일 경우 각각의 할인율이 정해져 있기 때문에
   30일 이상인 경우만 필터링하여 30일의 할인율이 적용되게 함.

SELECT 
	CAR_ID, 
	CAR_TYPE, 
    CAST((DAILY_FEE * (100 - DISCOUNT_RATE) * 30 * 0.01) AS SIGNED) AS FEE
FROM
(SELECT 
    C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, D.DISCOUNT_RATE,
    CASE WHEN H.END_DATE < '2022-11-01' OR H.START_DATE > '2022-11-30' THEN 0
    ELSE 1
    END AS AVAIL -- 기간 내에 대여 기록이 있는 경우 1을 출력, 없는 경우 0을 출력하여 1이 있는 경우 대여를 못하는 것으로 필터링했다.
FROM 
    CAR_RENTAL_COMPANY_CAR C
JOIN
    CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
ON C.CAR_TYPE = D.CAR_TYPE
WHERE 
    C.CAR_TYPE IN('세단', 'SUV') 
    AND (D.DURATION_TYPE IN('30일 이상'))
 ) AS SUB
GROUP 
	BY CAR_ID
HAVING 
	MAX(AVAIL) = 0 
    AND FEE >= 500000 AND FEE < 2000000
ORDER BY 
	FEE DESC, C.CAR_TYPE ASC, CAR_ID DESC

다른 사람들의 답안을 확인해보니, WHERE절에 SELECT FROM 형태로 NOT IN을 사용할 수 있는 것을 알았다.
WHERE절에 NOT IN 조건을 추가하면 별도의 서브쿼리 없이 간단하게 조건에 맞는 행만 출력할 수 있다.

SELECT C.CAR_ID as CAR_ID, -- 자동차 ID
       C.CAR_TYPE as CAR_TYPE, -- 자동차 종류
       -- 대여료 x 30일 x 내야할 비율 = 요금
       -- (100 - 할인률)/100 = 내야할 비율
       ROUND(C.DAILY_FEE*30*(100-P.DISCOUNT_RATE)/100) AS FEE -- 지불 비용
    FROM CAR_RENTAL_COMPANY_CAR C
            -- C가 각각 연결될 수 있기 때문에 C를 기준으로 나머지 두 테이블을 연결한다.
            JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H ON C.CAR_ID = H.CAR_ID -- CAR_ID기준으로
            JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE -- CAR_TYPE기준으로
            
    -- 대여 가능한 2022-11-01 ~ 2022-12-01에 대여가 가능한 자동차를 목록을 가져와야하므로 
    -- NOT IN을 써서 해당 기간에 렌탈 기록이 없는 CAR_ID를 가져와야 한다
    WHERE C.CAR_ID NOT IN ( 
        SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
        WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-12-01'
        
) AND P.DURATION_TYPE like '30%' -- 그리고 대여 기간이 30일 이상인 것을 검색해야하므로
GROUP BY C.CAR_ID -- 자동차 ID 기준으로 그룹화하여
HAVING C.CAR_TYPE IN ('세단', 'SUV') -- 자동차 종류가 세단과 SUV인 것만
    AND (FEE >= 500000 AND FEE < 2000000) -- 30일간의 대여 금액이 50만원 200만원 미만인 자동차 
ORDER BY FEE DESC, CAR_TYPE, CAR_ID DESC

출처 : [프로그래머스] 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

---

75번. 자동차 대여 기록 별 대여 금액 구하기
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

• 조회하려는 데이터 : 대여기록별 대여 금액(FEE), 대여 기록 ID, 대여 금액 리스트
• 조건 : CAR_TYPE = '트럭'
• 정렬 : 대여 금액 DESC, 대여 기록 ID DESC
  CASE WHEN의 경우, 작성 순서에 따라서 조건을 평가하기 때문에 첫번째 줄의 조건이 충족되면 나머지 조건은 평가되지 않는다.
  현재 하나의 HISTORY_ID에 총 3개의 (7일 이상, 30일 이상, 90일 이상) 할인율이 부여되어 있기 때문에 각 조건별 FEE를 정확하게 계산하기 위해서는
  큰 값부터 작은 값 순서대로 배치한다.

SELECT HISTORY_ID,
CASE 
WHEN DURATION >= 90 THEN ROUND(DURATION * DAILY_FEE * 0.85)
WHEN DURATION >= 30 THEN ROUND(DURATION * DAILY_FEE * 0.92)
WHEN DURATION >= 7 THEN ROUND(DURATION * DAILY_FEE * 0.95)
WHEN DURATION < 7 THEN ROUND(DURATION * DAILY_FEE)
END AS FEE
FROM
(
    SELECT 
        C.CAR_ID,
        C.CAR_TYPE,
        C.DAILY_FEE,
        H.HISTORY_ID,
        D.DISCOUNT_RATE,
        DATEDIFF(H.END_DATE, H.START_DATE) + 1 AS DURATION
FROM CAR_RENTAL_COMPANY_CAR C
JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY H
ON C.CAR_ID = H.CAR_ID
JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
ON C.CAR_TYPE = D.CAR_TYPE
WHERE C.CAR_TYPE = '트럭'
) AS SUB
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC;

---

76번. 상품을 구매한 회원 비율 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중
상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을
년, 월 별로 출력하는 SQL문을 작성해주세요.
상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고,
전체 결과는 년을 기준으로 오름차순 정렬해주시고
년이 같다면 월을 기준으로 오름차순 정렬해주세요.

• 조회하려는 데이터 : 상품을 구매한 회원 수, 상품 구매 회원 비율
• 조건 : 2021년에 가입한 전체 회원들 중 / 년, 월별 / 비율은 소수점 두번째 자리에서 반올림
• 정렬 : 년 기준 ASC, 월 기준 ASC

2021년에 가입한 전체 회원 수 = WHERE YEAR(UI.JOINED) = '2021'
상품을 구매한 회원 수 = COUNT(DISTINCT OS.USER_ID)
여기서 회원 수 COUNT를 USER_ID 컬럼이 아니라, ONLINE_SALE_ID 칼럼으로 해서 당연히 DISTINCT가 안먹혔다.
⭐ 구매를 한 적이 있는 회원 수를 구하는 것이기 때문에 같은 월에 동일한 고객이 두 번 구매를 했어도 월별 집계에서는 1번으로 COUNT 된다.
또한 SELECT절에 스칼라 서브쿼리로 전체 유저 수를 구해서 구매한 적이 있는 회원 수를 나눌 수 있었다. (중요)

SELECT 
    YEAR(SALES_DATE) AS YEAR, 
    MONTH(SALES_DATE) AS MONTH, 
    COUNT(DISTINCT OS.USER_ID) AS PURCHASED_USERS,
    ROUND(COUNT(DISTINCT OS.USER_ID) / (SELECT COUNT(USER_ID)
    FROM USER_INFO
    WHERE YEAR(JOINED) = '2021'), 1) AS PUCHASED_RATIO
FROM (
    SELECT USER_ID, JOINED
    FROM USER_INFO) UI
INNER JOIN (
    SELECT USER_ID, ONLINE_SALE_ID, SALES_AMOUNT, SALES_DATE
    FROM ONLINE_SALE) AS OS
ON UI.USER_ID = OS.USER_ID
WHERE YEAR(UI.JOINED) = '2021'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

---

70번대가 넘어가니 슬슬 복잡해지고 요청하는 문제 자체를 이해하는 것도 어려워졌다.
문제에서 요구하는 바를 정확히 파악하는 연습이 필요하다.