240718

SQL

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

이틀간 풀었다..

WITH TRUCK AS(
SELECT H.*, 
       C.DAILY_FEE,
       DATEDIFF(END_DATE, START_DATE) + 1 DURATION,
       CASE WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
            ELSE '해당 없음' END  duration_type     # 할인하지 않는 경우를 꼭 기억해라. 이것때문에 30분 더 걸림. 
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID AND C.CAR_TYPE = '트럭'    # CAR TABLE '트럭' 필터링
), TRUCK2 AS(
SELECT 
    HISTORY_ID,
    CASE WHEN T.DURATION_TYPE = '해당 없음' THEN DAILY_FEE * DURATION 
         WHEN T.DURATION_TYPE IN ('7일 이상','30일 이상','90일 이상') THEN DAILY_FEE * DURATION * (1-discount_rate/100)
         ELSE 0 END FEE
FROM TRUCK T LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON T.DURATION_TYPE = P.DURATION_TYPE AND P.CAR_TYPE = '트럭'  # PLAN TABLE '트럭' 필터링
ORDER BY 2 DESC, 1 DESC)

SELECT HISTORY_ID, ROUND(FEE) # ROUNDING을 위한... MULTI CTE 활용
FROM TRUCK2

조건이 많은 문제를 풀 때는 "조건에 해당하지 않는 값" 을 어떻게 처리해야 하는지를 생각하고 쿼리를 작성하자.

위 문제의 경우 할인을 받지 않는 7일 미만의 대여 데이터가 절대 다수였는데 해당 경우를 쿼리에 포함하지 않아 정답 테이블 데이터의 거의 70퍼센트를 날려버릴뻔 했다.

실제 현업 데이터였으면 시말서 감이다. 목적을 분명하게 세운 뒤에 분석하자.

76번. 상품 구매 회원 비율 구하기

• 나의 정답(이지만 뭔가 비효율적인) 쿼리

WITH 
SUM_2021 AS(
SELECT COUNT(DISTINCT(USER_ID))
FROM USER_INFO                     # 2021년 가입한 전체 회원 수
WHERE SUBSTR(JOINED,1,4) = '2021') # 158명
,
SUM_PURCHASED_2021 AS(
SELECT COUNT(DISTINCT(S.USER_ID))
FROM ONLINE_SALE S JOIN USER_INFO U
ON S.USER_ID = U.USER_ID             # 2021년 가입 회원 중 구매한 회원 수
WHERE SUBSTR(U.JOINED,1,4) = '2021') # 83명

SELECT 
    YEAR(S.SALES_DATE) YEAR
    , MONTH(S.SALES_DATE) MONTH
    , COUNT(DISTINCT(S.USER_ID)) PURCHASED_USERS
    , ROUND(COUNT(DISTINCT(S.USER_ID))/(SELECT * FROM SUM_2021),1) PUCHASED_RATIO # 비율 구하기
FROM ONLINE_SALE S JOIN USER_INFO U
ON S.USER_ID = U.USER_ID
WHERE SUBSTR(U.JOINED,1,4) = '2021'
GROUP BY 
    YEAR(S.SALES_DATE),
    MONTH(S.SALES_DATE)
ORDER BY 1, 2

비율을 구하는 과정이 복잡하여 with로 각 변수(2021 가입 회원 수, 2021 가입 회원 중 구매 회원 수) 를 각각 구해놓고 시작했다.

한 눈에 보기에도 비효율적이다...

• 다른 사람의 효율적 쿼리

WITH JOIN_2021 AS(
SELECT COUNT(*) USERS FROM USER_INFO
WHERE YEAR(JOINED) = 2021)
,
PUCHASED_2021 AS(
SELECT 
	YEAR(SALES_DATE) AS YEAR, 
    MONTH(SALES_DATE) AS MONTH, 
    COUNT(DISTINCT(A.USER_ID)) AS PUCHASED_USERS 
FROM ONLINE_SALE A JOIN (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021) B
ON A.USER_ID = B.USER_ID
GROUP BY YEAR, MONTH)

SELECT P.YEAR, P.MONTH, P.PUCHASED_USERS, ROUND(P.PUCHASED_USERS/J.USERS,1) AS PUCHASED_RATIO
FROM PUCHASED P, JOIN_2021 J
ORDER BY YEAR, MONTH

개념

코딩 팁

직접 쓰면서 해봐라.
work flow를 그리면서 하면 훨씬 괜찮다.