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

select
    a.visit_dt as date,
    concat(round(count(b.user_id) / count(a.user_id) * 100, 1),'%') as cr
from
    ( 
        select distinct
            user_id,
            date(visit_time) as visit_dt
        from
            visit_tb
    ) a #用户访问去重
    left join ( #左连接保证所有访问用户都取到
        select distinct
            user_id,
            date(order_time) as order_dt
        from
            order_tb
    ) b #用户下单按天去重
    on a.user_id = b.user_id 
    and a.visit_dt = b.order_dt #如果访问当天没有下单，order_dt关联不到，为null
group by
    1
order by
    date