[SQL/오답] 기간별 할인 로직 구현; WITH 절과 LEFT JOIN의 조화 (프로그래머스 Lv4)

 

1. Problem

자동차 종류가 '트럭'인 대여 기록들에 대해 각각의 최종 대여 금액(FEE)을 구해야 한다. 대여 기간에 따라 할인율이 다르게 적용되며, 7일 미만 대여 시에는 할인 정책이 존재하지 않는다. 단순히 테이블을 합치는 것을 넘어, 대여 기간을 계산하고 → 그 기간에 맞는 할인 구간을 정의하고 → 구간에 맞는 할인율을 매칭하는 3단계의 논리적 흐름이 요구되는 문제이다.

2. Solution

복잡한 계산 과정을 단계별로 분리하기 위해 WITH 절을 사용하여 가독성을 높이고, 데이터 유실을 막기 위해 LEFT JOIN 전략을 사용한다.

• DATEDIFF(...) + 1: 당일 대여/반납을 1일로 포함하는 비즈니스 관습을 정확히 반영한다.
• LEFT JOIN & IFNULL: 7일 미만 대여 건은 DURATION_TYPE이 NULL이 되어 할인 정책과 매칭되지 않지만, LEFT JOIN을 통해 행을 보존하고 IFNULL로 할인율 0%를 적용하여 계산의 완결성을 높였다.

3. Takeaway (비즈니스 로직의 코드화와 예외 처리)

복잡한 렌탈 비즈니스 지표를 산출하며 분석가가 견지해야 할 객관적 설계 원칙을 정리한다.

• 단계별 가공의 힘 (Modularization): 복잡한 쿼리일수록 WITH 절을 통해 로직을 파편화하는 것이 중요하다. 이는 가독성뿐만 아니라 추후 유지보수와 디버깅 시에도 압도적인 효율을 제공한다.
• 데이터 유실 방지 (Null Handling): 할인 정책이 없는 구간을 '오류'가 아닌 '할인율 0'이라는 '상태'로 해석하는 능력이 중요하다. INNER JOIN의 유혹을 뿌리치고 LEFT JOIN을 선택하는 순간 데이터 정합성이 확보된다.
• 도메인 지식의 쿼리 반영: '트럭'이라는 카테고리 필터링과 날짜 보정(+1)은 분석가가 도메인(현업)의 요구사항을 얼마나 정확하게 쿼리에 녹여낼 수 있는지를 보여주는 객관적 척도이다.

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

 

다음은 어느 자동차 대여 회사에서 대여 중인 자동차들의 정보를 담은 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 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: 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의 경우 예시처럼 정수부분만 출력되어야 합니다.

 

---

1. 정답 쿼리

WITH HISTORY AS (
    SELECT HISTORY_ID, 
           CAR_ID, 
           DATEDIFF(END_DATE, START_DATE) +1 AS DURATION,
           CASE                                  #DURATION_TYPE으로 조인 조건 걸기 위해 컬럼 생성
                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_TYPE
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
)


SELECT H.HISTORY_ID, 
       ROUND(DAILY_FEE * DURATION *(1-IFNULL(P.DISCOUNT_RATE/100,0))) AS FEE 
FROM HISTORY H
LEFT JOIN CAR_RENTAL_COMPANY_CAR C ON H.CAR_ID = C.CAR_ID                            # DAILY FEE 가져옴
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P ON C.CAR_TYPE = P.CAR_TYPE              # 조인 조건1
				AND H.DURATION_TYPE = P.DURATION_TYPE   		#조인 조건2
WHERE C.CAR_TYPE = '트럭'
ORDER BY FEE DESC, H.HISTORY_ID DESC;

 

 

 [SQL/오답노트] 자동차 대여 요금 구하기 (트럭 편)

1. 문제 해결의 핵심 포인트

• 트럭 한정: 전체 자동차가 아닌 CAR_TYPE = '트럭' 데이터만 필터링.
• 대여 기간 계산: DATEDIFF 함수를 사용할 때, 당일 대여/반납도 1일로 치기 위해 반드시 +1을 해줘야 함.
• 할인 정책 매칭: 대여 기간에 따라 '7일 미만(할인 없음)', '7일 이상', '30일 이상', '90일 이상'을 구분하여 할인율(DISCOUNT_RATE)을 적용.

2. 쿼리 설계 전략: WITH 절과 LEFT JOIN

이 문제의 가장 우아한 풀이법은 기간을 먼저 정의하고 나중에 할인율을 붙이는 것입니다.

1. [WITH] 기간 타입 정의: CASE WHEN을 사용해 각 대여 기록이 어떤 DURATION_TYPE에 해당되는지 미리 계산합니다.
2. [JOIN] 할인 정책 결합: LEFT JOIN을 사용하는 것이 핵심입니다. 7일 미만 대여건은 DURATION_TYPE이 NULL이 되어 할인 정책 테이블과 매칭되지 않기 때문입니다.
3. [IFNULL] 예외 처리: 할인 정책이 없는 경우(NULL) 할인율을 0으로 처리하여 계산 오류를 방지합니다.
4.  

쿼리테스트 출제자의 의도

이 문제가 실무자 채용 과정에서 나왔다면, 다음과 같은 역량을 확인하려는 것입니다.

① 복잡한 비즈니스 규칙의 코드화

실무에서는 "7일 이상은 몇 %, 30일 이상은 몇 %" 같은 복잡한 조건이 수시로 변합니다. 이를 CASE WHEN과 조인 조건을 활용해 데이터 구조에 맞게 논리적으로 구현할 수 있는지 봅니다.

② 데이터 무결성과 Null 처리 능력

INNER JOIN을 쓰면 할인 정책이 없는(7일 미만) 데이터가 통째로 사라지는 대참사가 발생합니다.

• 의도: "데이터가 유실되지 않도록 LEFT JOIN을 적재적소에 사용하는가?"
• 의도: "NULL 값이 산술 연산에 들어갔을 때 결과가 NULL이 되어버리는 특성을 알고 IFNULL이나 COALESCE로 방어하는가?"

③ 날짜 데이터 핸들링

분석 업무의 절반은 날짜 계산입니다. DATEDIFF의 특성과 +1 보정처럼 실제 비즈니스 현장(렌터카, 숙박 등)의 날짜 계산 방식을 정확히 이해하고 있는지 확인합니다.

④ 성능과 가독성의 균형 (WITH 절 사용)

너무 복잡한 서브쿼리는 유지보수가 어렵습니다. WITH 절을 통해 단계별로 데이터를 가공해 나가는 깔끔한 코딩 스타일을 가진 인재인지 평가합니다.