🛠️Skill/CodingTest
[leetcode] 1158. Market Analysis I / CASE WHEN, IFNULL
Istj_eff
2022. 12. 30. 17:03
1158. Market Analysis I
https://leetcode.com/problems/market-analysis-i/
Market Analysis I - LeetCode
Can you solve this real interview question? Market Analysis I - Table: Users +----------------+---------+ | Column Name | Type | +----------------+---------+ | user_id | int | | join_date | date | | favorite_brand | varchar | +----------------+---------+ u
leetcode.com
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