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

select
    t1.date,
    concat(cast((t1.order_nums*100/t2.visit_nums) as decimal(5,1)),'%') as cr
from

(select
date(v.visit_time) as date,
count(distinct o.user_id) as order_nums-----重要！要计数user_id，不要计数单时间或访问时间
from visit_tb v left join order_tb o on v.user_id=o.user_id
where o.order_time is not null and date(v.visit_time)=date(o.order_time)
group by date(v.visit_time)) t1

inner join

(select
date(v.visit_time) as date,
count(distinct v.user_id) as visit_nums-----重要！要计数user_id，不要计数单时间或访问时间
from visit_tb v left join order_tb o on v.user_id=o.user_id
group by date(v.visit_time)) t2

on t1.date=t2.date
group by t1.date,t1.order_nums,t2.visit_nums
order by t1.date asc;