题解 | #点击率排名与转化率排名差异#

with k as(select t3.uid,ctr,cvr,rk1,rk2 from
(select uid,ctr,row_number()over(order by ctr desc) as rk1 from (select uid,round(sum(is_click)/count(is_click),3) as ctr from user_res_event_log_tb where rid like 'ad%' and month(event_date) = 8 and year(event_date) =2022 group by uid order by ctr desc,uid desc) t1) t3
inner join
(select uid,cvr,row_number()over(order by cvr desc) as rk2 from (select uid,round(sum(case when is_click=1 then is_convert else 0 end)/sum(is_click),3) as cvr from user_res_event_log_tb where rid like 'ad%' and month(event_date) = 8 and year(event_date) =2022 group by uid order by cvr desc,uid desc) t2) t4 using(uid))
select uid,abs(cast(rk1 as signed)-cast(rk2 as signed)) as diff,ctr,cvr from k order by diff desc,uid desc limit 2