[programmers] SQL 고득점 Kit - JOIN

이번에는 다섯번째 파트, join이다

 

# 5월 식품들의 총 매출 조회하기

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT AS P INNER JOIN FOOD_ORDER AS O
     ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE DATE_FORMAT(O.PRODUCE_DATE, '%Y-%m') = '2022-05'
GROUP BY P.PRODUCT_NAME
ORDER BY TOTAL_SALES DESC, PRODUCT_ID

-  inner join으로 합쳐준 뒤에, 각 칼럼 정보 사용해서 '총계'칼럼 만들어주기

 

 

# 없어진 기록 찾기

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

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS AS OUTS
     LEFT JOIN ANIMAL_INS  AS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID

- FROM절에서 left join하고 나면 -> 이후로는 합쳐진 테이블로 진행됨.

- 그래서 이후에 WHERE절에서 outs테이블에는 있는데 ins테이블에는 없는 animal_id를 필터링 걸어서 -> out에 있는 정보를 추출해내면 된다. 

 

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

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

SELECT M.MEMBER_NAME, R.REVIEW_TEXT	, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d')
FROM MEMBER_PROFILE AS M INNER JOIN REST_REVIEW AS R
     ON M.MEMBER_ID = R.MEMBER_ID
WHERE M.MEMBER_ID = (SELECT MEMBER_ID 
                     FROM REST_REVIEW
                     GROUP BY MEMBER_ID
                     ORDER BY COUNT(MEMBER_ID) DESC
                     LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT

- 가장 많이 작성한 회원의 ID 값을 알아내는 구문을 하나 적어 -> where절에 여기에 해당하는 id를 필터링하면 됨.

 

# 상품을 구매한 회원 비율 구하기

USER_INFO 테이블과 ONLINE_SALE 테이블에서 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성해주세요. 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬해주세요.

-- 코드를 입력하세요
SELECT YEAR(S.SALES_DATE) AS YEAR, MONTH(S.SALES_DATE) AS MONTH, COUNT(DISTINCT S.USER_ID), ROUND(COUNT(DISTINCT S.USER_ID)/(SELECT COUNT(USER_ID)
                FROM USER_INFO
                WHERE YEAR(JOINED) = 2021),1)
FROM ONLINE_SALE S
LEFT JOIN USER_INFO U ON S.USER_ID = U.USER_ID 
WHERE YEAR(JOINED) = 2021
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH

- 거래 내역을 기준으로 user_info를 옆으로 넣어준다. 

- 다음으로 2021년에 가입한 사람 중 구매한 s.user_id의 distinct한 count를 구해주고 -> 

- 비율을 구해주기 위해서 분모로 값을 하나 만들어서 나눠주면 된다.