题解 | #每个人的累计搜索点击数排名#

select * from (select *,rank()over(order by search_num desc) as search_rank,rank()over(order by click_num desc) as click_rank from
(select uid,(case when search_num>0 then search_num else 0 end) as search_num,(case when click_num>0 then click_num else 0 end) as click_num from
(select uid,count(event_time) as search_num from search_log_tb group by uid) t1
left join
(select uid,count(event_time) as click_num from click_log_tb group by uid) t2 using(uid)) t3 
) t4 where search_rank<=3 or click_rank<=3