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

WITH
t1 AS (     -- 计算每日下单人数
    SELECT
        COUNT(DISTINCT user_id) AS dds,
        DATE(order_time) AS ddt
    FROM order_tb
    GROUP BY DATE(order_time)
),

t2 AS (     -- 计算每日访问人数
    SELECT
        COUNT(DISTINCT user_id) AS fws,
        DATE(visit_time) AS fdt
    FROM visit_tb
    GROUP BY DATE(visit_time)
)

SELECT      -- 主查询，计算每日下单率
    t2.fdt AS date,
    CONCAT(
        ROUND(
            t1.dds / t2.fws * 100.0
        ,1)
        ,'%') AS cr
FROM t2
LEFT JOIN t1
    ON (t1.ddt = t2.fdt)
ORDER BY date;