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

-- 不考虑访问和离开时间隔天的
-- 两个分开计数，不连接，避免了下订单却没访问，访问了没下订单的问题
-- 跟题目要求不符，需要distinct user——ID
with order_nums as(
    select date(order_time) as order_date,count(distinct user_id) as order_nums
    from order_tb
    group by order_date
),

visit_nums as(
    select date(visit_time) as visit_date,count(distinct user_id) as visit_nums
    from visit_tb
    group by visit_date
)

select o.order_date as date,concat(round(100*o.order_nums/v.visit_nums,1),'%') as cr
from order_nums o
inner join visit_nums v on o.order_date=v.visit_date
order by date;