[프로그래머스 SQL고득점 키트 JOIN]그룹별 조건에 맞는 식당 목록 출력하기

#리뷰를 보여주는것
#멤버 아이디로 조인
#리뷰 가장 많이 쓴사람 찾기
#리뷰작성일 기준 오름차순, 같으면 리뷰 텍스트 기준 오름차순

SELECT B.MEMBER_NAME, A.REVIEW_TEXT, date_format(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW A
JOIN (
    SELECT R.MEMBER_ID, M.MEMBER_NAME, RANK() OVER(ORDER BY CNT DESC) AS RANKING
    FROM (
        SELECT *, COUNT(MEMBER_ID) AS CNT
        FROM REST_REVIEW
        GROUP BY MEMBER_ID) AS R
    JOIN MEMBER_PROFILE M ON R.MEMBER_ID = M.MEMBER_ID) B
ON A.MEMBER_ID = B.MEMBER_ID
WHERE B.RANKING = 1
ORDER BY A.REVIEW_DATE, A.REVIEW_TEXT;