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

WITH t1 AS (
    SELECT *
    FROM order_tb
    WHERE DATE(order_time) = '2022-09-02'
),
t2 AS (
    SELECT *
    FROM visit_tb
    WHERE DATE(visit_time)='2022-09-02'
    AND user_id IN
    (SELECT user_id FROM t1 GROUP BY user_id)
),
t3 AS (
    SELECT user_id,
           count(user_id) AS visit_nums
    FROM t2
    GROUP BY user_id
    ORDER BY visit_nums DESC
)SELECT * FROM t3;

# 这道题的关键在于迷惑性，不需要考虑离开也是当天日期！