Hql之连续登录问题

----先对user_id和create_date去重，然后先把数据按照用户id分组，根据登录日期排序 
with
  t1 as (
    SELECT
      user_id,
      create_date,
      row_number() over (
        PARTITION by
          user_id
        order by
          create_date
      ) as rk
    FROM
      order_info
    group by
      user_id,
      create_date
  ),
  ---用登录日期与rn求date_sub，得到的差值日期如果是相等的，则说明这两天肯定是连续的 
  t2 as (
    SELECT
      user_id,
      date_sub (create_date, rk) data_diff
    FROM
      t1
  ), --------根据id和日期差date_diff分组，连续登录次数即为分组后的count(1) 
  t3 as (
    SELECT
      user_id,
      count(1) as conday
    FROM
      t2
    group by
      user_id,
      data_diff
    having
      count(1) >= 3
  )
SELECT
  user_id
from
  t3

这是最好的方法，还有一个利用lag和lead开窗的方法，不好用