leetcode 1158 市場分析I

leetcode 1158 市場分析I

select uo.user_id as buyer_id, uo.join_date, if(uo.buyer_id, uo.c, 0) as orders_in_2019 
from (
    select u.user_id, u.join_date, o.buyer_id, count(*) as c from Users u
left join Orders o 
on o.buyer_id  = u.user_id
and order_date between '2019-01-01' and '2019-12-31'
group by u.user_id
) uo

 

select u.user_id as buyer_id, u.join_date, count(order_id) as orders_in_2019 
from Users u
left join Orders o 
on o.buyer_id  = u.user_id
and order_date between '2019-01-01' and '2019-12-31'
group by u.user_id