【SQL笔试题】SN_1 连续登陆系列问题

简介

连续登陆天数场景描述是对一个特定情境或活动连续发生的天数进行详细的阐述。这种描述通常用于展示某个事件或活动的持续时间，以及它对参与者或环境产生的影响。

常见的应用场景：

1. 用户留存分析：通过跟踪用户的连续登录天数，可以分析用户的留存情况，了解用户对产品的粘性和忠诚度。
2. 产品使用频率：连续登录天数反映了用户对产品的使用频率，有助于了解产品的使用情况和用户的需求。
3. 用户活跃度：通过连续登录天数，可以评估用户群体的活跃度，识别高活跃度用户和需要激励的用户。
4. 营销策略效果评估：连续登录天数可以作为衡量营销策略效果的指标，如签到奖励、任务完成奖励等。
5. 用户行为预测：通过历史连续登录数据，可以建立模型预测用户的未来行为，如流失风险、购买倾向等。
6. 产品功能优化：分析连续登录天数与用户使用的产品功能之间的关系，可以为产品功能的优化提供依据。
7. 个性化推荐：根据用户的连续登录天数和相关行为数据，可以进行个性化内容或产品的推荐。
8. 客户关系管理：连续登录天数可以作为客户关系管理中的一个维度，帮助识别和奖励忠诚客户。

常见可能用到的数据分析和数据挖掘方法：

1. 趋势分析：通过绘制连续登录天数的趋势图，可以观察用户群体的整体活跃趋势。
2. 分段分析：将用户按照连续登录天数进行分段，分析不同段内用户的特征和需求。
3. 相关性分析：探究连续登录天数与其他用户行为指标（如购买次数、浏览时长等）之间的相关性。
4. 回归分析：建立回归模型，分析连续登录天数对用户行为（如收入、转化率等）的影响。
5. 用户画像构建：利用连续登录天数作为用户画像的一个维度，结合其他数据构建更全面的用户画像。
6. 漏斗分析：在特定业务流程中，分析连续登录天数对用户转化率的影响，识别潜在的瓶颈和改进点。
7. 聚类分析：对用户进行聚类，根据连续登录天数和其他行为数据将用户分为不同的群体，以进行更精细化的运营。
8. 异常检测：通过检测连续登录天数的异常变化，可以发现潜在的问题或欺诈行为。

示例分析和详解

常见的笔试题考点：

1. 用户最长连续登陆天数。（注意看是否有间断，间断是否统计）
2. 连续登陆超过x天/不少于x天的用户。
3. 任意/最近时间段内用户连续登陆天数/超过x天的用户。

……

题目：用户登录信息表login_tb（log_id：登录动作id,user_id：用户id, log_time：登录时间, log_port：登录端口）。

下面是建表语句和模拟的数据：

drop table if exists `login_tb` ;
create table if not exists `login_tb` (
`user_id` int,
`login_time` datetime,
`login_port` varchar(64));
insert into login_tb values(1101,'2022-02-09 07:24:15','pc');
insert into login_tb values(1102,'2022-02-09 09:12:57','app');
insert into login_tb values(1003,'2022-02-10 09:36:11','m');
insert into login_tb values(1102,'2022-02-10 09:37:01','app');
insert into login_tb values(1104,'2022-02-10 12:01:46','app');
insert into login_tb values(1106,'2022-02-10 10:23:01','app');
insert into login_tb values(1003,'2022-02-11 10:43:01','m');
insert into login_tb values(1102,'2022-02-11 11:56:47','app');
insert into login_tb values(1104,'2022-02-11 14:52:37','app');
insert into login_tb values(1106,'2022-02-11 16:56:27','app');
insert into login_tb values(1003,'2022-02-11 17:43:01','m');
insert into login_tb values(1106,'2022-02-12 10:56:17','app');
insert into login_tb values(1106,'2022-02-15 10:56:17','app');
insert into login_tb values(1106,'2022-02-16 10:56:17','app');
insert into login_tb values(1106,'2022-02-17 10:56:17','app');
insert into login_tb values(1106,'2022-02-18 10:56:17','app');

需求1：请用 SQL 查询用户最长连续登陆天数。

需求2：请用 SQL 查询连续登陆不少于3天的用户。

需求3：请用 SQL 查询2022年2月1日-2022年2月28日用户最长的连续登录天数。

首先，简单查询如下图，我们可以对进行 user_id 和 login_time 排序大致看看情况：

可以看到用户 1003 连续登陆 2 天，其中 2022-02-11 登陆多次；用户 1101 登陆 1 天；用户 1102 连续登陆 3 天；用户 1104 连续登陆 2 天；用户 1106 连续登陆 3 天，间断了 2 天，又接着连续登陆了 4 天。

注意事项：

1. 一个用户/商品一天可以登陆/下单多次，因此需要注意去重复。
2. 在一段时间内，可能连续登陆多天，间断几天后，又连续登陆。

下面我们介绍通过 SQL 方法主逐步实现上面需求。

方法一：利用排名窗口函数

1. 可能出现多次登陆，对所需列去重复；
2. 利用 row_number 对每个 user_id 登录日期进行升序排序，得到 rn 列（如果是连续的，可以发现用户 user_id 对应的排序字段 rn 是连续的）；
3. 利用 date_sub 将 login_date 与 rn 相减，可以得到“伪日期列-dt_uid”，可以发现不管是否间断，若连续登录，dt_uid 对应相同；

4. 对 user_id 和 dt_uid 进行分组聚合，再使用 datediff 或者 count ，就可以得出每个 user_id 对应连续的“开始日期、结束日期和连续天数”。

5. 结合具体题目需求，添加筛选或稍微处理，基本上可以应对相关系列题目。

需求答案：

########### 需求1：请用 SQL 查询用户最长连续登陆天数。
-- 一天可能出现多次登陆，去重
with t0 as
         (select distinct user_id, date(login_time) as login_date
          from login_tb),
-- 利用 row_number 对每个 user_id 登录日期进行升序排序，得到 rn 列（如果是连续的，可以发现用户 user_id 对应的排序字段 rn 是连续的）
-- 利用 date_sub 将 login_date 与 rn 相减，可以得到“伪日期列-dt_uid”，可以发现不管是否间断，若连续登录，dt_uid 对应相同；
     t1 as
         (select user_id
               , login_date
               , row_number() over (partition by user_id order by login_date) as rn
               , date_sub(login_date, interval row_number() over (partition by user_id order by login_date)day) as dt_uid
          from t0),
-- 对 user_id 和 dt_uid 进行分组聚合，再使用 datediff 或者 count ，就可以得出每个 u