题解 | 统计用户从访问到下单的转化率

with tmp1 as (
select 
time
,max(rn) as rn1
from(
select 
user_id
,substr(order_time,1,10) as time
,dense_rank() over(partition by substr(order_time,1,10) order by user_id) as rn
from order_tb) t
group by time),

tmp2 as (
select 
time
,max(rn) as rn2
from(
select 
user_id
,substr(leave_time,1,10) as time 
,dense_rank() over(partition by substr(leave_time,1,10) order by user_id) as rn
from visit_tb) t
group by time )

select 
t1.time as date
,concat(round(((t1.rn1 / t2.rn2) * 100),1),'%') as cr
from tmp1 as t1
inner join tmp2 as t2
on t1.time = t2.time;