JOIN>그룹별 조건에 맞는 식당 목록 출력하기

-- 코드를 입력하세요
select member_name, review_text, review_date
from member_profile A 
join rest_review B 
on A.member_id = B.member_id
    where member_name like (
        select /*member_id,*/ rank() over (order by count(member_id)) as cnt
        from rest_review
        group by member_id
        order by cnt desc
        )

-- select * from rest_review
select member_id, rank() over (order by count(*)) as cnt
from rest_review
group by member_id
order by cnt desc

SELECT B.MEMBER_NAME, A.REVIEW_TEXT, A.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

SELECT A.member_name, B.review_text, B.review_date
FROM member_profile A 
JOIN rest_review B 
ON A.member_id = B.member_id
WHERE A.member_name LIKE (
    SELECT CAST(RANK() OVER (ORDER BY COUNT(member_id) DESC) AS CHAR)
    FROM rest_review
    GROUP BY member_id
    ORDER BY COUNT(member_id)
    LIMIT 1
)
ORDER BY B.review_date;

SELECT A.member_name, B.review_text, B.review_date
FROM member_profile A 
JOIN rest_review B 
ON A.member_id = B.member_id
WHERE A.member_name LIKE (
   SELECT member_id, CAST(RANK() OVER (ORDER BY COUNT(member_id) DESC) AS CHAR) AS TMP
   FROM rest_review
	 GROUP BY member_id
	 HAVING TMP LIKE
			(
			SELECT CAST(RANK() OVER (ORDER BY COUNT(member_id) DESC) AS CHAR)
	    FROM rest_review
	    GROUP BY member_id
	    ORDER BY COUNT(member_id)
	    LIMIT 1
			) --> 13
)
ORDER BY B.review_date;

SELECT A.member_name, B.review_text, B.review_date
FROM member_profile A 
JOIN rest_review B 
ON A.member_id = B.member_id
WHERE A.member_id IN (
   SELECT member_id
   FROM rest_review
	 GROUP BY member_id
	 HAVING RANK() OVER (ORDER BY COUNT(member_id) DESC) = 
			(
			SELECT RANK() OVER (ORDER BY COUNT(member_id) DESC)
	    FROM rest_review
	    GROUP BY member_id
	    ORDER BY COUNT(member_id) DESC
	    LIMIT 1
			)
)
ORDER BY B.review_date;

왜 안되지?