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

#要我说出题的人就有毛病，你直接说输出小于等于3的数值就完了，还在排名+1 我真是服了。
#我都不知道那边全了，是搜素的人多还是点击的人多，毕竟需要有可能有一边是空值，服了。

with tiaojian as (
select 
uid,
count(query_kw) as search_num
from search_log_tb
group by uid
),tiaojian1 as (
select 
uid,
count(id) as click_num
from click_log_tb 
group by uid
)

select 
t.uid,
t.search_num,
ifnull(
t.click_num,0) as click_num,
t.search_rank,
t.click_rank
from(
select 
t.uid,
t.search_num,
t1.click_num,
dense_rank()over(order by t.search_num desc) as search_rank,
dense_rank()over(order by t1.click_num desc) as click_rank
from tiaojian t  left join  tiaojian1 t1 
on
t.uid=t1.uid
) as t 
where
t.search_rank<=2
or 
t.click_rank<=2