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

with t as (select v.user_id, date(v.visit_time) as visit_date, date(o.order_time) as order_date
from visit_tb v left join order_tb o
on v.user_id = o.user_id
and date(v.visit_time) = date(o.order_time)),

t2 as (select user_id, visit_date, case when order_date is null then 0 else 1 end as if_order
from t),


-- 每一天有多少个不同用户的订单
t3 as (select visit_date as date, count(distinct user_id) as num_orders
from t2
where if_order = 1
group by visit_date),

-- 每一天有多少个不同的用户访问
t4 as (select visit_date as date, count(distinct user_id) as num_visits
from t2
group by visit_date)

select t3.date, concat (round (100*(t3.num_orders / t4.num_visits),1), "%") as cr
from t3 inner join t4 
on t3.date = t4.date
order by t3.date