프로그래머스 MySQL Lv.4 (2)

Journey to Data Analyst·2023년 4월 29일

mysql 데이터 분석 쿼리 프로그래머스

프로그래머스 MySQL

목록 보기

7/7

이제 Lv.4의 마지막 포스팅을 시작해보려고 한다.

프로그래머스는 Lv.5까지 있지만 Lv.5는 한 문제밖에 없어
프로그래머스 쿼리 포스팅은 이게 마지막일 것 같다.
그럼 마지막을 향해 Go Go!

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

문제 설명

다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 CAR_RENTAL_COMPANY_CAR 테이블과 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 자동차 종류 별 대여 기간 종류 별 할인 정책 정보를 담은 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블 입니다.

CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며,
CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

Column name	Type	Nullable
CAR_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DAILY_FEE	INTEGER	FALSE
OPTIONS	VARCHAR(255)	FALSE

자동차 종류는 '세단', 'SUV', '승합차', '트럭', '리무진' 이 있습니다. 자동차 옵션 리스트는 콤마(',')로 구분된 키워드 리스트(예: ''열선시트,스마트키,주차감지센서'')로 되어있으며, 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며,
HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며,
PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE 는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.

Column name	Type	Nullable
PLAN_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DURATION_TYPE	VARCHAR(255)	FALSE
DISCOUNT_RATE	INTEGER	FALSE

할인율이 적용되는 대여 기간 종류로는 '7일 이상' (대여 기간이 7일 이상 30일 미만인 경우), '30일 이상' (대여 기간이 30일 이상 90일 미만인 경우), '90일 이상' (대여 기간이 90일 이상인 경우) 이 있습니다. 대여 기간이 7일 미만인 경우 할인정책이 없습니다.

문제

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를 기준으로 내림차순 정렬해주세요.

예시

예를 들어
CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 다음과 같다면

CAR_ID	CAR_TYPE	DAILY_FEE	OPTIONS
1	SUV	25000	가죽시트,열선시트,후방카메라
2	세단	14000	스마트키,네비게이션,열선시트
3	트럭	32000	주차감지센서,후방카메라,가죽시트
4	세단	12000	열선시트,후방카메라
5	세단	22000	스마트키,주차감지센서

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	1	2022-08-27	2022-09-02
2	1	2022-10-03	2022-10-04
3	2	2022-10-05	2022-10-20
4	2	2022-10-10	2022-11-12
5	3	2022-10-16	2022-10-17

PLAN_ID	CAR_TYPE	DURATION_TYPE	DISCOUNT_RATE
1	트럭	7일 이상	5%
2	트럭	30일 이상	7%
3	트럭	90일 이상	10%
4	세단	7일 이상	5%
5	세단	30일 이상	10%
6	세단	90일 이상	15%
7	SUV	7일 이상	3%
8	SUV	30일 이상	8%
9	SUV	90일 이상	12%

자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일 부터 2022년 11월 30일까지 대여가능한 자동차는 자동차 ID가 1, 4, 5인 자동차입니다.

일일 대여 요금에 자동차 종류 별 대여기간이 30일 이상인 경우의 할인율을 적용하여 30일간의 대여 금액을 구하면,

자동차 ID가 1인 경우, 일일 대여 금액 25,000원에서 8% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 690,000원
자동차 ID가 4인 경우, 일일 대여 금액 12,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 324,000원
자동차 ID가 5인 경우, 일일 대여 금액 22,000원에서 10% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 621,000원이고, 대여 금액이 50만원 이상 200만원 미만인 경우에 대해서 대여 금액을 기준으로 내림차순, 자동차 종류를 기준으로 오름차순 및 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.

CAR_ID	CAR_TYPE	FEE
5	세단	690000
1	SUV	621000

주의사항

FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.

Answer

-- 초기 코드
SELECT c.CAR_ID, c.CAR_TYPE, c.DAILY_FEE AS FEE
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 p
ON c.CAR_TYPE = p.CAR_TYPE

WHERE c.CAR_TYPE IN ('세단', 'SUV')
AND h.START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
AND p.DURATION_TYPE = '30일 이상'
AND c.DAILY_FEE BETWEEN 500000 AND 2000000

GROUP BY 1, 2
ORDER BY c.DAILY_FEE DESC, c.CAR_TYPE, c.CAR_ID DESC;

-- 최종 답안
SELECT c.CAR_ID, c.CAR_TYPE, 
       CAST(c.DAILY_FEE * (100 - p.DISCOUNT_RATE) / 100 * 30 AS signed) AS FEE
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 p
ON c.CAR_TYPE = p.CAR_TYPE

WHERE c.CAR_TYPE IN ('세단', 'SUV')
AND p.DURATION_TYPE = '30일 이상'
AND CAST(c.DAILY_FEE * (100 - p.DISCOUNT_RATE) / 100 * 30 AS signed) BETWEEN 500000 AND 2000000

GROUP BY 1
HAVING MAX(h.END_DATE) < '2022-11-01'
ORDER BY c.DAILY_FEE DESC, c.CAR_TYPE, c.CAR_ID DESC;

처음에는 FEE를 단순하게 구하려고 했지만 할인율이 있다는 것을 캐치하고 난 후 부터는 할인율을 적용하여 구하려했지만 생각보다 어려웠다.
그래서 타인의 답안을 참고하여 CAST 함수를 사용하였다.
CAST ... AS signed는 문자를 정수(음수/양수 구분)로 변환해준다.

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

문제 설명

CAR_RENTAL_COMPANY_CAR 테이블은 아래와 같은 구조로 되어있으며, CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS 는 각각 자동차 ID, 자동차 종류, 일일 대여 요금(원), 자동차 옵션 리스트를 나타냅니다.

Column name	Type	Nullable
CAR_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DAILY_FEE	INTEGER	FALSE
OPTIONS	VARCHAR(255)	FALSE

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블은 아래와 같은 구조로 되어있으며, PLAN_ID, CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE 는 각각 요금 할인 정책 ID, 자동차 종류, 대여 기간 종류, 할인율(%)을 나타냅니다.

Column name	Type	Nullable
PLAN_ID	INTEGER	FALSE
CAR_TYPE	VARCHAR(255)	FALSE
DURATION_TYPE	VARCHAR(255)	FALSE
DISCOUNT_RATE	INTEGER	FALSE

문제

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

예시

예를 들어 CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블이 다음과 같다면

CAR_ID	CAR_TYPE	DAILY_FEE	OPTIONS
1	트럭	26000	가죽시트,열선시트,후방카메라
2	SUV	14000	스마트키,네비게이션,열선시트
3	트럭	32000	주차감지센서,후방카메라,가죽시트

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	1	2022-07-27	2022-08-02
2	1	2022-08-03	2022-08-04
3	2	2022-08-05	2022-08-05
4	2	2022-08-09	2022-08-12
5	3	2022-09-16	2022-10-15

PLAN_ID	CAR_TYPE	DURATION_TYPE	DISCOUNT_RATE
1	트럭	7일 이상	5%
2	트럭	30일 이상	7%
3	트럭	90일 이상	10%
4	세단	7일 이상	5%
5	세단	30일 이상	10%
6	세단	90일 이상	15%

자동차 종류가 '트럭' 인 자동차의 대여 기록에 대해서 대여 기간을 구하면,

대여 기록 ID가 1인 경우, 7일
대여 기록 ID가 2인 경우, 2일
대여 기록 ID가 5인 경우, 30일입니다.

대여 기간 별로 일일 대여 요금에 알맞은 할인율을 곱하여 금액을 구하면 다음과 같습니다.

대여 기록 ID가 1인 경우, 일일 대여 금액 26,000원에서 5% 할인율을 적용하고 7일을 곱하면 총 대여 금액은 172,900원
대여 기록 ID가 2인 경우, 일일 대여 금액 26,000원에 2일을 곱하면 총 대여 금액은 52,000원
대여 기록 ID가 5인 경우, 일일 대여 금액 32,000원에서 7% 할인율을 적용하고 30일을 곱하면 총 대여 금액은 892,800원이 되므로, 대여 금액을 기준으로 내림차순 정렬 및 대여 기록 ID를 기준으로 내림차순 정렬하면 다음과 같아야 합니다.

HISTORY_ID	FEE
5	892800
1	172900
2	52000

주의사항

FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.

Answer

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

/* 
자동차 종류가 세단, SUV - c,p car_type  
11월 1일부터 11월 30일까지 대여 가능하고 - h start_date
30일간 대여 금액이 50만원 이상 200만원 미만 - 30일 이상(DISCOUNT_TYPE), DISCOUNT_RA
*/
SELECT c.CAR_ID, c.CAR_TYPE, c.DAILY_FEE AS FEE
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 p
ON c.CAR_TYPE = p.CAR_TYPE

WHERE c.CAR_TYPE IN ('세단', 'SUV')
AND h.START_DATE BETWEEN '2022-11-01' AND '2022-11-30'
AND p.DURATION_TYPE = '30일 이상'
AND c.DAILY_FEE BETWEEN 500000 AND 1999999

GROUP BY 1, 2
ORDER BY c.DAILY_FEE DESC, c.CAR_TYPE, c.CAR_ID DESC;

-- Duration type별 쿼리
SELECT h.HISTORY_ID,
    CASE 
        WHEN p.DURATION_TYPE = '7일 이상'
            THEN SUM((c.DAILY_FEE * p.DISCOUNT_RATE) * 5)
        WHEN p.DURATION_TYPE = '30일 이상'
            THEN SUM((c.DAILY_FEE * p.DISCOUNT_RATE) * 8)
        WHEN p.DURATION_TYPE = '90일 이상'
            THEN SUM((c.DAILY_FEE * p.DISCOUNT_RATE) * 15)
        ELSE SUM((c.DAILY_FEE * p.DISCOUNT_RATE))
        END AS FEE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN p

LEFT JOIN CAR_RENTAL_COMPANY_CAR c
ON p.CAR_TYPE = c.CAR_TYPE
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
ON c.CAR_ID = h.CAR_ID

WHERE p.CAR_TYPE = '트럭'
GROUP BY 1
ORDER BY c.DAILY_FEE DESC, h.HISTORY_ID DESC;

-- Duration type이 date diff값과 일치하는지 확인 -> 일치 x
SELECT c.CAR_TYPE, h.HISTORY_ID, p.DURATION_TYPE, p.DISCOUNT_RATE,
       (h.END_DATE - h.START_DATE)
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN p

LEFT JOIN CAR_RENTAL_COMPANY_CAR c
ON p.CAR_TYPE = c.CAR_TYPE
LEFT JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY h
ON c.CAR_ID = h.CAR_ID

WHERE c.CAR_TYPE = '트럭'
ORDER BY h.END_DATE - h.START_DATE ;

-- WITH DATEDIFF 테이블이 잘 동작하는지 확인
WITH DATEDIFF AS
(SELECT CAR_ID, HISTORY_ID, (END_DATE - START_DATE) AS DIFF FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)

SELECT * FROM DATEDIFF

-- 간단한 정답
SELECT HISTORY_ID , 
CASE WHEN DIFF < 7 THEN ROUND(DAILY_FEE * DIFF)
     WHEN DIFF < 30 THEN ROUND(DAILY_FEE * 0.95 * DIFF) 
     WHEN DIFF < 90 THEN ROUND(DAILY_FEE * 0.92 * DIFF) 
     ELSE ROUND(DAILY_FEE * 0.85 * DIFF) END FEE

FROM (SELECT * FROM CAR_RENTAL_COMPANY_CAR WHERE CAR_TYPE = '트럭') c
INNER JOIN (SELECT  *, DATEDIFF(END_DATE, START_DATE) + 1 AS DIFF FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY) h
ON c.CAR_ID = h.CAR_ID
GROUP BY HISTORY_ID
ORDER BY FEE DESC, HISTORY_ID DESC

이번에는 이 문제를 풀기 위해 작성한 모든 쿼리를 다 적어보았다.
역시 처음 초기 쿼리는 간단하게 구할 수 있는 조건들을 모두 때려넣어 작성하였다.
하지만 Duration Type별로 Discount Rate 가 다른것을 보고 그것들을 적용하여 CASE WHEN 쿼리문을 작성하였다.
그러나 Duration Type과 빌린 날짜와 반납 날짜의 차이가 맞지 않는 것 같아 확인을 해보니 역시 맞지 않았다.
따라서 날짜 차이를 따로 구하는 DATEDIFF 테이블을 WITH구문으로 만들어본 뒤
이를 적용하여 정답 쿼리를 작성하였다.

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

문제 설명

다음은 식당의 정보를 담은 REST_INFO테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다.
MEMBER_PROFILE 테이블은 다음과 같으며
MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH는 회원 ID, 회원 이름, 회원 연락처, 성별, 생년월일을 의미합니다.

Column name	Type	Nullable
MEMBER_ID	VARCHAR(100)	FALSE
MEMBER_NAME	VARCHAR(50)	FALSE
TLNO	VARCHAR(50)	TRUE
GENDER	VARCHAR(1)	TRUE
DATE_OF_BIRTH	DATE	TRUE

REST_REVIEW 테이블은 다음과 같으며
REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.

Column name	Type	Nullable
REVIEW_ID	VARCHAR(10)	FALSE
REST_ID	VARCHAR(10)	TRUE
MEMBER_ID	VARCHAR(100)	TRUE
REVIEW_SCORE	NUMBER	TRUE
REVIEW_TEXT	VARCHAR(1000)	TRUE
REVIEW_DATE	DATE	TRUE

문제

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요.
회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

예시

MEMBER_PROFILE 테이블이 다음과 같고

MEMBER_ID	MEMBER_NAME	TLNO	GENDER	DATE_OF_BIRTH
jiho92@naver.com	이지호	01076432111	W	1992-02-12
jiyoon22@hotmail.com	김지윤	01032324117	W	1992-02-22
jihoon93@hanmail.net	김지훈	01023258688	M	1993-02-23
seoyeons@naver.com	박서연	01076482209	W	1993-03-16
yelin1130@gmail.com	조예린	01017626711	W	1990-11-30

REST_REVIEW 테이블이 다음과 같을 때

REVIEW_ID	REST_ID	MEMBER_ID	REVIEW_SCORE	REVIEW_TEXT	REVIEW_DATE
R000000065	00028	soobin97@naver.com	5	부찌 국물에서 샤브샤브 맛이나고 깔끔	2022-04-12
R000000066	00039	yelin1130@gmail.com	5	김치찌개 최곱니다.	2022-02-12
R000000067	00028	yelin1130@gmail.com	5	햄이 많아서 좋아요	2022-02-22
R000000068	00035	ksyi0316@gmail.com	5	숙성회가 끝내줍니다.	2022-02-15
R000000069	00035	yoonsy95@naver.com	4	비린내가 전혀없어요.	2022-04-16

SQL을 실행하면 다음과 같이 출력되어야 합니다.

MEMBER_NAME	REVIEW_TEXT	REVIEW_DATE
조예린	김치찌개 최곱니다.	2022-02-12
조예린	햄이 많아서 좋아요	2022-02-22

주의사항

REVIEW_DATE의 데이트 포맷이 예시와 동일해야 정답처리 됩니다.

Answer

SELECT m.MEMBER_NAME, r.REVIEW_TEXT, 
       DATE_FORMAT(r.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE m
LEFT JOIN REST_REVIEW r
ON m.MEMBER_ID = r.MEMBER_ID
WHERE m.MEMBER_ID IN 
    (SELECT MEMBER_ID
     FROM (SELECT MEMBER_ID, COUNT(MEMBER_ID), REVIEW_TEXT, REVIEW_DATE, 
                  DENSE_RANK () OVER (ORDER BY COUNT(MEMBER_ID) DESC) AS rk
           FROM REST_REVIEW
           GROUP BY 1) r
     WHERE rk = 1)
ORDER BY r.REVIEW_DATE ASC, r.REVIEW_TEXT ASC

간단해보였지만 생각보다 간단한 문제가 아니였다.
일단 GROUP BY와 LIMIT를 사용하여 가장 많이 리뷰를 작성한 사람의 MEMBER_ID를 구하려고 했지만 가장 많은 리뷰를 쓴 사람이 두 명 이상일 수 있어 다른 방법을 생각해야했다.
이 때 사용한 함수가 DENSE_RANK였다. 이는 중복된 랭킹 값을 동일하게 반환해주는 함수로 아주 유용하게 썼다.

이제 프로그래머스의 모든 포스팅이 마무리 되었다.
남은 무료 플랫폼 문제는 Hackerrank 뿐인데 문제를 푼지가 오래되어서
한번 다시 풀고 업로드 할 것을 정리해봐야할 것 같다.
그럼 그때까지...

Journey to Data Analyst

성장하는 주니어 데이터 분석가(Tableau, SQL and Python)

이전 포스트

프로그래머스 MySQL Lv.4 (2)