题解 | 查询单日多次下订单的用户信息？

with tiaojian as (
select 
t.order_date,
t.user_id,
order_nums
from(
select 
date(order_time) as order_date,
user_id,
count(order_id) as order_nums,
dense_rank()over(partition by date(order_time) order by count(order_id) desc) as pdiff
from order_tb
group by user_id,date(order_time)
having count(order_id)>1
) as t 
where
t.pdiff=1
)


select 
t.order_date,
t.user_id,
t.order_nums,
ub.vip
from tiaojian t left join uservip_tb ub
 on t.user_id=ub.user_id
 order by t.order_nums desc