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

with cte as (
select uid,ctr,cvr,   
               row_number()over(order by ctr desc,uid desc) as dj_rk,
               row_number()over(order by cvr desc,uid desc) as zh_rk
from (
select uid,
       round(sum(is_click)/count(*),  3) as ctr,
       round(sum(is_convert)/sum(is_click),3) as cvr
from user_res_event_log_tb
where rid like 'ad%' and left(event_date,7)='2022-08'
group by uid ) a )
select uid,abs(cast(dj_rk as signed)-cast(zh_rk as signed)) as diff,ctr,cvr
from cte
order by diff desc,uid desc
limit 2;