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

with sh
as(
    select 
    uid
    ,count(1) sh
    from search_log_tb
    group by 1
)
,
cl as(
    select 
    uid
    ,count(1) cl
    from click_log_tb
    group by 1
)
select 
uid
,sh search_num
,ifnull(cl , 0) click_num
,srk search_rank
,crk click_rank
from 
(    select 
    sh.uid
    ,sh
    ,cl
    ,rank()over(order by sh desc) srk
    ,rank()over(order by cl desc) crk 
    from sh left join cl 
    on sh.uid = cl.uid 
) a
where srk <= '3' or crk <= '3'
order by 5 , 1