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

-- 注意存在9.2多次下单的user，因此在关联前可先去重
with
    users as (
        select distinct
            user_id
        from
            order_tb o
        where
            date(o.order_time) = '2022-09-02'
    )
select
    u.user_id,
    count(v.info_id) as visit_nums
from
    users u
    left join visit_tb v using (user_id)
where
    date(v.visit_time) = '2022-09-02'
    and date(v.leave_time) = '2022-09-02'
group by
    u.user_id
order by
    visit_nums desc