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

#2022年9月2日这天下订单的用户的访问次数
#访问次数定义为用户在该日"访问-离开"则记录1次，输出结果按照访问次数降序排序

select
t1.user_id,
sum(t2.visit_nums) as visit_nums
from

#92下订单的用户
(select
distinct user_id
from order_tb
where order_price>0 and date(order_time) = '2022-09-02')t1

inner join

#用户访问次数
(select
user_id,
count(if(datediff(visit_time,leave_time)=0,visit_time,null)) as visit_nums
from visit_tb
where date(visit_time)='2022-09-02' 
group by 1)t2

on t1.user_id = t2.user_id
group by 1
order by 2 desc