[LeetCode/SQL] 1158. Market Analysis I

[LeetCode/SQL]

---

📌 1158. Market Analysis I

문제

풀이

각 사용자에 대해 가입일과 2019년에 구매자로서 
생성한 주문 수를 출력하라 

방법 I

SELECT u.user_id AS buyer_id
     , join_date
     , IFNULL(COUNT(order_date), 0) AS orders_in_2019 
FROM Users as u
LEFT JOIN
Orders as o
ON u.user_id = o.buyer_id
AND YEAR(order_date) = '2019'
GROUP BY u.user_id;

방법 II

WITH cte AS (
    SELECT buyer_id
          ,COUNT(order_id) AS orders_in_2019
    FROM Orders
    WHERE YEAR(order_date)=2019
    GROUP BY buyer_id

)

SELECT u.user_id AS buyer_id
       ,u.join_date AS join_date
       ,IFNULL(cte.orders_in_2019,0) AS orders_in_2019
FROM Users u
LEFT JOIN cte ON u.user_id=cte.buyer_id