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

select  a.date ,CONCAT(ROUND((a.num1 / b.num2) * 100, 1), '%')  as cr
from (select date(order_time) as date,count(distinct (user_id)) as num1 from order_tb group by date(order_time)) as a
join (select date(visit_time) as date,count(distinct (user_id)) as num2 from visit_tb group by date(visit_time)) as b
        on a.date=b.date order by date asc;

本人新手小白，入门sql一周多的解题思路如下：

1.子查询 a（订单表数据处理）:从订单表中，按日期统计每天有多少独立用户完成了下单，结果字段为 date（日期）和 num1（独立下单用户数）。具体的SQL语句为：(select date(order_time) as date,count(distinct (user_id)) as num1 from order_tb group by date(order_time)) as a

2.子查询 b（访问表数据处理）:从访问表中，按日期统计每天有多少独立用户进行了访问，结果字段为 date（日期）和 num2（独立访问用户数）。具体的SQL语句为：(select date(visit_time) as date,count(distinct (user_id)) as num2 from visit_tb group by date(visit_time)) as b

3.关联子查询并计算转化率：select a.date, concat(round((a.num1 / b.num2) * 100, 1), '%') as cr from a join b on a.date = b.date order by date asc