SQL - 날짜에 관한 group by 문제

해당 문제는 프로그래머스의 Level 3 SQL문제 중 group by에 대한 문제이다.

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

 

이 문제에 대해 내가 생각한 풀이는 다음과 같다.

1. 자동차 ID 별로 max를 이용해 최근 대여 시작일과 대여 종료일을 가져온다.
2. 두 날짜 사이에 '2022-10-16'이라는 특정 날짜가 있다면 현재 대여중이고 아니라면 대여가 가능한 상태이다.

위 풀이대로 쿼리를 작성했고 결과는 오답이다.

SELECT car_id,
    CASE
        WHEN '2022-10-16' BETWEEN start_date AND end_date THEN '대여중'
        ELSE '대여가능'
    END availability
FROM (

    SELECT car_id, MAX(start_date) AS start_date, MAX(end_date) AS end_date
    FROM car_rental_company_rental_history
    GROUP BY car_id
) t1
ORDER BY car_id DESC

 

 

이 쿼리의 문제점은 대여 기록 사이에 특정 날짜(2022-10-16)에 대여가 가능해도 이후에 예약이 있다면 이후 예약 건을 기준으로 계산되기 때문에 오답이다.

 

그래서 쿼리를 다시 작성했다.

이번에는 결과를 계속 확인하면서 쿼리를 추가했다.

 

1. 전체 데이터에서 대여 시작일과 대여 종료일 사이에 특정 날짜(2022-10-16)가 있는지 확인한다.

SELECT *
FROM car_rental_company_rental_history
WHERE '2022-10-16' BETWEEN start_date AND end_date

 

2. 조회된 데이터 중 하나를 기준으로 쿼리를 작성한다. (자동차 ID 23번을 기준으로 잡음)

SELECT car_id, start_date, end_date, 
    CASE
        WHEN '2022-10-16' BETWEEN start_date AND end_date
            THEN '대여중' ELSE '대여가능'
    END availability
FROM car_rental_company_rental_history
WHERE car_id = 23

 

2번 쿼리의 결과를 확인하면 대여 시작일과 대여 종료일 사이에 특정 날짜(2022-10-16)가 있는 경우만 "대여중"이라고 표시된다.

 

3. 2번의 쿼리 그대로 group by를 진행하면 첫 번째 레코드만 출력되기 때문에 집계함수(SUM)를 이용해야 한다.

   자동차 ID를 기준으로 그룹화하고 SUM으로 집계하기 위해 '대여중'을 1로 '대여가능'을 0으로 변경한다.

   ex) 특정 자동차 ID의 availability가 0 +0 + 0 + 1 + 0 + 0이라면 1, 대여 기록 중 해당 날짜에 대여가 불가능 하다는 뜻

SELECT car_id, start_date, end_date, 
    SUM(
        CASE 
            WHEN '2022-10-16' BETWEEN start_date AND end_date THEN 1 ELSE 0
        END 
    ) AS availability
FROM car_rental_company_rental_history
WHERE car_id = 23
GROUP BY car_id

 

4. 특정 자동차 ID로 제한하는 WHERE절을 지우고 SELECT절에서 대여 시작일과 대여 종료일도 지운다.

    t1이라는 이름의 테이블 데이터로 만들어서 SELECT절에서 다시 CASE를 작성한다.

    1이면 '대여중' 0이면 '대여가능'으로 출력되게 한다.

    자동차 ID를 기준으로 내림차순 정렬한다.

with t1 as (
    SELECT car_id,
        SUM(
            CASE 
                WHEN '2022-10-16' BETWEEN start_date AND end_date THEN 1 ELSE 0
            END 
        ) AS availability
    FROM car_rental_company_rental_history
    GROUP BY car_id
)
SELECT car_id,
    CASE
        WHEN availability = 1 THEN '대여중' ELSE '대여 가능' -- 띄어쓰기 주의
        END AS availability
FROM t1
ORDER BY 1 DESC

 

이렇게 작성 후 다시 채점하니 통과 되었다.

중간에 '대여 가능'을 '대여가능'이라고 띄어쓰기를 하지 않아서 실패도 했었다.

앞으로 쿼리를 작성할 때 중간 체크를 하는 것을 잊지 말아야겠다.