统计连续3天下单的用户

1. Mysql：

利用Mysql的date_sub函数实现

SELECT DISTINCT user_id from (
SELECT user_id, date_sub(create_date,INTERVAL rn DAY) ds from 
(
  select user_id,create_date,ROW_NUMBER() over(PARTITION by user_id order by create_date) rn from tb_order
) t1
  
) t2 GROUP BY user_id,ds HAVING COUNT(*)>=3;

查询结果

2. SqlServer：

利用SqlServer的dateadd函数实现

SELECT DISTINCT user_id from (

SELECT user_id, dateadd(DAY,-rn,create_date) ds from 
(
  select user_id,create_date,ROW_NUMBER() over(PARTITION by user_id order by create_date) rn from tb_order
) t1

) t2 GROUP BY user_id,ds HAVING COUNT(*)>=3;

查询结果