I was trying to solve this Market Analysis | Problem on LeetCode: https://leetcode.com/problems/market-analysis-i/
I wrote this query,
select
u.user_id 'buyer_id',
u.join_date 'join_date',
sum(case when o.order_id is not null then 1 else 0 end) as 'orders_in_2019'
from Users u left join Orders o
on u.user_id = o.buyer_id
where year(o.order_date) = 2019
group by u.user_id
But, it didn't accept my query and gave me unexpected results, which were as follows,
Input Tables
{"headers":{"Users":["user_id","join_date","favorite_brand"],"Orders":["order_id","order_date","item_id","buyer_id","seller_id"],"Items":["item_id","item_brand"]},"rows":{"Users":[[1,"2018-01-01","Lenovo"],[2,"2018-02-09","Samsung"],[3,"2018-01-19","LG"],[4,"2018-05-21","HP"]],"Orders":[[1,"2019-08-01",4,1,2],[2,"2018-08-02",2,1,3],[3,"2019-08-03",3,2,3],[4,"2018-08-04",1,4,2],[5,"2018-08-04",1,3,4],[6,"2019-08-05",2,2,4]],"Items":[[1,"Samsung"],[2,"Lenovo"],[3,"LG"],[4,"HP"]]}}
My Output
{"headers": ["buyer_id", "join_date", "orders_in_2019"], "values": [[1, "2018-01-01", 1], [2, "2018-02-09", 2]]}
Expected Output
{"headers": ["buyer_id", "join_date", "orders_in_2019"], "values": [[1, "2018-01-01", 1], [2, "2018-02-09", 2], [3, "2018-01-19", 0], [4, "2018-05-21", 0]]}
Apparently, the issue with my query was just a simple where clause. If I change the following
where year(o.order_date) = 2019
to
and year(o.order_date) = 2019
The query works as expected. I don't understand how is it so. Can anyone explain please? From my understanding, where clause is also doing the same thing.