[Programmers] 그룹별 조건에 맞는 식당 목록 출력하기
정답
-- 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; ```