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

思路：

1. 要计算出是否下单这个字段

-- 每天
-- 转化率
with tmp as (
    select date(v.visit_time) as dt
           , v.user_id
           , max(
                case when date(o.order_time) = date(v.visit_time)
                and o.order_time > v.visit_time
                then 1 else 0 end
           ) as is_order
    from visit_tb v
    left join order_tb o
    on v.user_id = o.user_id
    group by date(v.visit_time), v.user_id
)

select dt as date
       , concat(round(sum(is_order)*100/count(user_id), 1), '%') as cr
from tmp
group by dt
order by dt