9/24 SQL with문

with문은 SQL에서 공통 테이블(CTE, Common Table Expression)을 정의하기 위해 사용된다.
복잡한 쿼리를 재사용 가능하게 해준다.

WITH CTE_name AS (
	-- 하위 쿼리
    SELECT col1, col2 ...
    FROM table
    WHERE cond
)
SELECT col1, col2
FROM CTE_name
WHERE condition

장점

• 재사용성: 중간에 계산한 결과를 여러번 사용할 수 있다.
• 가독성: 복잡한 쿼리 로직을 여러 단계로 나눠서 표현
• 성능 최적화

문제

문제 설명

당신은 도서관의 대여 기록을 관리하는 시스템을 운영하고 있습니다. 대여 기록이 저장된 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) 이걸로 며칠 차이인지 알 수 있어요.

문제

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

정답

WITH overdue_books AS (
  SELECT book_id, COUNT(*) AS overdue_count, AVG(DATEDIFF(return_date, due_date)) AS avg_overdue_days
  FROM book_rentals
  WHERE return_date > due_date
  GROUP BY book_id
)
SELECT book_id, overdue_count, avg_overdue_days
FROM overdue_books;