题解 | #点击率排名与转化率排名差异#
点击率排名与转化率排名差异
https://www.nowcoder.com/practice/45edb549dffd45aa9de9f31b79ec80b9
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;