-- 第一题 最大连续活跃天数
select
  user_id,
  max(act_day) max_act_day
from
  (
    select
      user_id,
      gp,
      count(*) act_day
    from
      (
        select
          user_id,
          date_sub(
            login_date,
            row_number() over(
              partition by
                user_id
              order by
                login_date
            )
          ) gp
        from
          (
            select
              user_id,
              date_format(online_time, 'yyyy-MM-dd') login_date
            from
              (
                select
                  1 user_id,
                  '2024-01-01 01:01:01' online_time
                union all
                select
                  1 user_id,
                  '2024-01-02 01:01:01' online_time
                union all
                select
                  1 user_id,
                  '2024-01-03 01:01:01' online_time
                union all
                select
                  1 user_id,
                  '2024-01-05 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-02 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-05 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-06 01:01:01' online_time
                union all
                select
                  2 user_id,
                  '2024-01-07 01:01:01' online_time
                union all
                select
                  3 user_id,
                  '2024-01-01 01:01:01' online_time
                union all
                select
                  3 user_id,
                  '2024-01-02 01:01:01' online_time
                union all
                select
                  3 user_id,
                  '2024-01-03 01:01:01' online_time
              ) tmp
            group by
              user_id,
              login_date
          )
      )
    group by
      user_id,
      gp
  ) group by user_id;