题解 | #广告点击率排名#

with tiaojian as (
select 
t.uid,
t.m
from(
select 
uid,
count(rid) as cnt,
rank()over(order by sum(is_click)/count(rid) desc,uid desc) as m
from  user_res_event_log_tb
where date_format(event_date,'%Y-%m')='2022-07'  
and rid like 'ad%'
group by uid
) as t 
where
t.m<=3
),tiaojian1 as (
select 
uid,
round(
sum(is_click)/count(rid),3) as ctr_2208,
rank()over(order by sum(is_click)/count(rid) desc,uid desc) as m
from  user_res_event_log_tb
where date_format(event_date,'%Y-%m')='2022-08'  
and rid like 'ad%'
group by uid
)


select 
t1.uid,
t1.ctr_2208,
t1.m as rk_ctr
from tiaojian1 t1   join tiaojian t on t.uid=t1.uid
order by t1.uid