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

with
t1 as(
    select distinct
        user_id
    from
        visit_tb
    where
        user_id in (
            select
                user_id
            from
                order_tb
            where
                date_format(order_time,'%Y%m%d')='20220902'
        )
)
,t2 as(
    select
        user_id,
        count(user_id) as visit_nums
    from
        t1 left join visit_tb using(user_id)
    where
        date_format(visit_time,'%Y%m%d')='20220902'
        and
        date_format(leave_time,'%Y%m%d')='20220902'
    group by
        user_id
    order by
        visit_nums desc
)

select * from t2