题解 | #统计最大连续登录天数区间#

1. 关键知识点：dense_rank，distinct，date_sub，count/max，case when，order by
2. 思路解析：
   ①用窗口函数dense_rank对每个用户的登录按登录日期升序进行编号
   ②用date_sub计算每次登录的向前偏移编号天数后的基准日期，保证连续两天登录的记录的基准日期相同
   ③统计每个基准日期对应连续登录天数
   ④计算每个用户最大的连续登录天数
   ⑤用case when生成最大登录天数对应的区间值
   ⑥按区间值分组，统计每个区间内的用户数

select
  CASE
    WHEN max_login_days = 1 THEN '未连续登录'
    WHEN max_login_days < 4 THEN '连续登录2~3天'
    WHEN max_login_days < 8 THEN '连续登录4~7天'
    ELSE '连续登录大于7天'
  END as days_range,
  count(1) as user_num
from
  (
    -- 计算每个用户的最多连续登录天数
    select
      uid,
      max(login_days) as max_login_days
    from
      (
        -- 计算每次连续登录天数
        select
          uid,
          base_dt,
          count(1) as login_days
        from
          (
            -- 连续登录的记录将是同一个base_dt
            select
              uid,
              login_dt,
              rn,
              DATE_SUB(login_dt, INTERVAL rn DAY) as base_dt
            from
              (
                select
                  DISTINCT uid,
                  DATE(login_date) as login_dt,
                  DENSE_RANK() over(
                    partition by uid
                    order by
                      DATE(login_date)
                  ) as rn
                from
                  user_login_tb
              ) as t_login_date_rn
          ) as t_login_base
        group by
          uid,
          base_dt
      ) as t_login_idx
    group by
      uid
  ) as t_max_login_days
group by
  days_range
order by
  user_num desc;