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

WITH t1 AS (
    SELECT user_id,
           DATE(order_time) AS order_date
    FROM order_tb
    GROUP BY user_id, DATE(order_time)
),
t2 AS (
    SELECT user_id,
           DATE(visit_time) AS visit_date
    FROM visit_tb
),
t3 AS (
    SELECT order_date,
           COUNT(DISTINCT user_id) AS buy_num
    FROM t1
    GROUP BY order_date
),
t4 AS (
    SELECT visit_date,
           COUNT(DISTINCT user_id) AS visit_num
    FROM t2
    GROUP BY visit_date
)
SELECT t3.order_date AS date,
       CONCAT(
           ROUND(100 * buy_num / visit_num,1)
           ,'%') AS cr
       FROM t3
LEFT JOIN t4
ON t3.order_date = t4.visit_date
ORDER BY date ASC;

# 分表计算，合表汇总。