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

WITH visit AS (
    SELECT
        DATE(visit_time) AS `date`,
        COUNT(DISTINCT user_id) AS daily_visit
    FROM visit_tb
    GROUP BY DATE(visit_time)
),

ord AS (
    SELECT
        DATE(order_time) AS `date`,
        COUNT(DISTINCT user_id) AS daily_order
    FROM order_tb
    GROUP BY DATE(order_time)
)

SELECT
    v.`date`,
    CONCAT(
        ROUND(100 * COALESCE(o.daily_order, 0) / NULLIF(v.daily_visit, 0), 1),
        '%'
    ) AS cr
FROM visit AS v
LEFT JOIN ord AS o USING (`date`)
ORDER BY v.`date` ASC;