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

1. 저자 별 카테고리 별 매출액 집계하기 (GROUP BY)  정답률 : 75%

프로그래머스

 

문제

2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

 

문제는 매우 평이한 group by 문제이다. 필요한 항목들을 가져오기 위해 join으로 묶고, 요건에 맞는 값을 구하기 위해 select 절에서 구하는 산식을 작성하고, group by를 통해 계산을 하고자하는 그룹을 지어주면된다. 

 

-- 1. 정답
SELECT A.AUTHOR_ID, AUTHOR_NAME, CATEGORY
    , SUM(PRICE*SALES) AS TOTAL_SALES
FROM BOOK B
LEFT JOIN AUTHOR A
    ON B.AUTHOR_ID=A.AUTHOR_ID
LEFT JOIN BOOK_SALES S
    ON B.BOOK_ID=S.BOOK_ID	
WHERE DATE_FORMAT(SALES_DATE,"%Y%m") = 202201
GROUP BY 1,2,3
ORDER BY AUTHOR_ID, CATEGORY DESC

 

 

 

2. 자동차 대여 기록 별 대여 금액 구하기(String,Date)  정답률 : 49%

프로그래머스

 

 

문제

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

 

처음엔 역시 데이터 테이블들을 join했다. 그 과정에서 두가지 중점적으로 처리하야하는 부분은 ①대여 일수에 맞는 할인율 가져오기 ②할인 없는 row에 공란 채우기이다. 

-- 1. 테이블 생성
SELECT HISTORY_ID, C.CAR_TYPE, DATEDIFF(END_DATE,START_DATE)+1 AS DATE_DIFF
         , DURATION_TYPE, DAILY_FEE
         , IF (DISCOUNT_RATE IS NULL, 0,DISCOUNT_RATE) AS D_RATE -- 할인 없는것 공란 채우기
FROM (SELECT *, 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일 이상'
                        END AS DURATION 
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)  H
 LEFT JOIN CAR_RENTAL_COMPANY_CAR C
        ON H.CAR_ID=C.CAR_ID
 LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
        ON C.CAR_TYPE=D.CAR_TYPE AND H.DURATION=D.DURATION_TYPE
WHERE C.CAR_TYPE='트럭'

 

다음으로는 테이블을 제대로 생성 했다면 생성한 항목들을 갖고 fee를 구해주면 끝이다. 

-- 2. fee 구하기
SELECT HISTORY_ID, ROUND(DATE_DIFF*(1-D_RATE/100)*DAILY_FEE) AS FEE
FROM (SELECT HISTORY_ID, C.CAR_TYPE, DATEDIFF(END_DATE,START_DATE)+1 AS DATE_DIFF
         , DURATION_TYPE, DAILY_FEE
         , IF (DISCOUNT_RATE IS NULL, 0,DISCOUNT_RATE) AS D_RATE
     FROM (
        SELECT *, 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일 이상'
                        END AS DURATION 
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)  H
    LEFT JOIN CAR_RENTAL_COMPANY_CAR C
        ON H.CAR_ID=C.CAR_ID
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN D
        ON C.CAR_TYPE=D.CAR_TYPE AND H.DURATION=D.DURATION_TYPE
    WHERE C.CAR_TYPE='트럭' ) A
ORDER BY 2 DESC, 1 DESC