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

with
    t as (
        select distinct
            user_id
        from
            order_tb
        where
            date(order_time) = '2022-9-2'
    ) -- 本题唯一的难点：因为group by后不能用distinct函数，所以为了不重复聚合必须另外开一张表，或者用子查询
select
    user_id,
    count(*) as visit_nums
from
    visit_tb
    join t using (user_id)
where
    date(visit_time) = '2022-9-2'
    and date(leave_time) = '2022-9-2'
group by
    user_id
order by
    visit_nums desc