水善利万物而不争,处众人之所恶,故几于道💦
题目:
查询订单信息表(order_info)中最少连续3天下单的用户id
期望结果如下:
需要用到的函数:
row_number()、date_sub(a,b)
select
user_id
from
(
select
user_id,
date_sub (create_date, rn)
from
(
select
user_id,
create_date,
row_number() over (partition by user_id order by create_date asc) rn
from
order_info
group byuser_id,create_date
) t1
) t2
group by
user_id
having
count(*) >= 3
总结:
row_number()
:按顺序编号
date_sub(a,b)
:返回a日期的前b天,也就是做减操作(a-b)