I am an amazing person.

1 분 소요

문제 링크

정답

-- USED_GOODS_BOARD: BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS
-- USED_GOODS_USER: USER_ID, NICKNAME, CITY, STREET_ADDRESS1, STREET_ADDRESS2, TLNO

-- STATUS=DONE AND 총금액 >= 700k
WITH TOTAL_PRICE_BY_DONE_USER AS (
    SELECT WRITER_ID, SUM(PRICE) AS TOTAL_SALES
    FROM USED_GOODS_BOARD
    WHERE STATUS = 'DONE'
    GROUP BY WRITER_ID
)

SELECT T2.USER_ID, T2.NICKNAME, T1.TOTAL_SALES
FROM TOTAL_PRICE_BY_DONE_USER AS T1
JOIN USED_GOODS_USER AS T2 
    ON T1.WRITER_ID = T2.USER_ID
WHERE T1.TOTAL_SALES >= 700000
ORDER BY T1.TOTAL_SALES
;

✅ Point + 개선점

  • GROUP BY와 SELECT의 일관성 유지
    • WRITER_ID 기준으로 SUM(PRICE)을 올바르게 집계
    • 700k 이상 조건은 cte 선언이 아닌 select 문에서 where로 적용
      • CTE 선언 시, HAVING으로 적용 가능!
  • CTE을 선언하지 않고도 풀 수 있다

개선

  • CTE 선언 시, HAVING 적용 ```sql WITH TOTAL_PRICE_BY_DONE_USER AS ( SELECT WRITER_ID, SUM(PRICE) AS TOTAL_SALES FROM USED_GOODS_BOARD WHERE STATUS = ‘DONE’ GROUP BY WRITER_ID HAVING TOTAL_SALES >= 700000 )

SELECT T2.USER_ID, T2.NICKNAME, T1.TOTAL_SALES FROM TOTAL_PRICE_BY_DONE_USER AS T1 JOIN USED_GOODS_USER AS T2 ON T1.WRITER_ID = T2.USER_ID ORDER BY T1.TOTAL_SALES ;


- CTE 없이 풀이
    - **주의**: cte 없이 푸는게 무조건 좋은 건 아님!
    - 이 문제의 경우, explain 로 비교해보니, cte를 적용한 풀이가 더 효율적
```sql
SELECT U.USER_ID, U.NICKNAME, SUM(B.PRICE) AS TOTAL_SALES
FROM USED_GOODS_BOARD AS B
JOIN USED_GOODS_USER AS U
    ON B.WRITER_ID = U.USER_ID
WHERE B.STATUS = 'DONE'
GROUP BY U.USER_ID
HAVING TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES
;

참고