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

-- 下订单客户：select distinct user id from order tb 
-- 某日访问离开判断：date提取日期，两个日期相减判断是否为0，datediff函数，为0则记录一次
with
    visit_date as (
        select
            user_id,
            date (visit_time) as vt,
            if (
                datediff (date (leave_time), date (visit_time)) = 0,
                1,
                0
            ) as visit_counted
        from
            visit_tb
        where
            user_id in (
                select distinct
                    user_id
                from
                    order_tb
                where
                    date (order_time) = "2022-09-02"
            )
    )
select
    user_id,
    sum(visit_counted) as visit_nums
from
    visit_date
where
    vt = "2022-09-02"
group by
    user_id
order by
    visit_nums desc