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

WITH t1 AS (SELECT user_id, DATE_FORMAT(order_time, '%Y-%m-%d') AS order_time
            FROM order_tb
            GROUP BY user_id, DATE_FORMAT(order_time, '%Y-%m-%d')),
     t2 AS (SELECT user_id, DATE_FORMAT(visit_time, '%Y-%m-%d') AS visit_time
            FROM visit_tb
            GROUP BY user_id, DATE_FORMAT(visit_time, '%Y-%m-%d'))
SELECT temp1.order_time AS date, CONCAT(round(cnt1 / cnt2 * 100, 1), '%') AS cr
FROM (SELECT order_time, COUNT(1) AS cnt1 FROM t1 GROUP BY order_time) temp1
RIGHT JOIN (SELECT visit_time, COUNT(1) AS cnt2 FROM t2 GROUP BY visit_time) temp2
ON temp1.order_time = temp2.visit_time
ORDER BY order_time