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

with a1 as (
    select date(visit_time) as visit_date,user_id
    from visit_tb
    group by user_id,date(visit_time)
),

a3 as (
    select user_id,date(order_time) as order_date
    from order_tb
    group by user_id,date(order_time)
),

a2 as (
select visit_date,a1.user_id,order_date
from a1
left join a3 
on a3.user_id = a1.user_id and a3.order_date = a1.visit_date
)

select visit_date as date,concat(round((sum(case when order_date is not null then 1 else 0 end)/count(*))*100,1) ,'%') as cr
from a2
group by visit_date
order by date