(Programmers) SQL 풀이 (11)

✏️ Programmers MySQL 정답률 37~54% 전체 풀이

1️⃣ 그룹별 조건에 맞는 식당 목록 출력하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131124)

SELECT MP.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATE
FROM MEMBER_PROFILE AS MP
JOIN (
    SELECT RR.MEMBER_ID, REVIEW_TEXT, REVIEW_DATE
    FROM REST_REVIEW AS RR
    JOIN (
        SELECT MEMBER_ID
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY COUNT(REVIEW_ID) DESC
        LIMIT 1
    ) AS RRR
    ON RR.MEMBER_ID = RRR.MEMBER_ID
) AS R
ON MP.MEMBER_ID = R.MEMBER_ID
ORDER BY REVIEW_DATE, REVIEW_TEXT;

SELECT MP.MEMBER_NAME, RR.REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS MP
JOIN REST_REVIEW AS RR
ON MP.MEMBER_ID = RR.MEMBER_ID
WHERE MP.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;

➡️ 만약 리뷰 개수의 max 값이 같은 사람들이 여러명이고, LIMIT 1 의 작동에서 누구를 가지고 와야하는지에 대한 기준이 없다면, 완벽한 쿼리는 아니라고 생각 

2️⃣ 상품을 구매한 회원 비율 구하기 (https://school.programmers.co.kr/learn/courses/30/lessons/131534)

SELECT YEAR(SALES_DATE), 
MONTH(SALES_DATE), 
COUNT(DISTINCT(OS.USER_ID)) AS PUCHASED_USERS,
ROUND(
    (COUNT(DISTINCT(OS.USER_ID)))
    /
    (SELECT COUNT(USER_ID) FROM USER_INFO WHERE YEAR(JOINED)=2021), 1)    
FROM ONLINE_SALE AS OS
JOIN (
    SELECT USER_ID
    FROM USER_INFO
    WHERE YEAR(JOINED) = 2021
) AS UI
ON OS.USER_ID = UI.USER_ID
GROUP BY YEAR(SALES_DATE), MONTH(SALES_DATE);

# SELECT 부분에서 다른 테이블에서의 count 값을 가지고올 때 주의할 점
# 틀린 방법
SELECT *, SELECT COUNT(*) FROM ONLINE_SALE
FROM USER_INFO;

# 맞는 방법 : 괄호를 쳐줘야함!!
SELECT *, (SELECT COUNT(*) FROM ONLINE_SALE)
FROM USER_INFO;

✏️ Programmers MySQL 최신 문제 풀이

3️⃣ 인기있는 아이스크림 (https://school.programmers.co.kr/learn/courses/30/lessons/133024) / 정답률 90%

SELECT FLAVOR
FROM FIRST_HALF
ORDER BY TOTAL_ORDER DESC, SHIPMENT_ID;

4️⃣ 성분으로 구분한 아이스크림 총 주문량 (https://school.programmers.co.kr/learn/courses/30/lessons/133026) / 정답률 77%

SELECT INGREDIENT_TYPE, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF
JOIN ICECREAM_INFO
USING (FLAVOR)
GROUP BY INGREDIENT_TYPE
ORDER BY TOTAL_ORDER;