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

with ox as(
select DATE_FORMAT(o.order_time, '%Y-%m-%d') AS date, count(distinct o.user_id) ou
             from order_tb o 
             group by DATE_FORMAT(o.order_time, '%Y-%m-%d')
),
vx as(
select DATE_FORMAT(v.visit_time, '%Y-%m-%d') AS date, count(distinct v.user_id) vu
             from visit_tb v
             group by DATE_FORMAT(v.visit_time, '%Y-%m-%d')
)
SELECT vx.date,
       concat(round(ox.ou/vx.vu*100,1), '%') cr
FROM vx 
left join ox on ox.date=vx.date
order by vx.date;