[프로그래머스] SQL Level 3 모음(~ing)

1. 즐겨찾기가 가장 많은 식당 정보 출력하기

문제

`REST_INFO` 테이블에서 음식종류별로 즐겨찾기수가 가장 많은 식당의 음식 종류, ID, 식당 이름, 즐겨찾기수를 조회하는 SQL문을 작성해주세요. 이때 결과는 음식 종류를 기준으로 내림차순 정렬해주세요.

SELECT FOOD_TYPE, REST_ID, REST_NAME,FAVORITES
FROM REST_INFO
WHERE (FOOD_TYPE, FAVORITES) in (SELECT FOOD_TYPE, MAX(FAVORITES)
                                 FROM REST_INFO
                                 GROUP BY FOOD_TYPE)
ORDER BY FOOD_TYPE DESC;

 

어떻게 그룹별로 가장 큰 값을 뽑아 출력할 수 있을지 꽤 고민했다. 정처기 책도 다시 한 번 보고 상관 없지만 정규 표현식도 약간 공부해보고 ㅎ 그러다 다시 머리를 비우고 생각해봤다. 우선 필요한 값을 먼저 구해봤다. 즉 음식 종류로 그룹을 나눠 그룹별로 가장 많은 즐겨찾기 수를 구했다(서브쿼리). 그 후 WHERE 절에서 잘 연결만 시키면 될 것 같아서 `WHERE FOOD_TYPE, FAVORITES in` 쓰니깐 에러나서 괄호를 추가했더니 쿼리가 잘 작동했다!

 

2. 조건에 맞는 사용자와 총 거래금액 조회하기

문제

`USED_GOODS_BOARD`와 `USED_GOODS_USER` 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.

SELECT
U.USER_ID AS USER_ID,
U.NICKNAME AS NICKNAME,
SUM(PRICE) AS TOTAL_SALES

FROM USED_GOODS_BOARD B, USED_GOODS_USER U
WHERE B.WRITER_ID = U.USER_ID
AND B.STATUS = 'DONE'

GROUP BY WRITER_ID
HAVING TOTAL_SALES >= 700000

ORDER BY TOTAL_SALES;

 

1. 총 거래 금액이 70만원 이상인 사람의 id를 구해야함

ID를 기준으로 그룹 생성. 그룹의 조건으로 TOTAL_SALES 가 70만원 이상을 걸음

GROUP BY WRITER_ID
HAVING TOTAL_SALES >= 700000

 

2. 두 테이블을 조인해야 `닉네임`을 알 수 있으므로 join함

조인의 조건은 ID가 같을 때, 그리고  거래가 `DONE`인 상태만 찾아야 하므로, STATUS 조건도 걸어주기

FROM USED_GOODS_BOARD B, USED_GOODS_USER U
WHERE B.WRITER_ID = U.USER_ID
AND B.STATUS = 'DONE'

 

3. TOTAL_SALES 기준으으로 오름차순 정렬

ORDER BY TOTAL_SALES;

 

3. 자동차 대여 기록에서 대여중/ 대여 가능 여부 구분하기

문제

`CAR_RENTAL_COMPANY_RENTAL_HISTORY` 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명:`AVAILABILITY`)을 추가하여 자동차 ID와 `AVAILABILITY` 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT
CAR_ID,
CASE WHEN CAR_ID IN 
          (SELECT CAR_ID
           FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
           WHERE '2022-10-16' BETWEEN DATE_FORMAT(START_DATE, '%Y-%m-%d') 
           AND DATE_FORMAT(END_DATE, '%Y-%m-%d'))
    THEN '대여중'
ELSE '대여 가능' END AVAILABILITY
                
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
ORDER BY CAR_ID DESC;​

자동차 문제는 다 어려운 것 같다.. 

알게된 점은 CASE 절을 활용하는 방법을 배웠다. 원하는 테이블을 뽑아 IN 연산자를 사용하여 조건에 만족하는 CAR_ID이면 '대여중'을, 그렇지 않으면 '대여 가능'을 컬럼으로 추가한다. 

 

고민했던 점은 '대여중' 조건을 만족하는 CAR_ID는 구하겠는데 그 후 아닌 것에 대한 것은 어떻게 연결시키는지 였다. 또한 CAR_ID에 대해 데이터가 한 개인 것이 아니라 여러 개의 데이터가 있어서 조건을 어떻게 걸지도 고민했었다. 결과로는 Case 문에 IN 연산자를 함께 사용하여 조건을 만족하는 CAR_ID를 구해주고 그렇지 않는 것에 대해서는 Else 처리 해주는 것이였다.

 

4. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 (level 4)

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

SELECT 
MONTH(START_DATE) AS MONTH
,CAR_ID
,COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY

WHERE CAR_ID IN (SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10'
                GROUP BY CAR_ID
                HAVING count(*)  >= 5)
AND DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10' 
                
GROUP BY MONTH(START_DATE), CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH, CAR_ID DESC;

맞는 것 같았는데 놓친 부분이 있었다.

바로 전체 WHERE절 문에서 8월부터 10월까지의 데이터만 조회하는 부분이다. `AND DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10' `쓰는 것을 생각하지 못했다.

 

GROUP의 조건으로 8월부터 10월까지 이미 뽑아서 결과로도 8월~10월의 대여기록만 나올 줄 알았다. 하지만 서브 퀴리 속 조건문은 5개 이상의 거래내역을 찾을 때만 사용되었고, 전체 쿼리에는 적용되지 않아 나는 모든 월을 출력하고 있었다. 전체 쿼리에 월별 조건을 추가해줬더니 정답처리 됐다!

 

5.카테고리 별 도서 판매량 집계하기

문제

2022년 1월의 카테고리 별 도서 판매량을 합산하고, 카테고리(`CATEGORY`), 총 판매량(`TOTAL_SALES`) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 카테고리명을 기준으로 오름차순 정렬해주세요.

SELECT
CATEGORY,
SUM(S.SALES) AS TOTAL_SALES

FROM BOOK B, BOOK_SALES S
WHERE B.BOOK_ID = S.BOOK_ID
AND DATE_FORMAT(S.SALES_DATE, '%Y-%m') = '2022-01'   

GROUP BY CATEGORY
ORDER BY CATEGORY;

 

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

문제

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

SELECT
 A.AUTHOR_ID AS AUTHOR_ID
,A.AUTHOR_NAME AS AUTHOR_NAME
,B.CATEGORY AS CATEGORY
,SUM(SALES*PRICE) AS TOTAL_SALES

FROM BOOK B 
join AUTHOR A on B.AUTHOR_ID = A.AUTHOR_ID
join BOOK_SALES S on B.BOOK_ID = S.BOOK_ID

WHERE DATE_FORMAT(S.SALES_DATE, '%Y-%m')  = '2022-01'
GROUP BY AUTHOR_ID, CATEGORY
ORDER BY AUTHOR_ID, CATEGORY DESC;

 

7 .없어진 기록 찾기 [JOIN]

문제

천재지변으로 인해 일부 데이터가 유실되었습니다. 입양을 간 기록은 있는데, 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하는 SQL문을 작성해주세요.

SELECT
O.ANIMAL_ID,
O.NAME

FROM ANIMAL_INS I RIGHT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID

WHERE I.ANIMAL_ID is NULL
ORDER BY I.ANIMAL_ID;