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

with cte as(
select uid,sum(is_click)/count(*) as rk_7
from user_res_event_log_tb
where rid like 'ad%' and left(event_date,7)='2022-07'
group by uid
order by rk_7 desc ,uid desc
limit 3   ),
cte1 as (
select uid,ctr_2208,rank()over(order by ctr_2208 desc,uid desc) as rk_ctr
from (
select uid,round(sum(is_click)/count(*),3) as ctr_2208
from user_res_event_log_tb
where rid like 'ad%' and left(event_date,7)='2022-08'
group by uid
order by ctr_2208 desc,uid desc ) a )
select * 
from cte1
where uid in (select uid from cte)
order by uid

稍后补一下为什么用rank不能先聚合后开窗的问题