[Programmers] 조건에 맞는 사용자와 총 거래금액 조회하기
정답
-- 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
;