KEEP GOING
[MySQL] LeetCode : Market Analysis 본문
반응형
https://leetcode.com/problems/market-analysis-i/
[LEFT JOIN 사용]
SELECT Users.user_id as 'buyer_id'
,Users.join_date
,IFNULL(COUNT(order_date), 0) AS 'orders_in_2019'
FROM Users LEFT JOIN Orders
ON Orders.buyer_id = Users.user_id
AND YEAR(Orders.order_date) = '2019'
GROUP BY Users.user_id
[CTE 사용]
WITH O AS(
SELECT buyer_id, COUNT(*) AS cnt
FROM Orders
WHERE YEAR(order_date) = '2019'
GROUP BY buyer_id
)
SELECT U.user_id AS buyer_id
,U.join_date
,IFNULL(O.cnt, 0) AS orders_in_2019
FROM Users U LEFT JOIN O ON U.user_id = O.buyer_id
[틀린 풀이]
GROUP BY 절에서 buyer_id, order_date와 같이 묶을 경우 아래와 같은 Users 테이블에서
order_id가 5인 경우와 8인 경우 같은 행으로 취급 -> 쿼리 조회시 id가 3인 사용자의 구매정보가 3이 아닌 2로 카운팅됨
WITH O AS(
SELECT buyer_id, COUNT(order_date) AS cnt
FROM Orders
GROUP BY buyer_id, order_date
HAVING YEAR(order_date) = '2019'
)
SELECT U.user_id AS buyer_id
,U.join_date
,COUNT(O.cnt) AS orders_in_2019
FROM Users U LEFT JOIN O ON U.user_id = O.buyer_id
GROUP BY U.user_id
반례)
Users table:
["user_id","join_date","favorite_brand"]
[1,"2019-01-01","Lenovo"],
[2,"2019-02-09","Samsung"],
[3,"2019-01-19","LG"],
[4,"2019-05-21","HP"]
Users table:
["order_id","order_date","item_id","buyer_id","seller_id"]
[[1,"2019-08-01",4,1,2],
[2,"2019-08-02",2,1,3],
[3,"2019-08-03",3,2,3],
[4,"2019-08-04",1,4,2],
[5,"2019-08-04",1,3,4],
[6,"2019-08-05",4,3,4],
[7,"2019-08-06",2,2,4],
[8,"2019-08-04",2,3,1]],
Users table:
[1,"Samsung"],
[2,"Lenovo"],
[3,"LG"],
[4,"HP"]
반응형
'code review > sql' 카테고리의 다른 글
[MySQL] LeetCode : Trips and Users (NOT IN) (0) | 2022.04.08 |
---|---|
[MySQL] LeetCode : Tree Node (NOT IN) (0) | 2022.04.07 |
[MySQL] 3주차 (for coding test) (0) | 2022.03.07 |
[MySQL] 2주차 (0) | 2022.03.06 |
[MySQL] 1주차 (0) | 2022.02.28 |
Comments