I am an amazing person.

최대 1 분 소요

문제 링크

정답

-- MEMBER_PROFILE: MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH
-- REST_REVIEW: REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT, REVIEW_DATE
WITH MOST_REVIEWS_MEMBER_ID AS (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(REVIEW_ID) DESC
    LIMIT 1
)

SELECT 
    M.MEMBER_NAME, 
    R.REVIEW_TEXT, 
    DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS M
JOIN MOST_REVIEWS_MEMBER_ID AS MID
    ON M.MEMBER_ID = MID.MEMBER_ID
JOIN REST_REVIEW AS R
    ON M.MEMBER_ID = R.MEMBER_ID
ORDER BY 3, 2;

✅ Point

  • 최다 리뷰 수의 멤버 아이디를 별도의 cte로 선언
    • 내림차순 정렬 후 limit 1 로 결과를 제한했기 때문에, 동점자에 대해선 처리 못 함
  • 총 3개의 table을 join. 모두 member_id가 같은 경우를 기준으로 join

다른 풀이

  • 동점자를 모두 반영하는 풀이 ```sql – MEMBER_PROFILE: MEMBER_ID, MEMBER_NAME, TLNO, GENDER, DATE_OF_BIRTH – REST_REVIEW: REVIEW_ID, REST_ID, MEMBER_ID, REVIEW_SCORE, REVIEW_TEXT, REVIEW_DATE WITH REVIEWS_CNT AS ( SELECT MEMBER_ID, COUNT(REVIEW_ID) AS REVIEW_CNT FROM REST_REVIEW GROUP BY MEMBER_ID ), MAX_REVIEW_MEMBER_ID AS ( SELECT MEMBER_ID FROM REVIEWS_CNT WHERE REVIEW_CNT = ( SELECT MAX(REVIEW_CNT) FROM REVIEWS_CNT ) )

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, ‘%Y-%m-%d’) AS REVIEW_DATE FROM MEMBER_PROFILE AS M JOIN MAX_REVIEW_MEMBER_ID AS MID ON M.MEMBER_ID = MID.MEMBER_ID JOIN REST_REVIEW AS R ON M.MEMBER_ID = R.MEMBER_ID ORDER BY 3, 2; ```

참고