[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"]