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

with tiaojian as (
select 
uid,
sum(is_click)/count(id) as ctr,
sum(is_convert)/sum(is_click) as cvr,
dense_rank()over(order by sum(is_convert)/sum(is_click)  desc,uid desc) as m,
dense_rank()over(order by sum(is_click)/count(id) desc,uid desc) as d
from user_res_event_log_tb
where
date_format(event_date,"%Y%m")=202208
and 
rid like "ad%"
group by uid
)

select 
t.uid,
t.diff,
round(t.ctr,3) as ctr,
round(
t.cvr,3) as cvr
from(
select 
uid,
ctr,cvr,
abs(cast(d as signed)-cast(m as signed)) as diff,
dense_rank()over(order by 
abs(cast(d as signed)-cast(m as signed)) desc,uid desc) as pt
from tiaojian
) as t 
where
t.pt<=2