CodeKata

SQL

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

• 문제
  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를 기준으로 내림차순 정렬해주세요.
  • 자동차 종류: 세단 또는 suv
    • car_rental_company_car 테이블
      car_type 컬럼
  • 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능
    • car_rental_company_rental_history 테이블
      start_date, end_date가 해당 기간에 속하지 않아야 함
  • 대여 금액: 30일 기준 50만원 이상 200만원 미만
    • car_rental_company_discount_plan 테이블
      duration_type 컬럼 == "30일 이상"
      해당하는 discount_rate --- ①
      car_rental_company_company_car 테이블
      daily_fee --- ②
      → ②에 ① 할인율 적용하고 30일 곱하기
  • 출력해야 하는 것
    • 자동차 id
    • 자동차 종류
    • 대여 금액(컬럼명: fee)
  • 정렬 순서
    1. 대여 금액 기준 내림차순
    2. 자동차 종류 기준 오름차순
    3. 자동차 ID 기준 내림차순
• 작성 쿼리

1. 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능한 차 구하기

# 1번 쿼리
SELECT
  car_id
  , start_date
  , end_date
FROM
  car_rental_company_rental_history
WHERE 
  '202211' BETWEEN DATE_FORMAT(start_date, '%Y%m') AND DATE_FORMAT(end_date, '%Y%m')

-- 23대?

# 2번 쿼리
SELECT
  car_id
  , start_date
  , end_date
FROM
  car_rental_company_rental_history
WHERE 
  DATE_FORMAT(start_date, '%Y%m') = '202211'
  OR DATE_FORMAT(end_date, '%Y%m') = '202211'

-- 14대??
/*
20번 차는 2022년 10월 31일에 빌려서
2022년 12월 10일 반납해서 대여 불가능인데
위 쿼리로는 안 잡힘
*/

# 3번 쿼리
SELECT
  car_id
  , start_date
  , end_date
FROM
  car_rental_company_rental_history
WHERE 
  DATE_FORMAT(start_date, '%Y%m') <> '202211'
  AND DATE_FORMAT(end_date, '%Y%m') <> '202211'
-- 146대???
/*
10월 안에 빌리고 반납한 사람들이 뜸
3, 13, 30번 차 등등
*/

2. 차종별 한 달 대여료

SELECT
  car_id
  , car_type
  , daily_fee*30 AS monthly_fee
FROM
  car_rental_company_car
WHERE
  car_id NOT IN(TABLE november_rented)

3. 차종별 한 달 할인률

SELECT
  car_type
  ,(1 - discount_rate*0.01) AS dc
FROM
  car_rental_company_discount_plan
WHERE
  duration_type = '30일 이상'

• 처음 작성한 쿼리

WITH november_rented AS (
  SELECT
    car_id
  FROM
    car_rental_company_rental_history
  WHERE 
    '202211' BETWEEN DATE_FORMAT(start_date, '%Y%m') AND DATE_FORMAT(end_date, '%Y%m')
),
available_car AS (
  SELECT
    car_id
    , car_type
    , daily_fee*30 AS monthly_fee
  FROM
    car_rental_company_car
  WHERE
    car_id NOT IN(TABLE november_rented)
),
discount AS (
  SELECT
    car_type
    ,(1 - discount_rate*0.01) AS dc
  FROM
    car_rental_company_discount_plan
  WHERE
    duration_type = '30일 이상'
)
SELECT
  car_id
  , a.car_type
  , ROUND(monthly_fee*dc, 0) AS fee
FROM
  available_car a
  JOIN discount d
  USING(car_type)
ORDER BY
  ROUND(monthly_fee*dc, 0) DESC
  , a.car_type ASC
  , car_id DESC

→ 대여 금액이 30일 기준 50만원 이상 200만원 미만이라는 조건을 빼먹었음
→ WHERE 500000 <= (monthly_fee - monthly_fee*dc) < 2000000 추가해서 제출했더니 또 틀림
🡆 이렇게 쓰면 작동 안 함!

• 통과된 쿼리

WITH november_rented AS (
  SELECT
    car_id
  FROM
    car_rental_company_rental_history
  WHERE 
    '202211' BETWEEN DATE_FORMAT(start_date, '%Y%m') AND DATE_FORMAT(end_date, '%Y%m')
),
available_car AS (
  SELECT
    car_id
    , car_type
    , daily_fee*30 AS monthly_fee
  FROM
    car_rental_company_car
  WHERE
    car_id NOT IN(TABLE november_rented)
),
discount AS (
  SELECT
    car_type
    ,(1 - discount_rate*0.01) AS dc
  FROM
    car_rental_company_discount_plan
  WHERE
    duration_type = '30일 이상'
)
SELECT
  car_id
  , a.car_type
  , ROUND(monthly_fee*dc, 0) AS fee
FROM
  available_car a
  JOIN discount d
  USING(car_type)
WHERE
  monthly_fee BETWEEN 500000 AND 1999999
ORDER BY
  ROUND(monthly_fee*dc, 0) DESC
  , a.car_type ASC
  , car_id DESC

🡆 WHERE절 조건을 잘 쓰자… 그리고 제출하기 전에 실행 결과 내용이 정확한지 다시 한번 읽어본 뒤에 내자…
🡆 ROUND는 반올림 함수라 엄밀히 따졌을 떄 이렇게 쓰면 틀림! CONVERT(monthly_fee*dc, UNSIGNED)로 형식을 바꾸는 걸 추천

참고할 만한 다른 풀이

• HAVING절 활용

WITH CANT_RENTAL AS (
    SELECT DISTINCT CAR_ID 
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE 
    DATE_FORMAT(START_DATE, '%Y-%m') = '2022-11' OR 
    DATE_FORMAT(END_DATE, '%Y-%m') = '2022-11' OR 
    (START_DATE < '2022-11-01' AND END_DATE >= '2022-12-01')
),
DISCOUNT AS (
    SELECT CAR_TYPE, DISCOUNT_RATE 
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
    WHERE DURATION_TYPE	= '30일 이상'
)
SELECT C.CAR_ID, C.CAR_TYPE, 
CONVERT((C.DAILY_FEE * 30) * (100 - D.DISCOUNT_RATE) / 100 , UNSIGNED) FEE
FROM CAR_RENTAL_COMPANY_CAR C JOIN DISCOUNT D 
ON C.CAR_TYPE = D.CAR_TYPE 
WHERE C.CAR_ID NOT IN (SELECT * FROM CANT_RENTAL)
HAVING FEE BETWEEN 500000 AND 1999999
ORDER BY 3 DESC, 2 ASC , 1 DESC

→ GROUP BY 없이 단독으로 HAVING을 쓸 수 있는지 몰랐다! 좀 더 찾아봐야겠다.

SDL

GROUP BY 없이 HAVING 사용

Python

22. 두 정수 사이의 합

• 작성 코드

def solution(a, b):
    answer = 0
    if a == b:
        answer = a
    elif a > b:
        for i in range(a-b+1):
            answer += b + i
    else:
        for i in range(b-a+1):
            answer += a + i
    return answer

def solution(a, b):
    return sum(range(min(a,b),max(a,b)+1))

참고할 만한 다른 풀이

def solution(a, b):
    if a > b:
        a, b = b, a
    return sum(range(a, b + 1))

→ a가 b보다 크면 서로 자리 바꿈

def adder(a, b):
    return (abs(a-b)+1)*(a+b)//2

→ 절댓값 함수 사용
→ 기본적으로 n(n+1)/2의 기초는 가우스 합입니다.

1부터 100까지 다 더한다고 한다면, 1부터 100까지 쭉 적고 그 아래 100부터 1까지 쭉 적은 다음 위아래를 합하면 100개의 101이 나오고(합: 10100), 두 번 더했으니 반으로 나눕니다.
== n개의 숫자를 n+1만큼 더한 다음 2로 나누는 것: n(n+1)/2
a부터 b까지의 합 역시 a+b를 숫자의 개수('a와 b의 차이'+1)만큼 곱하고 반으로 나누면 됩니다.