챌린지반 숙제 - WITH절 활용하기

문제 풀어보기

---

문제 설명

당신은 도서관의 대여 기록을 관리하는 시스템을 운영하고 있습니다. 대여 기록이 저장된 BOOK_RENTALS 테이블에서 연체된 책들에 대한 정보를 분석하려고 합니다.

BOOK_RENTALS 테이블은 아래와 같은 구조로 되어있습니다.

rental_id	book_id	member_id	days_rented	due_date	return_date
1	1001	201	10	2024-01-10	2024-01-20
2	1002	202	5	2024-01-05	2024-01-04
3	1003	203	8	2024-01-08	2024-01-15
4	1001	204	7	2024-01-07	2024-01-06
5	1002	205	12	2024-01-12	2024-01-15
6	1003	206	15	2024-01-15	2024-01-25

각 컬럼에 대한 설명은 다음과 같습니다.

• days_rented: 대여 기간(일수)
• due_date: 반납 예정일
• return_date: 실제 반납일

연체는 return_date가 due_date보다 늦은 경우로 정의됩니다. 참고로, 날짜 비교는 DATEDIFF 함수를 통해서 사용할 수 있습니다. 즉, DATEDIFF(return_date, due_date) 이걸로 며칠 차이인지 알 수 있어요.

문제

각 도서에 대해 연체된 대여 건수를 계산하고 연체 된 도서들만 대상으로 각 책의 평균 연체 일수를 계산하세요.

예시

book_id	overdue_count	avg_overdue_days
1001	1	10
1002	1	3
1003	2	8

 

---

문제 풀이

각 도서에 대해(GROUP BY) 연체된 대여 건수를 계산(COUNT → DATEDIFF(return_date, due_date)>0), 평균 연체 일수 계산(AVG(DATEDIFF(return_date, due_date)))

일단 연체 일수가 기록된 테이블이 먼저 필요할 것 같다. → WITH 절로 CTE(공통 테이블 표현식)을 작성

WITH Overdue_books AS(
    SELECT 
        book_id
        , DATEDIFF(return_date, due_date) AS overdue_days
    FROM BOOK_RENTALS
    WHERE DATEDIFF(return_date, due_date) > 0
)
SELECT 
    book_id
    , COUNT(*) AS overdue_count
    , AVG(ob.overdue_days) AS avg_overdue_days
FROM Overdue_books AS ob
GROUP BY book_id

 

---

 

실수했던 부분

CTE의 WHERE절을 WHERE overdue_days >0로 표현하려고 했는데 알고 보니 SELECT에서 계산된 값은 WHERE절에서 바로 사용할 수 없다고 한다. 이유를 찾아봤더니 SQL의 실행 순서 때문이라고 한다.

SQL 실행 순서

1. FROM: 테이블을 읽는다.
2. WHERE: 조건에 따라 행을 필터링한다.
3. SELECT: 필터링된 행에서 필요한 컬럼을 계산하거나 선택한다.
4. GROUP BY, HAVING, ORDER BY 등이 순차적으로 실행된다.