[sql] DAY8 (프로그래머스 SQL 고득점 Kit)

lv4. SELECT : 서울에 위치한 식당 목록 출력하기
REST_INFO와 REST_REVIEW 테이블에서 서울에 위치한 식당들의 식당 ID, 식당 이름, 음식 종류, 즐겨찾기수, 주소, 리뷰 평균 점수를 조회하는 SQL문을 작성해주세요. 이때 리뷰 평균점수는 소수점 세 번째 자리에서 반올림 해주시고 결과는 평균점수를 기준으로 내림차순 정렬해주시고, 평균점수가 같다면 즐겨찾기수를 기준으로 내림차순 정렬해주세요.

#내 코드
SELECT A.REST_ID, A.REST_NAME, A.FOOD_TYPE, A.FAVORITES, A.ADDRESS, 
(ROUND (AVG (B.REVIEW_SCORE),2)) AS SCORE
FROM REST_INFO AS A 
INNER JOIN  REST_REVIEW AS B 
ON A.REST_ID = B.REST_ID
WHERE ADDRESS LIKE "서울%"
ORDER BY SCORE DESC, FAVORITES DESC

#JOIN 안에 SELECT~FROM~WHERE 쓸 수 있는 걸 잊지 말자.
SELECT A.REST_ID, B.REST_NAME, B.FOOD_TYPE, B.FAVORITES, B.ADDRESS,
ROUND(AVG(A.REVIEW_SCORE),2) AS SCORE 
FROM REST_REVIEW AS A
JOIN (
    SELECT REST_ID, REST_NAME, FOOD_TYPE, FAVORITES, ADDRESS
    FROM REST_INFO
    WHERE ADDRESS LIKE '서울%'
) AS B
ON A.REST_ID = B.REST_ID
GROUP BY A.REST_ID
ORDER BY SCORE DESC, FAVORITES DESC

---

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

#이런 식으로 쓰는 WHERE 절이 넘 어렵다... 익숙치 않아...

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

---

lv4. GROUP BY : 년, 월, 성별 별 상품 구매 회원 수 구하기
USER_INFO 테이블과 ONLINE_SALE 테이블에서 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성해주세요. 결과는 년, 월, 성별을 기준으로 오름차순 정렬해주세요. 이때, 성별 정보가 없는 경우 결과에서 제외해주세요.

SELECT YEAR(S.SALES_DATE) AS YEAR,
MONTH(S.SALES_DATE) AS MONTH,
I.GENDER,
COUNT(DISTINCT I.USER_ID) AS USERS
/*원래 코드에서는 이 부분을 COUNT(*)로 출력했음.
COUNT(DISTINCT I.USER_ID)처럼 DISTINCT 사용해서 중복 값 제거.*/
FROM USER_INFO  AS I
JOIN ONLINE_SALE AS S
ON I.USER_ID = S.USER_ID
WHERE I.GENDER IS NOT NULL
GROUP BY MONTH, I.GENDER
ORDER BY YEAR, MONTH, I.GENDER

---

lv4. SELECT : 오프라인/온라인 판매 데이터 통합하기
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.

/*없는 값을 NULL로 채우고 싶을 땐 NULL AS
데이터 통합 UNION*/

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, USER_ID, SALES_AMOUNT
FROM ONLINE_SALE
WHERE LEFT(SALES_DATE,7) = '2022-03'

UNION

SELECT DATE_FORMAT(SALES_DATE, "%Y-%m-%d") AS SALES_DATE,
PRODUCT_ID, NULL AS USER_ID, SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE LEFT(SALES_DATE,7) = '2022-03'

ORDER BY SALES_DATE, PRODUCT_ID, USER_ID

---

lv4. String, Date : 취소되지 않은 진료 예약 조회하기
PATIENT, DOCTOR 그리고 APPOINMENT 테이블에서 2022년 4월 13일 취소되지 않은 흉부외과(CS) 진료 예약 내역을 조회하는 SQL문을 작성해주세요. 진료예약번호, 환자이름, 환자번호, 진료과코드, 의사이름, 진료예약일시 항목이 출력되도록 작성해주세요. 결과는 진료예약일시를 기준으로 오름차순 정렬해주세요.

#3개 이상의 테이블 조인
#https://kimsyoung.tistory.com/entry/3%EA%B0%9C-%EC%9D%B4%EC%83%81%EC%9D%98-%ED%85%8C%EC%9D%B4%EB%B8%94-LEFT-JOIN-%ED%95%98%EA%B8%B0

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT AS A
LEFT JOIN PATIENT AS P
ON A.PT_NO = P.PT_NO
LEFT JOIN DOCTOR AS D
ON A.MDDR_ID = D.DR_ID
WHERE DATE_FORMAT(A.APNT_YMD,'%Y-%m-%d') = '2022-04-13'
AND A.APNT_CNCL_YN = 'N'
AND A.MCDP_CD = 'CS'
ORDER BY APNT_YMD

---

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

#DISTINCT 어디에 쓸지... 생각...

SELECT YEAR(S.SALES_DATE) AS YEAR,
MONTH(S.SALES_DATE) AS MONTH, 
COUNT(DISTINCT S.USER_ID) AS PUCHASED_USERS,
ROUND(
    COUNT(DISTINCT S.USER_ID)
     / 
     (SELECT DISTINCT COUNT(USER_ID)
     FROM USER_INFO
     WHERE YEAR(JOINED) LIKE '2021')
    ,1)
     AS PUCHASED_RATIO
FROM ONLINE_SALE AS S
LEFT JOIN USER_INFO AS I
ON S.USER_ID = I.USER_ID
WHERE YEAR(I.JOINED) = '2021'
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH