17일차

Suhyeon Lee·2024년 10월 24일

CodeKata

SQL

69. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

작성 쿼리

WITH month_id_record AS (
  SELECT
    car_id
    , MONTH(start_date) AS "MONTH"
    , COUNT(*) AS "RECORDS"
  FROM
    car_rental_company_rental_history
  WHERE
    DATE(start_date) BETWEEN '2022-08-01' AND '2022-10-31'
  GROUP BY
    car_id
    , MONTH(start_date)
  HAVING
    COUNT(*) > 0
),
five_or_above AS (
  SELECT
    car_id
  FROM
    month_id_record
  GROUP BY
    car_id
  HAVING
    SUM(records) >= 5
)
SELECT
  month
  , car_id
  , records
FROM
  month_id_record
WHERE
  car_id IN (TABLE five_or_above)
ORDER BY
  month ASC
  , car_id DESC
;

참고할 만한 다른 풀이

WITH GET_CAR_ID AS(
    SELECT CAR_ID, COUNT(HISTORY_ID) RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE (MONTH(START_DATE) BETWEEN 8 AND 10) AND YEAR(START_DATE) = 2022
    GROUP BY 1
    HAVING RECORDS >= 5
)

SELECT MONTH(START_DATE) MONTH, G.CAR_ID, COUNT(C.HISTORY_ID) RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY C
JOIN GET_CAR_ID G ON C.CAR_ID = G.CAR_ID
WHERE (MONTH(START_DATE) BETWEEN 8 AND 10) AND YEAR(START_DATE) = 2022
GROUP BY 1,2
ORDER BY 1,2 DESC

→ join 하는 부분에 날짜 조건 없이 car_id 로만 조건을 주게 되면 모든 날짜에 대해서 join 되기 때문에 car_id 가 2번이면서 2023년도 데이터라도 join 결과값에 같이 나오게 되는 거니 주의!
→ 문제 설명을 보시면 2022년 8월부터 2022년 10월 까지의 총 대여 횟수를 출력하라고 되어 있습니다.
서브 쿼리에서의 조건절은 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차를 구하기 위한 조건절이고, 밖에서의 조건절은 해당 기간을 위한 조건절입니다.

서브쿼리 사용

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
    WHERE CAR_ID IN (SELECT CAR_ID 
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                     WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                     GROUP BY CAR_ID
                     HAVING COUNT(*) >= 5)
            AND START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
    GROUP BY MONTH, CAR_ID
    ORDER BY MONTH, CAR_ID DESC

SELECT MONTH, CAR_ID, RECORDS
FROM (SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS
      FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
      AND CAR_ID IN (SELECT CAR_ID
                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                 GROUP BY 1
                 HAVING COUNT(*) >= 5)
      GROUP BY 1, 2) AS RENT
ORDER BY 1, 2 DESC;

JOIN 활용

with co as (
select CAR_ID
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where Year(START_DATE) = 2022 and MONTH(START_DATE) in (8,9,10)
group by CAR_ID
having count(*) > 4)

select MONTH(START_DATE) as MONTH, CAR_ID, COUNT(CAR_ID) as RECORDS
from CAR_RENTAL_COMPANY_RENTAL_HISTORY as his inner join co as c using(CAR_ID)
where MONTH(START_DATE) in (8,9,10)
group by MONTH, CAR_ID
order by MONTH asc, CAR_ID DESC;

Python

20. 정수 내림차순으로 배치하기

def solution(n):
    answer = list(str(n))
    answer.sort(reverse=True)
    return int(''.join(answer))

def solution(n):
    return int("".join(sorted(list(str(n)), reverse=True)));

참고할 만한 다른 풀이

def merge(left, right):
    result = []
    while len(left) > 0 or len(right) > 0:
        if len(left) > 0 and len(right) > 0:
            if left[0] >= right[0]:
                result.append(left[0])
                left = left[1:]
            else:
                result.append(right[0])
                right = right[1:]
        elif len(left) > 0:
            result.append(left[0])
            left = left[1:]
        elif len(right) > 0:
            result.append(right[0])
            right = right[1:]
    return result

def mergeSort(arr):
    if len(arr) <= 1:
        return arr

    mid = len(arr) // 2
    left = arr[:mid]
    right = arr[mid:]

    left = mergeSort(left)
    right = mergeSort(right)

    return merge(left, right)

def solution(n):
    arr = list(str(n))
    n = int(''.join(mergeSort(arr)))
    return n