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

SELECT
    order_date date,
    CONCAT(ROUND(order_num/COUNT(DISTINCT user_id)*100,1),"%") cr
FROM
    (SELECT
        SUBSTRING(ot.order_time,1,10) order_date,
        COUNT(DISTINCT ot.user_id) order_num
    FROM order_tb ot
    JOIN visit_tb vt ON ot.user_id = vt.user_id
    WHERE SUBSTRING(ot.order_time,1,10) = SUBSTRING(vt.visit_time,1,10)
    GROUP BY SUBSTRING(ot.order_time,1,10)) t1
JOIN visit_tb vt1 ON SUBSTRING(vt1.visit_time,1,10) = t1.order_date
GROUP BY order_date