사실 이 문제를 처음 풀 때는 그냥 아무 생각 없이 가장 많이 작성한 회원이 단 1명이라는 가정을 하고 풀었다.
그런데 이번에 조원분들과 스터디를 하기 위해 다시 데이터를 뜯어보니, 1명이 아닌 3명이 3회로 공동 1위를 달리고 있었다.
그래서 보다 본질적인 의미에서, 문제가 의도한 바를 풀어내기 위해 다시 이 문제를 고민했다.

문제

70번.

MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.

(링크)

문제 이해 및 구상

MEMBER_PROFILE와 REST_REVIEW 테이블을 JOIN해야 함
NULL값 요구가 없으므로 INNER JOIN이면 가능할 듯
추가 조건 : 리뷰를 가장 많이 작성한 회원 구하기
-1. REST_REVIEW 테이블에서 member_id별로 cnt와 max_cnt를 구하고
-2. SELF JOIN으로 WHERE cnt = max_cnt인 회원의 member_id를 추출해서
-3. 이 member_id를 가진 회원들의 리뷰 데이터만 추출

기존 풀이

최다 리뷰 작성자가 중복이어도 그 중 한명의 리뷰 데이터만 조회하도록 작성했던 코드
WITH aa에서 cnt 내림차순 정렬을 한 뒤 LIMIT 1을 했기에,
가장 cnt가 높은 회원 중 한 명의 member_id 1개만 나오게 된다

WITH aa AS (
SELECT
    member_id
		, count(1) as 'cnt'
FROM
    rest_review
GROUP BY
    member_id
ORDER BY
		2 desc
LIMIT 1
)
SELECT
    m.member_name
    , r.review_text
    , date_format(r.review_date, '%Y-%m-%d') as 'review_date'
FROM 
    member_profile m
    INNER JOIN rest_review r
        ON m.member_id = r.member_id
WHERE
    m.member_id in (
    SELECT
        member_id
    FROM aa
    )
ORDER BY
    3
    , 2
;

추가 조건 풀이

서브쿼리로 작성하면 너무 코드가 길어지고 가독성이 낮아질 것 같았기에,
최근에 배운 WITH 구문을 적극 활용하기로 했다.

REST_REVIEW 테이블에서 member_id별 cnt, max_cnt 구하기

WITH aa AS (
SELECT
    member_id
    , count(*) as 'cnt'
    , max(count(*)) over() as 'max_cnt'
FROM
    rest_review
GROUP BY
    1
)
SELECT
    *
FROM 
		aa
;

따로 PARTITION BY를 하지 않았기 때문에 전체 데이터에 대한 max()가 계산되었다.

SELF JOIN으로 WHERE cnt = max_cnt인 회원의 member_id 추출

WITH aa AS (
SELECT
    member_id
    , count(*) as 'cnt'
    , max(count(*)) over() as 'max_cnt'
FROM
    rest_review
GROUP BY
    1
),
bb AS (
SELECT
    member_id
FROM 
    aa
WHERE
    cnt = (
        SELECT
            distinct max_cnt
        FROM
            aa
        )
)
SELECT
    *
FROM
    bb
;
    

(아래 이미지는 중간 단계로 SELECT *을 통해 잘 추출 되었는지 확인하기 위함)

추출된 member_id를 가진 회원들의 리뷰 데이터만 조회

# 리뷰를 가장 많이 작성한 회원이 복수일 경우에 이를 모두 출력하는 코드
WITH aa AS (
# rest_review에서 count(*)를 수행하고, 이 중 가장 큰 값(max_cnt)를 함께 출력
SELECT
    member_id
    , count(*) as 'cnt'
    , max(count(*)) over() as 'max_cnt'
FROM
    rest_review
GROUP BY
    1
),
bb AS (
# aa의 max_cnt와 동일한 cnt 값을 가진 member_id만 남김
SELECT
    member_id
FROM 
    aa
WHERE
    cnt = (
        SELECT
            distinct max_cnt
        FROM
            aa
        )
)
# ==-----메인쿼리시작-----==
SELECT
    m.member_name
    , r.review_text
    , date_format(r.review_date, '%Y-%m-%d') as 'review_date'
FROM
    MEMBER_PROFILE m
    INNER JOIN REST_REVIEW r
        ON m.member_id = r.member_id
WHERE
    m.member_id in (
    # member_id가 cnt = max_cnt인 회원들의 member_id만 조회하기 위함
        SELECT
            member_id
        FROM
            bb
        )
ORDER BY
    r.review_date
    , r.review_text
;

새로 알게 된 점

윈도우 함수 중에서는 row_number()나 ranking() 정도만 주로 사용했는데,
처음으로 max()함수를 사용했다
max() 함수의 경우 어차피 최대값을 뽑기에 order by가 불필요하며, 만약 전체 데이터를 조회해 partition by까지 사용하지 않는다면 max(컬럼명) over()로 작성하게 되는, 다소 이상한 형태로 나타난다

그 외에도 SUM이나 COUNT 같은 함수들도 윈도우 함수로 사용할 수 있는데,
이들 함수도 추후 작성할 코드에서 적극 애용해봐도 좋지 않을까 싶다.