题解 | #广告点击率排名#
广告点击率排名
https://www.nowcoder.com/practice/489a4e583a4549ba8d788212469276e6
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不能先聚合后开窗的问题