240812(+68) | SQL 코드카타 | 59~68

Q.59 대여 여부 확인

SELECT CAR_ID, 
    MAX(CASE 
    WHEN '2022-10-16' BETWEEN START_DATE AND END_DATE
    THEN '대여중' 
    ELSE '대여 가능' 
    END) AS AVAILABILITY
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC

Q.60 상품 구매 회원수

USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성

SELECT     
    YEAR(o.sales_date) AS year,
    MONTH(o.sales_date) AS month,
    u.gender,
    COUNT(DISTINCT o.user_id) AS users
FROM ONLINE_SALE o
JOIN USER_INFO u
ON o.user_id= u.user_id
WHERE u.gender is not null # count(*) 122->120
GROUP BY year, month, u.gender
ORDER BY year, month, u.gender;

• 날짜를 포맷팅하는 내장 함수를 사용.
• u.gender가 NOT NULL 이여야 하므로, (INNER) JOIN문 사용.

Q.61 서울에 위치한 식당 목록

SELECT i.REST_ID
    ,REST_NAME
    ,FOOD_TYPE
    ,FAVORITES
    ,ADDRESS
    ,ROUND(AVG(REVIEW_SCORE),2) as 'SCORE'
FROM REST_INFO as i
INNER JOIN REST_REVIEW as r
ON i.rest_id = r.rest_id
WHERE address like '서울%'
AND REVIEW_SCORE IS NOT NULL
GROUP BY r.rest_id
ORDER BY SCORE DESC, FAVORITES DESC

• 주소 컬럼에 서울특별시, 서울시가 있기 때문에 '서울%'로 문자열 필터링
• 점수가 비어있을 데이터도 존재하기에, IS NOT NULL 조건문으로 필터링

Q.62 자동차 대여 기록 구분 ✅

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)+1 >= 30 THEN '장기 대여'
    ELSE '단기 대여'
    END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE BETWEEN '2022-09-01' AND '2022-09-30' 
ORDER BY 1 DESC

• 날짜 빼기 연산에서는 종료일을 포함하지 않기 때문에, 실제 대여 기간을 정확히 계산하려면 +1을 추가해야 한다.

Q.63 자동차 평균 대여 기간

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 2 DESC, 1 DESC

Q.64 헤비 유저가 소유한 장소

1. WHERE 서브 쿼리

SELECT ID, NAME, HOST_ID
FROM PLACES
WHERE HOST_ID 
    IN (SELECT HOST_ID FROM PLACES
        GROUP BY HOST_ID
        HAVING COUNT(*) >= 2)
ORDER BY ID;

2. WITH CTE

WITH HeavyUsers AS (
    SELECT HOST_ID
    FROM PLACES
    GROUP BY HOST_ID
    HAVING COUNT(*) >= 2
)
#MAIN_QUERY
SELECT p.ID, p.NAME, p.HOST_ID
FROM PLACES p
JOIN HeavyUsers h
ON p.HOST_ID = h.HOST_ID
ORDER BY p.ID;

• 특정 조건을 만족시키는 단순한 데이터 집합 필터링에는 WHERE 서브 쿼리가 유용하다.

Q.65 우유와 요거트 ✅

우유와 요거트가 모두 들어가 있는 장바구니를 찾으세요.

STEP_1 Milk, Yogurt 필터링

SELECT CART_ID
    , NAME
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')

• CART_ID 286과 448에는 Milk와 Yogurt가 모두 들어가 있다.
• CART_ID 789와 830에는 Yogurt만 들어가 있다.

---

STEP_2 장바구니 별 상품 갯수

SELECT CART_ID
    , NAME
    ,COUNT(NAME) as 'COUNT'
FROM CART_PRODUCTS
WHERE NAME IN ('Milk', 'Yogurt')
GROUP BY CART_ID

• 장바구니 속에 든 물건의 수

---

STEP_3 Milk와 Yogurt가 함께 들어있는 장바구니

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

• NAME 중복 제거를 통해, Milk&Yogurt 집계 값이 2인 장바구니만 필터링

Q.66 인기게시물 첨부파일

조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성
(단, 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력)

SELECT CONCAT('/home/grep/src/', f.BOARD_ID, '/'
			  , f.FILE_ID ,f.FILE_NAME ,f.FILE_EXT) AS FILE_PATH
FROM USED_GOODS_BOARD as b
JOIN USED_GOODS_FILE as f
ON b.board_id = f.board_id #USING(board_id)
WHERE 
    VIEWS = (
        SELECT MAX(VIEWS)
        FROM USED_GOODS_BOARD
    )
ORDER BY FILE_ID DESC

Q.67 상위 3개 맛 ✅

7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 "맛"을 조회하는 SQL 문

• 어떤 키를 기준으로 JOIN 하냐에 따라서 결과값이 다르게 나온다. (참고)

1. USING(SHIPMENT_ID) JOIN
   

• 두 테이블의 출하번호가 같은 경우의 데이터만 가져온다. (다른 경우 누락)

---

2. USING(FLAVOR) JOIN
   

• 출하번호가 달라도 맛이 같으면 데이터를 누락없이 가져 온다.

STEP_1 총 주문량 확인

SELECT f.SHIPMENT_ID, f.FLAVOR, sum(f.total_order+j.total_order) AS TOTAL
FROM FIRST_HALF as f
JOIN JULY as j
WHERE f.flavor = j.flavor
GROUP BY f.FLAVOR
ORDER BY TOTAL DESC
LIMIT 3

---

STEP_2 문제 요구사항에 따라 쿼리 수정

SELECT f.FLAVOR
FROM FIRST_HALF as f
JOIN JULY as j
WHERE f.flavor = j.flavor
GROUP BY FLAVOR
HAVING sum(f.total_order+j.total_order)
ORDER BY sum(f.total_order+j.total_order) DESC
LIMIT 3

Q.68 매출액 집계 ✅

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

---

STEP_1 작가의 카테고리별 도서 총 매출액

SELECT author_id, category, price
    , SUM(SALES*price) as total_price
FROM BOOK_SALES
JOIN BOOK
USING (BOOK_ID)
WHERE SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31' #날짜 필터링
GROUP BY CATEGORY, AUTHOR_ID
ORDER BY AUTHOR_ID asc, CATEGORY desc

STEP_2 문제 요구 사항에 따라 쿼리 수정

SELECT  a.author_id, a.author_name, category
    , SUM(bs.SALES*b.price) as TOTAL_SALES
FROM BOOK_SALES bs, BOOK b, AUTHOR a
WHERE bs.book_id=b.book_id and b.author_id=a.author_id and
      SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY CATEGORY, AUTHOR_ID
ORDER BY AUTHOR_ID asc, CATEGORY desc