题解 | 广告点击率排名

with tmp1
as
(   select 
    uid
    ,sum(is_click) / count(1) sc
    from user_res_event_log_tb a
    where left(rid , 2) = 'ad' and  left(event_date , 7) = '2022-07'
    group by 1
    order by 2 desc , uid desc
    limit 3
)
,
tmp2
as
(   select 
    uid
    ,sum(is_click) / count(1) cnt1
    ,row_number()over(order by sum(is_click) / count(1) desc , a.uid desc) cnt3
    from user_res_event_log_tb a
    where left(event_date , 7) = '2022-08' and left (rid , 2) = 'ad'
    group by 1   
)
select 
tmp1.uid
,round(cnt1 , 3) ctr_2208
,cnt3 rk_ctr
from tmp1 inner join tmp2
on tmp1.uid = tmp2.uid