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

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

정답 코드

# 딱 한 명만 뽑음
SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE JOIN REST_REVIEW USING(MEMBER_ID)
WHERE MEMBER_ID = (
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY COUNT(REVIEW_ID) DESC
    LIMIT 1
)
ORDER BY REVIEW_DATE, REVIEW_TEXT;

# 가장 많은 인원이 중복인 경우에 모두 뽑음
SELECT P.MEMBER_NAME, R.REVIEW_TEXT, DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM (SELECT *, COUNT(MEMBER_ID) OVER(PARTITION BY MEMBER_ID) AS COUNT_REVIEW
      FROM REST_REVIEW) AS R
JOIN MEMBER_PROFILE AS P
ON R.MEMBER_ID = P.MEMBER_ID
WHERE COUNT_REVIEW = (
    SELECT COUNT(MEMBER_ID) OVER(PARTITION BY MEMBER_ID) AS C
    FROM REST_REVIEW
    ORDER BY C DESC
    LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT;