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

-- select 输出 日期，转化率（百分数四舍五入保留一位小数）
-- 输出格式化 concat和round
-- order 日期升序
-- 人数 distinct去重

with visit_date as (
    select
        distinct
        user_id,
        date(visit_time) as vt
    from visit_tb
),
order_date as (
    select
        distinct 
        user_id,
        date(order_time) as ot
    from order_tb
)

select 
v.vt as date,
concat(round(ifnull(count(o.ot),0)/count(v.user_id)*100,1),"%") as cr
from visit_date v
left join order_date o 
    on v.user_id = o.user_id and v.vt = o.ot
group by v.vt
order by date