Sql必会-求用户连续登录天数

1、指标定义

连续登录天数指的是用户在某一段时间内没有间断地登录应用或平台的天数。

2、指标用途

在用户行为数据分析中，用户连续登录天数指标主要有以下用途：

1. 衡量用户粘性：了解一定时间内用户在平台上保持活跃的天数，衡量产品吸引力的工具。
2. 制定激励措施：平台可以根据用户的连续登录天数提供奖励或优惠，如签到活动。
3. 分析用户习惯：通过这个指标，平台可以了解用户的使用习惯并调整运营策略。
4. 预测用户流失：连续登录天数的下降通常意味着用户的活跃度减弱，可能导致未来的流失。此时可以采取相应的留存策略、

其是衡量用户粘性和平台吸引力的重要工具。通过合理的分析和优化策略，平台可以有效提升用户留存率和长期价值。

3、计算思路

1.开窗函数排序，计算辅助日期cdate

select
          user_id,
          active_date,
          rn,
          date_sub(active_date, rn -1) as cdate
        from
          (
            SELECT
              active_date,
              user_id,
              row_number() over (
                partition by
                  user_id
                order by
                  active_date
              ) as rn
            FROM
              user_log_records
            where
              substr(active_date, 1, 7) = '2025-06'
          ) a

执行结果t1：

　　　　

 2.计算用户连续登录天数

select
      user_id,
      cdate,
      min(active_date) as `开始日期`,
      max(active_date) as `结束日期`,
      count(1) as cont_days -- `连续登录天数`
    from t1
    group by
      1,
      2

执行结果t2：

　　　　

 3.求6月份连续登录7天（含）以上的用户ID、次数、最大连续登录天数

select
  user_id,
  count(1) as `次数`,
  max(cont_days) as `最大连续登录天数`
from
  t2
where
  cont_days >= 7
group by
  1
order by
  2 desc

执行结果：　

　　　　

 

完整Sql代码:

with
  login_cdays as (
    select
      user_id,
      cdate,
      min(active_date) as `开始日期`,
      max(active_date) as `结束日期`,
      count(1) as cont_days-- `连续登录天数`
    from
      (
        select
          user_id,
          active_date,
          rn,
          date_sub(active_date, rn -1) as cdate
        from
          (
            SELECT
              active_date,
              user_id,
              row_number() over (
                partition by
                  user_id
                order by
                  active_date
              ) as rn
            FROM
              user_log_records
            where
              substr(active_date, 1, 7) = '2025-06'
          ) a
      ) t
    group by
      1,
      2
  )

select
  user_id,
  count(1) as `次数`,
  max(cont_days) as `最大连续登录天数`
from
  login_cdays
where
  cont_days >= 7
group by
  1
order by
  2 desc