题解 | 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;

# 分表计算,合表汇总。

全部评论

相关推荐

小叮当411:应该是1-3个月吧
点赞 评论 收藏
分享
哈哈哈哈哈哈哈哈哈哈这个世界太美好了
凉风落木楚山秋:毕业出路老师不管,你盖个章他好交差就完事了,等你盖完毕业了就不关他事情了
点赞 评论 收藏
分享
07-07 12:47
门头沟学院 Java
码农索隆:竟然还真有卡体检报告的
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务