【Hive sql面试题】找出连续活跃3天及以上的用户

表数据如下：

要求：求出连续活跃三天及以上的用户

建表语句和插入数据如下：

create table t_useractive(uid   string,dt    string
);insert into t_useractive
values('A','2023-10-01 10:10:20'),('A','2023-10-02 10:10:20'),('A','2023-10-03 10:16:20'),('A','2023-10-04 10:16:20'),('B','2023-10-01 12:10:25'),('B','2023-10-03 12:10:25'),('B','2023-10-04 18:10:25'),('B','2023-10-05 18:10:25'),('C','2023-10-01 10:20:20'),('C','2023-10-03 10:20:20'),('C','2023-10-05 12:21:24'),('C','2023-10-06 13:11:24'),('D','2023-10-02 12:10:20'),('D','2023-10-03 12:10:20'),('D','2023-10-05 13:12:29'),('D','2023-10-06 15:10:20');

解题思路和答案

核心：

根据uid和 年月日 分组之后（目的是排除同一个用户在同一天登录了多次的情况），下一条数据登录时间与本条数据登 录时间间隔为一天就代表连续登录

思路：

1、根据id和 substr(dt,1,10) 分组（排除同一个用户在同一天登录了多次的情况）

2、根据 substr(dt,1,10) 进行排名（这里排除同一个用户在同一天登录了多次的情况，所以row_number()、rank() 和 dense_rank() 都可以使用）

3、将日期和排名之后的数字相减（使用 date_sub() ），得到一个新的日期

4、如果这个新的日期出现三次及以上，就代表连续登录三天及以上（根据新的日期分组 count() ）

代码：

with t as (select uid,substr(dt,1,10) dt1,row_number() over (partition by uid order by substr(dt,1,10)) day from t_useractive group by uid,substr(dt,1,10)
)
select uid from t group by date_sub(dt1,day),uid having count(*) >= 3;