[SQL]프로그래머스 Lv.4 그룹별 조건에 맞는 식당 목록 출력하기

✅ 코드

# 리뷰를 가장 많이 작성한 회원
WITH MOST_REVIEWS AS (
   SELECT MP.MEMBER_ID AS MEMBER_ID
       , MP.MEMBER_NAME AS MEMBER_NAME
       , COUNT(DISTINCT RR.REVIEW_ID) AS REVIEWS
   FROM MEMBER_PROFILE AS MP
      INNER JOIN REST_REVIEW AS RR ON MP.MEMBER_ID = RR.MEMBER_ID
   GROUP BY MP.MEMBER_ID
         , MP.MEMBER_NAME
   ORDER BY REVIEWS DESC
   LIMIT 1
)

SELECT MR.MEMBER_NAME AS NAME
    , RR.REVIEW_TEXT AS REVIEW_TEXT
    , DATE_FORMAT(RR.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW AS RR
   INNER JOIN MOST_REVIEWS AS MR ON RR.MEMBER_ID = MR.MEMBER_ID
ORDER BY REVIEW_DATE
      , REVIEW_TEXT

우선 WITH문으로 리뷰를 가장 많이 작성한 회원의 ID, 이름, 리뷰 수를 구했습니다.

이후 REST_REVIEW와 해당 3가지 정보를 조인했습니다.