[TIL]_2025.03.06 본캠프 18일차 (2): 코드카타 SQL

오늘부터는 코드카타나 기타 코드를 작성할 때에 타이머를 20분으로 맞춰놓고 작성하기로 했다.
코딩테스트 시에는 제한 시간이 있으니까 평소에도 연습을 해두어, 시간을 점점 줄일 수 있도록 노력해보려고 한다.

---

61번. 서울에 위치한 식당 목록 출력하기 12분 20초 소요
다음은 식당의 정보를 담은 REST_INFO 테이블과 식당의 리뷰 정보를 담은 REST_REVIEW 테이블입니다.
REST_INFO 테이블은 다음과 같으며 REST_ID, REST_NAME, FOOD_TYPE, VIEWS, FAVORITES, PARKING_LOT, ADDRESS, TEL은 식당 ID, 식당 이름, 음식 종류, 조회수, 즐겨찾기수, 주차장 유무, 주소, 전화번호를 의미합니다.
REST_REVIEW 테이블은 다음과 같으며
REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT,REVIEW_DATE는 각각 리뷰 ID, 식당 ID, 회원 ID, 점수, 리뷰 텍스트, 리뷰 작성일을 의미합니다.
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요.
이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고,
평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

• 조회하려는 데이터 : 식당 ID (REST_ID), 식당 이름 (REST_NAME), 음식 종류 (FOOD_TYPE), 즐겨찾기 수 (FAVORITES), 주소 (ADDRESS), 리뷰 평균 점수AVG(REVIEW_SCORE)
• 조건 : 서울에 위치, 리뷰 평균 점수는 소수점 세번째에서 반올림
• 정렬 : 평균점수 DESC, 즐겨찾기수 DESC

SELECT 
    RI.REST_ID,
    RI.REST_NAME, 
    RI.FOOD_TYPE, 
    RI.FAVORITES, 
    RI.ADDRESS, 
    ROUND(AVG(RR.REVIEW_SCORE), 2) AS SCORE
FROM 
    REST_INFO RI 
JOIN 
    REST_REVIEW RR
ON 
    RI.REST_ID = RR.REST_ID
WHERE 
    RI.ADDRESS LIKE '서울%'
GROUP BY 
	RI.REST_ID
ORDER BY 
	ROUND(AVG(RR.REVIEW_SCORE), 2) DESC, RI.FAVORITES DESC;

원래는 ORDER BY에 별칭을 사용했었는데, 전체 다 작성하는 것이 FM이라고 하여 이렇게 작성해보았다. 가독성이 좀 떨어지는 것 같아, 다시 별칭으로 작성하려고 한다.

62번. 자동차 대여 기록에서 장기/단기 대여 구분하기 22분 10초 소요
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요.
결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

• 조회하려는 데이터 : 대여기록
• 조건 : 2022-09, 대여 기간 30일 이상이면 '장기 대여', ELSE '단기 대여'인 컬럼 RENT_TYPE
• 정렬 : 대여 기록 ID DESC

SELECT 
    HISTORY_ID, 
    CAR_ID,
    DATE_FORMAT(START_DATE, '%Y-%m-%d') AS START_DATE,
    DATE_FORMAT(END_DATE, '%Y-%m-%d') AS END_DATE,
    CASE WHEN DATEDIFF(END_DATE, START_DATE) >= 29 THEN '장기 대여'
    ELSE '단기 대여'
    END AS RENT_TYPE
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE 
    START_DATE LIKE '2022-09-%'
ORDER BY 
	HISTORY_ID DESC;

어디서 틀렸는지 몰라서 한참 예시랑 비교하다가 발견했다💦
예시에서는 2022-09-01과 2022-09-30인 경우도 장기 대여로 구별한다는 뜻!
즉 대여 시작일부터 1일로 카운트를 한다는 것이다...
하지만 DATEDIFF는 차이를 계산하는 것이기 때문에, 당일은 0으로 계산된다.
따라서 >= 29인 경우로 작성해줘야 한다.
휴...이런 약간 지엽적인 문제가 점점 많이 나오고 있어서, 테이블 자체와 예시도 꼼꼼히 봐야겠다.

63번. 자동차 평균 대여 기간 구하기 5분 40초 소요
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

• 조회하려는 데이터 : 자동차 ID, 평균 대여 기간(AVERAGE_DURATION)
• 조건 : 평균 대여 기간 7일 이상, 평균 대여 기간은 소수점 두번째 자리에서 반올림
• 정렬 : 평균 대여 기간 DESC, 자동차 ID DESC

SELECT 
    CAR_ID,
    ROUND(AVG(DATEDIFF(END_DATE, START_DATE) + 1), 1) AS AVERAGE_DURATION
FROM 
    CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY 
    CAR_ID
HAVING 
    AVERAGE_DURATION >= 7
ORDER BY 
    AVERAGE_DURATION DESC, 
    CAR_ID DESC;

위에서 해당 테이블로 문제를 풀지 않았더라면 헤맸을 수도 있을 것 같다.
DATEDIFF는 당일은 0으로 출력된다는 것을 명심하고, 예시 결과가 있다면 예시 결과에서는 일 수를 어떻게 계산하는지 참고하여 계산해야겠다.

64번. 헤비 유저가 소유한 장소 20분 초과
PLACES 테이블은 공간 임대 서비스에 등록된 공간의 정보를 담은 테이블입니다.
PLACES 테이블의 구조는 다음과 같으며 ID, NAME, HOST_ID는 각각 공간의 아이디, 이름, 공간을 소유한 유저의 아이디를 나타냅니다.
ID는 기본키입니다.
이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다.
헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

• 조회하려는 데이터 : 공간 정보(*)
• 조건 : 공간을 둘 이상 등록한 헤비 유저가 등록한 공간 정보
• 정렬 : ID순 ASC

SELECT 
    P1.ID,
    P1.NAME,
    P1.HOST_ID
FROM 
    PLACES P1 
JOIN (
    SELECT 
    	HOST_ID, 
        COUNT(HOST_ID) AS PL_CNT
    FROM 
    	PLACES
    GROUP BY 
    	HOST_ID) AS P2
ON 
	P1.HOST_ID = P2.HOST_ID
WHERE 
	PL_CNT > 1
ORDER BY 
	P1.ID;

오늘 배운 WITH를 사용할지 WINDOW 함수를 사용할지 이것저것 해보다가 20분이 훌쩍 넘어버렸다.
결국에 서브쿼리로 풀었는데, 내 현재 상황에서는 서브쿼리가 아직은 가장 먼저 떠올릴 수 있고 그나마 다루기 쉬운 것 같다.
WITH와 WINDOW 함수를 동시에 써서 내가 만들고 싶었던 쿼리문은 아래와 같다.
COUNT() OVER()를 사용하여 GROUP BY와 JOIN 없이 한번에 내가 생각한 테이블을 만들고 그 테이블을 WITH로 불러와서 거기서 조건을 걸어서 추출할 수 있다.

WITH PLACES_COUNT AS (
    SELECT 
        ID, 
        NAME, 
        HOST_ID, 
        COUNT(*) OVER (PARTITION BY HOST_ID) AS PL_CNT
    FROM PLACES
)
SELECT 
    ID, 
    NAME, 
    HOST_ID
FROM 
    PLACES_COUNT
WHERE 
    PL_CNT > 1
ORDER BY 
    ID;

65번. 우유와 요거트가 담긴 장바구니 18분 소요
CART_PRODUCTS 테이블은 장바구니에 담긴 상품 정보를 담은 테이블입니다.
CART_PRODUCTS 테이블의 구조는 다음과 같으며,
ID, CART_ID, NAME, PRICE는 각각 테이블의 아이디, 장바구니의 아이디, 상품 종류, 가격을 나타냅니다.
데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다.
우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요.
이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

• 조회하려는 데이터 : 장바구니의 아이디
• 조건 : NAME이 우유와 요거트 동시에 구입
• 정렬 : 장바구니 아이디순 ASC

WITH DAIRY AS
(
	SELECT
		CART_ID,
		GROUP_CONCAT(NAME) AS TOTAL_CART
	FROM
		CART_PRODUCTS
	WHERE
		NAME IN(
			'MILK', 'YOGURT'
		)
	GROUP BY
		CART_ID
)
SELECT
	CART_ID
FROM
	DAIRY
WHERE
	TOTAL_CART LIKE '%MILK%'
	AND TOTAL_CART LIKE '%YOGURT%'
ORDER BY CART_ID;

하하 다른 사람들 답 찾아보고 진짜 이마 탁..
내 이마 남아날 일이 없음.

SELECT
	CART_ID
FROM
	CART_PRODUCTS
WHERE
	NAME IN (
		'Milk', 'Yogurt'
	)
GROUP BY
	CART_ID
HAVING
	COUNT(DISTINCT NAME)= 2

---

여기까지 문제를 풀어보니, 현재의 나는 기초적인 문법은 구사 가능하지만,
응용력이 조금 부족한 것 같다.
각 함수에 대해서 기초 개념을 확실히 복습하면서 작동 원리에 대해서 완벽히 이해하고 명심할 수 있도록 해야겠다...!!

타인과 비교하지 말고, 어제의 나와 비교해서 더 나은 사람이 되도록하는 마음가짐을 갖자!