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

#2022年9月2日    下订单的用户   访问次数 访问次数定义为用户在该日"访问-离开"则记录1次

with visitInfo as 
(select
    user_id, days, sum(status + 1) / 2 - sum(status) as visit_nums
from
    (
    select
        user_id, date_format(visit_time, "%Y-%m-%d") as days, visit_time as ts, 1 status
    from
        visit_tb
    union all
    select
        user_id, date_format(leave_time, "%Y-%m-%d") as days, leave_time as ts, -1 status
    from
        visit_tb
    ) as log_info
group by
    user_id, days)
select
    user_id, round(visit_nums, 0) as visit_nums
from
    visitInfo
where
    days = '2022-09-02' and user_id in (select
                                            user_id
                                        from
                                            order_tb
                                        where   
                                            order_time between "2022-09-02 00:00:00" and "2022-09-02 23:59:59")
order by
    visit_nums desc;