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

# 处理订单表，订单表有相同用户下了两单的情况，根据题目要求，我们只需要统计当日下单用户数量，所以要去重处理
with t2 as(
    select otime 
        ,count(*) as num1
        from(
            select distinct user_id
                ,date(order_time) as otime
                from order_tb) as t1
    group by otime),

# 处理登录表，同样有相同用户当日多次登录情况，我们只需要统计当日登录用户数，所以要去重处理
t4 as(
select vtime
      ,count(*) as num2
    from(
        select distinct user_id
            ,date(visit_time) as vtime
            ,date(leave_time) as ltime
            from visit_tb) as t3
group by vtime
order by vtime)

# 进行连表操作，分子除以分母，得出结果
select otime as date
      ,concat(round(num1/num2*100,1),'%') as cr
    from(
        select *
            from t2 join t4
                on t2.otime=t4.vtime) as t6