여러 최대값 조회

프로그래머스 SQL Kit JOIN: 그룹별 조건에 맞는 식당 목록 출력하기
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문

 

WITH COUNT_CTE AS (
    SELECT MEMBER_ID, COUNT(MEMBER_ID) AS COUNT_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
), MAX_CTE AS (
SELECT MEMBER_ID, COUNT_ID
    FROM COUNT_CTE C1
    JOIN (SELECT MAX(COUNT_ID) AS MAX_ID FROM COUNT_CTE) C2
    ON C1.COUNT_ID = C2.MAX_ID
)

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE M JOIN REST_REVIEW R
ON M.MEMBER_ID = R.MEMBER_ID
JOIN MAX_CTE C
ON C.MEMBER_ID = R.MEMBER_ID
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT