[Coding Test - SQL] 프로그래머스 SQL 고득점 Kit : JOIN

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

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

SELECT A.MEMBER_NAME, B.REVIEW_TEXT, DATE_FORMAT(B.REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE A
JOIN REST_REVIEW B USING(MEMBER_ID)
WHERE A.MEMBER_ID IN
    (SELECT MEMBER_ID 
     FROM (SELECT MEMBER_ID, RANK() OVER(ORDER BY COUNT(*) DESC) AS R
           FROM REST_REVIEW
           GROUP BY MEMBER_ID) RT
     WHERE R = 1)
ORDER BY B.REVIEW_DATE, B.REVIEW_TEXT

RANK, RANK_DENSE

RANK : 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 중복 개수만큼 떨어진 순위로 출력
- RANK() OVER (ORDER BY col1 DESC) 
DENSE_RANK : 중복 값들에 대해서 동일 순위로 표시하고, 중복 순위 다음 값에 대해서는 순차적인 순위 값 출력
- DENSE_RANK() OVER (ORDER BY col1 DESC) 
ROW_NUMBER : 중복 값들에 대해서도 순차적인 순위 표시하도록 출력
- ROW_NUMBER() OVER (ORDER BY col1 DESC) 
NTILE : N등분 함수
- NTILE(4) OVER (ORDER BY col1 DESC) 
PARTITION BY : 특정 속성 별로 구분
- RANK() OVER (PARTITION BY cate ORDER BY col1 DESC) 

---

2. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY
 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '세단' 또는 'SUV' 인 자동차 중 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 30일간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 자동차 ID, 자동차 종류, 대여 금액(컬럼명: FEE) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 자동차 종류를 기준으로 오름차순 정렬, 자동차 종류까지 같은 경우 자동차 ID를 기준으로 내림차순 정렬해주세요.

SELECT * 
FROM (SELECT A.CAR_ID, A.CAR_TYPE, ROUND(30*A.DAILY_FEE*(1-B.DISCOUNT_RATE/100)) AS FEE
	  FROM CAR_RENTAL_COMPANY_CAR A
	  JOIN (SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
      		WHERE DURATION_TYPE = '30일 이상') B 
	  USING(CAR_TYPE)
	  WHERE A.CAR_TYPE IN ('세단','SUV') 
	  AND A.CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      			 	WHERE '2022-11-01' BETWEEN START_DATE AND END_DATE 
             	 		   OR '2022-11-30' BETWEEN START_DATE AND END_DATE)
      ) tb
WHERE FEE >= 500000 AND FEE < 2000000
ORDER BY FEE desc, CAR_TYPE, CAR_ID desc

HAVING절 

💡 FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY
실행순서 고려하여 HAVING절 적극적으로 사용하기
예시) 아래 처럼 HAVING절을 활용하여 위 코드를 고치면 이전보다 단순화 가능 !! 

SELECT A.CAR_ID, A.CAR_TYPE, ROUND(30*A.DAILY_FEE*(1-B.DISCOUNT_RATE/100)) AS FEE
	  FROM CAR_RENTAL_COMPANY_CAR A
	  JOIN (SELECT * FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
      		WHERE DURATION_TYPE = '30일 이상') B 
	  USING(CAR_TYPE)
	  WHERE A.CAR_TYPE IN ('세단','SUV') 
	  AND A.CAR_ID NOT IN (SELECT CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
      			 	WHERE '2022-11-01' BETWEEN START_DATE AND END_DATE 
             	 		   OR '2022-11-30' BETWEEN START_DATE AND END_DATE)
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE desc, CAR_TYPE, CAR_ID desc

---

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

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

WITH tb_join(USER_ID) AS (SELECT USER_ID
                            FROM USER_INFO
                            WHERE JOINED LIKE '2021%'
)

SELECT
        YEAR(A.SALES_DATE) YEAR, 
        MONTH(A.SALES_DATE) MONTH,
        COUNT(DISTINCT A.USER_ID) PUCHASED_USERS,
        ROUND(COUNT(DISTINCT A.USER_ID)/(SELECT COUNT(*) FROM tb_join),1) AS PUCHASED_RATIO
FROM ONLINE_SALE A RIGHT OUTER JOIN tb_join B USING(USER_ID)
GROUP BY YEAR(A.SALES_DATE), MONTH(A.SALES_DATE) HAVING YEAR IS NOT NULL
ORDER BY YEAR, MONTH

WITH절 - 임시 테이블

WITH tb_name AS (SELECT * FROM tb_raw)

COUNT() - NULL 값 계산

COUNT(*) : NULL 값을 포함하여 계산
COUNT(col) : NULL 값을 포함하지 않고 계산