240816(+72) | SQL 코드카타 | 75(COALESCE),76

COALESCE

여러 값을 인자로 받아 가장 첫번째로 'NULL'이 아닌 값을 반환한다.

SELECT COALESCE(NULL, NULL, 'HeHe', 'CAT') AS Result;
# NULL 2개를 건너 뛰고 HeHe를 반환한다.

Q.75-2 차량 대여 금액

• CAR_RENTAL_COMPANY_DISCOUNT_PLAN에서 대여 기간 별 DISCOUNT_RATE를 가져오는데
• 7일 미만의 대여 기간은 NULL값이기 때문에 출력되지 않는다.
• 7일 미만의 대여 기간에 대한 값도 계산 되어야 하기 때문에
• 7일 미만에 대한 DISCOUNT_RATE의 반환 값을 할당해준다.
• COALESCE 함수로 특정 기간에 맞는 할인율을 가져오고, 해당하는 할인율이 없을 경우 0을 반환

---

STEP_1 7일 미만의 DURATION_TYPE 대한 DISCOUNT_RATE 쿼리

    SELECT 
        h.HISTORY_ID
        , h.CAR_ID
        , DATE_FORMAT(START_DATE,'%Y-%m-%d') START
        , DATE_FORMAT(END_DATE,'%Y-%m-%d') END        
        , DATEDIFF(h.END_DATE, h.START_DATE) + 1 AS RENTAL_PERIOD
        , COALESCE(
            (SELECT d.DISCOUNT_RATE
             FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN d
             WHERE d.CAR_TYPE = c.CAR_TYPE
               AND d.DURATION_TYPE = 
                   CASE 
                       WHEN RENTAL_PERIOD >= 90 THEN '90일 이상'
                       WHEN RENTAL_PERIOD >= 30 THEN '30일 이상'
                       WHEN RENTAL_PERIOD >= 7 THEN '7일 이상'
                       ELSE NULL #'7일 미만'
                   END),
            0
        ) AS DISCOUNT_RATE
    FROM 
        CAR_RENTAL_COMPANY_RENTAL_HISTORY h
        JOIN CAR_RENTAL_COMPANY_CAR c USING(CAR_ID)
    WHERE 
        c.CAR_TYPE = '트럭'

• 7일 미만의 DISCOUNT_RATE가 0으로 반환됨을 확인.

---

STEP_2

WITH DT AS (
    SELECT        
         HISTORY_ID,
         CAR_ID,
         DATEDIFF(END_DATE, START_DATE) + 1 AS RENTAL_PERIOD,
         CASE 
             WHEN RENTAL_PERIOD + 1 >= 90 THEN '90일 이상'
             WHEN RENTAL_PERIOD + 1 >= 30 THEN '30일 이상'
             WHEN RENTAL_PERIOD + 1 >= 7 THEN '7일 이상'
             ELSE '7일 미만'
         END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY h
)
# main_query
SELECT 
    dc.HISTORY_ID,
    FLOOR((dc.RENTAL_PERIOD * c.DAILY_FEE) * 
         (1 - COALESCE(d.DISCOUNT_RATE, 0) / 100)) AS FEE
FROM 
    DT dc
JOIN CAR_RENTAL_COMPANY_CAR c ON c.CAR_ID = dc.CAR_ID 
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN d 
    ON d.CAR_TYPE = c.CAR_TYPE
    AND d.DURATION_TYPE = dc.DURATION_TYPE
WHERE 
    c.CAR_TYPE = '트럭'
ORDER BY 
    FEE DESC, 
    dc.HISTORY_ID DESC

Q.76 상품 구매 회원 비율

• 년,월 별로 출력 (YEAR ASC, MONTH ASC)
• 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수
• 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)
• 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림

SELECT 
    YEAR(s.SALES_DATE) AS YEAR,
    MONTH(s.SALES_DATE) AS MONTH,
    COUNT(DISTINCT s.USER_ID) AS PURCHASED_USERS,
    ROUND(
        COUNT(DISTINCT s.USER_ID) * 1.0 / 
        (SELECT COUNT(*) 
         FROM USER_INFO 
         WHERE YEAR(JOINED) = 2021), #2021년에 가입한 전체 회원수 서브쿼리
        1
    ) AS PURCHASED_RATIO
FROM ONLINE_SALE s
JOIN USER_INFO u ON s.USER_ID = u.USER_ID
WHERE YEAR(u.JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH;