2. 查询至少连续三天下单的用户

题目需求

查询订单信息表(order_info)中 最少连续3天 下单的用户id，期望结果如下：

user_id
101

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

思路一

通过与行号作差的方式判断是否连续（推荐）

实现一

-- 4) 按照 user_id, sub_res 进行分组，对每组记录进行计数，选出>=3的user_id
select
	distinct user_id
from
(
  -- 3）计算 create_date - row_num 得到 sub_res
  -- 2）对order_info中的每条记录编号。对 user_id 进行分组，组内按照 create_date 升序排序，并对每条记录使用 row_number 进行编号 row_num
  SELECT
      user_id,
      --create_date,
      --row_number() over(partition by user_id order by create_date) as  row_num
      date_add(create_date, -row_number() over(partition by user_id order by create_date))  as  sub_res
  from 
  (
    -- 1） 对用户进行去重。原因：用户一天可能存在多次下单的情况，我们此处只需要一次即可
    SELECT
      DISTINCT user_id,
      order_id,
      create_date
    from order_info
  )t1
)t2
group by user_id,sub_res having count(*)>=3;

思路二

通过 lead() 开窗函数获取往后第二次的登录日期

实现二

-- 3） 筛选出 sub_res=2 的记录
select
	DISTINCT user_id
from
(
  -- 2） 通过 lead() 开窗函数获取每个用户往后第二次的登录日期
  select
      user_id,
      -- lead(create_date, 2, '9999-12-31') over(partition by user_id order by create_date) next_2_date
      datediff(lead(create_date, 2, '9999-12-31') over(partition by user_id order by create_date), create_date) sub_res
  from
  (
    -- 1） 对用户进行去重。原因：用户一天可能存在多次下单的情况，我们此处只需要一次即可
    SELECT
        DISTINCT user_id,
        order_id,
        create_date
    from order_info
  )t1
)t2
where sub_res=2;