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

-- 要求输出：日期，转化率（该日下订单人数/访问人数，以百分数形式输出并四舍五入保留1位小数）

-- 访问人数：每天的访问人数有多少
WITH T1 AS (SELECT DATE(visit_time) AS visit_date,COUNT(DISTINCT user_id) AS num_visits
FROM visit_tb
GROUP BY DATE(visit_time)),

-- 每天购买的人数
T2 AS (SELECT DATE(order_time) AS order_date, COUNT(DISTINCT user_id) AS num_orders
FROM order_tb
GROUP BY DATE(order_time)),

T3 AS (SELECT T1.visit_date, T1.num_visits, T2.num_orders FROM T1 JOIN T2 ON T1.visit_date = T2.order_date)

SELECT visit_date AS date, 
CONCAT(ROUND((num_orders/num_visits) * 100, 1), "%") AS cr
FROM T3
ORDER BY visit_date