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

with t1 as (
    select user_id, date_format(order_time, '%Y-%m-%d') as order_time
    from order_tb
    group by user_id, date_format(order_time, '%Y-%m-%d')
), t2 as (
    select user_id, date_format(visit_time, '%Y-%m-%d') as visit_time
    from visit_tb
    group by user_id, date_format(visit_time, '%Y-%m-%d')
)
select t2.visit_time as date,
concat(round(count(distinct t1.user_id)/count(distinct t2.user_id)*100, 1), '%') as cr
from t2 left join t1 on t1.user_id=t2.user_id and t1.order_time=t2.visit_time
group by t2.visit_time
order by t2.visit_time