Problem Description –
Write an SQL query to find for each user, the join date and the number of orders they made as a buyer in
Return the result table in any order.
The query result format is in the following example.
Difficulty Level – Medium
Problem Link – Market Analysis I
SELECT user_id as buyer_id, join_date, IFNULL(orders_in_2019, 0) as orders_in_2019 FROM Users as u LEFT JOIN (SELECT o.buyer_id, COUNT(o.order_id) as orders_in_2019 FROM Orders as o JOIN Users as u ON o.buyer_id = u.user_id WHERE YEAR(order_date) = '2019' GROUP BY 1) x ON u.user_id = x.buyer_id ORDER by 1