[leetcode] 1158. Market Analysis I / CASE WHEN, IFNULL

1158. Market Analysis I

 

https://leetcode.com/problems/market-analysis-i/

Market Analysis I - LeetCode

 

Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in 2019.

Return the result table in any order.

 

# 1
SELECT U.user_id AS buyer_id
    , join_date
    , COUNT(CASE WHEN YEAR(order_date) = 2019 
                THEN 1 
                ELSE NULL END) AS orders_in_2019
FROM Users U LEFT JOIN Orders O ON U.user_id = O.buyer_id
GROUP BY user_id, join_date

# 2
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