大数据面试SQL038 用户连续登录所有断点日期（二）

一、题目

038题目发出后，群里很多朋友发出各种疑问，也给出了很多写法。大家的疑问主要有两个

1.关于每个用户的起止时间问题，如果想要的是每个用户最早到最后登录日期中间断点登录，而不是固定周期；

2.生成全量用户的登录记录，这样数据量会不会太大，在实际应用场景中是否可行；

在我给出038自己的写法后，一直在思考面试出这个题目的落脚点在哪？想要测试应试者具备哪些知识点。始终没有特别清晰的思路，但是当看到群里小伙伴给出的解法后，恍然大悟，还是在考察连续问题+数据生成。 这个问题我们在039问题中，也是类似考察点，只不过展现形式不一样。

具体题目如下，这里把期望结果的数据做下调整，删除bbb的2023-12-10的登录结果。

现有用户登录记录表，已经按照用户日期进行去重处理。请查询出用户连续登录中出现断点的所有日期

+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| aaa      | 2023-12-01  |
| aaa      | 2023-12-02  |
| aaa      | 2023-12-04  |
| aaa      | 2023-12-08  |
| aaa      | 2023-12-10  |
| bbb      | 2023-12-01  |
| bbb      | 2023-12-03  |
| bbb      | 2023-12-04  |
| bbb      | 2023-12-07  |
| bbb      | 2023-12-08  |
| bbb      | 2023-12-09  |
+----------+-------------+

期望输出结果

+----------+-------------+
| user_id  | login_date  |
+----------+-------------+
| aaa      | 2023-12-03  |
| aaa      | 2023-12-05  |
| aaa      | 2023-12-06  |
| aaa      | 2023-12-07  |
| aaa      | 2023-12-09  |
| bbb      | 2023-12-02  |
| bbb      | 2023-12-05  |
| bbb      | 2023-12-06  |
+----------+-------------+

二、分析

连续问题，我们找到用户每次断点后的登录日期，并计算出与上次登录的日期差，然后根据日期和日期差生成断点的日期记录。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️

三、SQL

1.查询每个用户非连续登录后的首次登录记录，以及与上次登录的日期差，然后再减1。0代表连续，-1代表为用户首次登录。

select user_id,login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_date,datediff(login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date))-1 as diff_days from t_login_38

查询结果

2.限制diff_day>0,然后使用生成函数，根据diff_day生成断点日期记录。

select user_id,login_date,lag_date,diff_days,sub_day,date_sub(login_date,sub_day) as un_login_date
from(
select user_id,login_date,lag_date,diff_days
from
(
 select
  user_id,
  login_date,
  lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_date,
  datediff(login_date,lag(login_date,1,login_date) over(partition by user_id order by login_date))-1 as diff_days
 from t_login_38
 )t1
where diff_days >0) tt1
 lateral view explode(sequence(1,diff_days)) tt2 as sub_day

查询结果

根据上面记录，只要取user_id 和un_login_date列即可。

3.群里小伙伴sql

select
    user_id
    ,date_sub(login_date,rn) as miss_login_date
from
(
    select *
    from 
    (
        select *
            ,datediff(login_date,lag_date)-1 as diff_days
        from
        (
            select *
                ,lag(login_date,1,login_date) over(partition by user_id order by login_date) as lag_date
            from t_login_38
        )tmp
    )tmp
    where diff_days>0 --0是连续
)tmp
lateral view posexplode(split(space(diff_days),'')) ed  as rn,value
where rn>=1;

查询结果

四、建表语句和数据插入

--建表语句
create table t_login_38
(
user_id string COMMENT '用户ID',
login_date string COMMENT '登录日期'
) COMMENT '用户登录记录表'
stored as orc
;
--插入数据
insert into t_login_38(user_id,login_date)
values
('aaa','2023-12-01'),
('aaa','2023-12-02'),
('aaa','2023-12-04'),
('aaa','2023-12-08'),
('aaa','2023-12-10'),
('bbb','2023-12-01'),
('bbb','2023-12-03'),
('bbb','2023-12-04'),
('bbb','2023-12-07'),
('bbb','2023-12-08'),
('bbb','2023-12-09')