题解 | 查询下订单用户访问次数？

WITH order092 AS(
    SELECT
        DISTINCT user_id
    FROM order_tb
    WHERE DATE(order_time) = '2022-09-02'
), # 创建临时表来存储仅包含订单日期为2022-09-02数据行的表
visit092 AS(
    SELECT
        user_id,
        info_id
    FROM visit_tb
    WHERE DATE(visit_time) = '2022-09-02' AND DATE(leave_time) = '2022-09-02'
)SELECT # 创建临时表来存储仅包含访问日期和离开日期日期均为为2022-09-02数据行的表
    user_id,
    COUNT(*) AS visit_nums
FROM order092
INNER JOIN visit092 USING(user_id)
GROUP BY user_id
ORDER BY visit_nums DESC #对临时表进行操作