select v.user_id,
count(distinct v.visit_time) as visit_nums
from
(select user_id
from order_tb 
where substr(order_time,1,10) = '2022-09-02')a
join visit_tb v
on a.user_id = v.user_id
where substr(v.visit_time,1,10) = '2022-09-02'
group by v.user_id
order by visit_nums desc

多层查询