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

# 多少人访问，多少人下单
with
t1 as(
    select distinct
        user_id,
        date_format(visit_time,'%Y-%m-%d') as visit_date
    from
        visit_tb
)
,t2 as(
    select distinct
        user_id,
        date_format(order_time,'%Y-%m-%d') as order_date
    from
        order_tb
)
,t3 as(
    select
        t1.user_id,
        visit_date as date,
        order_date,
        (
        case
            when visit_date=order_date then 1
            else null
        end
        ) as is_buy
    from
        t1 left join t2 on t1.user_id=t2.user_id
)
,t4 as(
    select
        date,
        concat(round(count(is_buy)/count(distinct user_id)*100,1),'%') as cr
    from
        t3
    group by
        date
    order by
        date
)


select * from t4