[TIL]_2025.03.07 본캠프 19일차 (1): 코드카타 SQL

66번. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요.
첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요.
기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고,
파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요.
조회수가 가장 높은 게시물은 하나만 존재합니다.

• 조회하려는 데이터 : 첨부파일 경로 (/home/grep/src/게시글 ID/파일ID,파일이름,파일확장자
• 조건 : 조회수가 가장 높은 중고 거래 게시물
• 정렬 : 파일 ID기준 DESC

SELECT 
    CONCAT('/home/grep/src/', UB.BOARD_ID,'/',FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM 
    USED_GOODS_BOARD UB
    INNER JOIN USED_GOODS_FILE UF 
    ON UB.BOARD_ID = UF.BOARD_ID
WHERE
    VIEWS = (
    SELECT 
        MAX(VIEWS)
    FROM
        USED_GOODS_BOARD UB
    )
ORDER BY 
	UF.FILE_ID DESC;

첨부 파일 경로를 작성하기 위해서 두 테이블을 JOIN한다.
상식적으로 생각했을 때 게시글에 있는 첨부파일 정보를 담은 게 USED_GOODS_FILE 테이블이므로 LEFT JOIN을 해도, INNER JOIN을 해도 큰 차이는 없을 것이다.

그 후에 조회수가 가장 높은 중고 거래 게시물을 출력해야 한다.
WHERE절에 서브쿼리를 사용하여 USED_GOODS_BOARD 테이블에서 VIEWS가 가장 높은 행만 출력한다.
그 후에 CONCAT으로 조건에 맞는 첨부파일 경로를 만들어주고 예시에 맞게 컬럼 이름을 FILE_PATH로 만들어준다.

보통 '가장 높은 ~ , 가장 많은 ~' 이런 문구가 들어간 문제일 경우,
WHERE절에 서브쿼리를 이용하여 맞는 행만 뽑아내는 것 같다.

---

67번. 주문량이 많은 아이스크림들 조회하기

FIRST_HALF과 JULY 테이블을 이용하여, 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL 문을 작성해주세요.

• FIRST_HALF 테이블
  
  SHIPMENT_ID는 JULY 테이블의 FK
  FLAVOR는 JULY 테이블의 PK

• JULY 테이블
  
  SHIPMENT_ID는 FIRST_HALF테이블의 PK
  FLAVOR는 FIRST_HALF테이블의 FK

• 조회하려는 데이터 : FLAVOR

• 조건 : 7월 총 주문량 + 상반기 총 주문량이 큰 상위 3개

• 정렬 : X

SELECT 
    FH.FLAVOR
FROM 
    FIRST_HALF FH
LEFT JOIN (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) AS J 
ON 
    J.FLAVOR = FH.FLAVOR
ORDER BY 
    SUM(J.TOTAL_ORDER + FH.TOTAL_ORDER) OVER (PARTITION BY FH.FLAVOR) DESC
LIMIT 3;

JULY 테이블을 조회했을 때 FLAVOR에 STRAWBERRY가 SHIPMENT_ID가 총 2개이다. 따라서 먼저 FLAVOR별로 TOTAL_ORDER를 구할 수 있도록 GROUP BY FLAVOR를 한 테이블을 만들어준다.
그 후에 GROUP BY를 한 JULY 테이블과 FIRST_HALF 테이블을 JOIN하는데,
이때 FLAVOR가 FIRST_HALF 테이블의 PK이기 때문에 이것을 고려하여 LEFT JOIN을 한다.
그 다음에 ORDER BY절에 WINDOW 함수인 SUM을 통해 JOIN한 각 테이블의 TOTAL_ORDER를 FLAVOR 기준으로 합산한 뒤 상위 3개만 출력할 수 있도록 LIMIT 3을 작성한다.
최종적으로 조회하려는 컬럼은 FLAVOR이기 때문에 SELECT FLAVOR를 한다.

이렇게 해도 정답은 나오지만 뭔가 찜찜해서 검색해보니
ORDER BY에 SUM 윈도우 함수가 아니라 그냥 컬럼 + 컬럼으로 작성하는 편이 불필요한 연산을 줄일 수 있다고 한다. JOIN과 GROUP BY를 사용했기 때문에 이미 각 FLAVOR 당 하나의 행만 존재하게 되어 굳이 윈도우 함수를 쓰지 않아도 된다.

그리고 LEFT JON을 하게 되면 JULY에는 주문량이 없는 ORDER가 있을 수 있어, NULL값을 방지하기 위해서
ORDER BY COALESCE(J.TOTAL_ORDER, 0) + FH.TOTAL_ORDER DESC 이런 식으로 작성해야 한다!

68번. 저자 별 카테고리 별 매출액 집계하기

BOOK, AUTHOR, BOOK_SALES 테이블을 이용하여,
2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여,
저자 ID(AUTHOR_ID), 저자명(AUTHOR_NAME), 카테고리(CATEGORY), 매출액(SALES) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 저자 ID를 오름차순으로, 저자 ID가 같다면 카테고리를 내림차순 정렬해주세요.

• 조회하려는 데이터 : 저자ID, 저자명, 카테고리, 매출액(TOTAL_SALES)
• 조건 : 2022년 1월 판매된, 저자 별, 카테고리 별, 매출액
• 정렬 : 저자 ID ASC, CATEGORY DESC

SELECT
	A.AUTHOR_ID,
	A.AUTHOR_NAME,
	B.CATEGORY,
	SUM(B.PRICE * BS.SALES) AS TOTAL_SALES
FROM
	BOOK B
JOIN AUTHOR A ON
	B.AUTHOR_ID = A.AUTHOR_ID
JOIN BOOK_SALES BS ON
	B.BOOK_ID = BS.BOOK_ID
WHERE
	BS.SALES_DATE LIKE '2022-01%'
GROUP BY
	A.AUTHOR_NAME,
	B.CATEGORY
ORDER BY
	A.AUTHOR_ID,
	B.CATEGORY DESC;

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

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요.
결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요.
특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

• 조회하려는 데이터 : 총 대여횟수(RECORDS)
• 조건 : 월별 + 자동차 ID별 , 대여시작일 기준 2022-08~2022-10, 총 대여횟수 5회 이상

SELECT
    MONTH(START_DATE) AS MONTH,
    CAR_ID,
    COUNT(HISTORY_ID) 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 CAR_ID
        HAVING COUNT(HISTORY_ID) >= 5
    )
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC;

'문제에서 특정 기간동안의 총 대여 횟수가 5회 이상인 자동차들에 대해서'라고 했기 때문에, 해당 조건의 테이블을 WHERE로 작성해주고,
이 자동차들에 대해서 월별 자동차ID별 총 대여 횟수 리스트를 출력하도록 조건에 맞춰 작성해준다.
특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외하라고 했으니 최종적으로 월별로 분류한 메인 쿼리에서 HAVING 조건으로 0 초과의 값만 조건으로 걸어주었다.

70번. 그룹별 조건에 맞는 식당 목록 출력하기

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요.
회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고,
결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

• 조회하려는 데이터 : 리뷰를 가장 많이 작성한 회원들의 회원 이름(MEMBER_NAME), 리뷰 텍스트(REVIEW_TEXT), 리뷰 작성일(REVIEW_DATE)
• 조건 : 리뷰를 가장 많이 작성한
• 정렬 : 리뷰 작성일 ASC, 리뷰 텍스트 ASC

SELECT
	MP.MEMBER_NAME, -- 회원 이름
	RR.REVIEW_TEXT, -- 리뷰 텍스트
	DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE -- 리뷰 작성일
FROM(
	SELECT *,
		COUNT(*) OVER(PARTITION BY MEMBER_ID) AS REVIEW_CNT
	FROM
		REST_REVIEW
	) AS RR
JOIN MEMBER_PROFILE MP
ON RR.MEMBER_ID = MP.MEMBER_ID
WHERE
	REVIEW_CNT = ( -- 리뷰 수 = 회원별 리뷰수 중 최대
		SELECT
			MAX(REVIEW_CNT) -- 회원별 리뷰수 중 최대 (3)
		FROM
			(SELECT
				COUNT(*) AS REVIEW_CNT
			FROM
				REST_REVIEW
             GROUP BY MEMBER_ID -- 회원별 리뷰수 카운트
			) AS sub
	)
ORDER BY RR.REVIEW_DATE, RR.REVIEW_TEXT;

리뷰를 가장 많이 작성한 회원을 구하기 위해서, 회원별로 리뷰 갯수를 구해주는데 MEMBER_NAME을 구해야 해서 JOIN을 할 것이기 때문에 COUNT의 WINDOW함수를 사용했음.
그 후에 회원별 리뷰 갯수 중에서 최대값을 찾는 서브쿼리를 WHERE에 작성한 뒤,
조회가 필요한 컬럼과 DATE 형식만 맞춰주었다.