2.连续N天登录问题

2.连续N天登录问题

文章目录

• • 连续N天登录问题两种思路
  • • 思路一
    • 思路二
  • 例题一：OPPO
  • 例题二： 脉脉
  • 例题三：广州银行

连续N天登录问题两种思路

思路一

思路分析过程

原始表中只有dt和name列

--核心代码
->distinct  -- 一个用户一天可能登录多次，所以先去重
-> row_number  -- 增加伪列，分组对登录时间排序  如上图rn列
-> date_sub(dt,rn) as dt2 -- 登录日期与排序需要相减，得到新伪列，如果用户是连续登录的，则该伪列的值应该是一致的，如上图dt2列
-> group by dt2,name  -- 按dt2和用户名分组聚合，得到用户所有连续登录的情况
-> where count(1)>=N天 -- 选出连续登录大于N天的记录
-> distinct name -- 一个用户可能有多次连续登录了指定天，所以继续对结果去重一下
-> count(name) -- 统计有多少用户符合连续登录N天

思路二

思路分析过程

--核心代码
->distinct  -- 一个用户一天可能登录多次，所以先去重
->date_add(dt,N-1) as date2 -- 得到用户当天登录日期的预期第N-1天后的日期
->lead(dt,N-1) over(partition by userid order by dt) as date3  -- 得到当天登录的实际不同日期的第N-1次后的登录日期
->where date2=date3  -- 预期的与实际的是否相同
->distinct  -- 一个用户可能有多次连续登录了指定天，所以继续对结果去重一下
-> count(name) -- 统计有多少用户符合连续登录N天

例题一：OPPO

以下为用户登陆游戏的日期，用一条SQL语句查询出连续三天登录的人员姓名

create table game(name string,  `date` string);
insert overwrite table game values
('张三','2021-01-01'),
('张三','2021-01-02'),
('张三','2021-01-03'),
('张三','2021-01-02'),('张三','2021-01-07'),
('张三','2021-01-08'),
('张三','2021-01-09'),('李四','2021-01-01'),
('李四','2021-01-02'),
('王五','2021-01-03'),
('王五','2021-01-02'),
('王五','2021-01-02');-- 方案一
with t1 as ( select distinct  name,`date` from game),t2 as ( select *,row_number() over (partition by name order by `date`) rnfrom t1),t3 as ( select *,date_sub(`date`,rn) date2 from t2 )select distinct name from t3 group by name,date2 having count(1)>=3;--方案二
select * from game;
with t1 as (select distinct name,`date` from game
),t2 as (select *,date_add(`date`,3-1) as date2,lead(`date`,3-1) over(partition by name order by `date`) as date3from t1)
select distinct name from t2 where date2=date3;--方案二的写法2
with t1 as (select distinct name,`date` from game
),t2 as (select *,lead(`date`,3-1) over(partition by name order by `date`) as date3from t1)
select distinct name from t2 where datediff(date3,`date`)=2 ;

例题二： 脉脉

用户每日登陆脉脉会访问app不同的模块，现有一个表，记录了每日脉脉活跃用户的uid和不同模块的活跃时长

表形式如下：maimai.dau

在过去一个月内,曾连续两天活跃的用户

-- 建表
-- 表 dau   记录了每日脉脉活跃用户的uid和不同模块的活跃时长
create table dau(d string, uid int, module string, active_duration int);
insert overwrite table dau
values ('2020-01-01', 1, 'jobs', 324),('2020-01-01', 2, 'feeds', 445),('2020-01-01', 3, 'im', 345),('2020-01-02', 2, 'network', 765),('2020-01-02', 3, 'jobs', 342);
select *from dau;with t1 as ( select distinct  d,uid from dau),t2 as ( select *,row_number() over (partition by uid order by d) as rn from t1), -- 增加伪列rnt3 as ( select *,date_sub(d,rn) d2 from t2), -- 增加伪列d2t4 as ( select uidfrom t3where d <= current_date() -- 题目要求是一个月内的，故用where过滤一下and d >= date_sub(current_date(),30)group by uid,d2having count(1) >= 2)
select distinct uid from t4;

一月内，没有连续两天活跃的用户

例题三：广州银行

有一张表C_T（列举了部分数据）表示持卡人消费记录，表结构如下：

每个月每张卡连续消费的最大天数（如卡在当月只有一次消费则为1）。

连续消费天数：指一段时间内连续每天都有消费，同一天有多笔消费算一天消费，不能跨月份统计。

create table c_t
(card_nbr string,c_month  string,c_date   string,c_type   string,c_atm    decimal
);
insert overwrite table c_t values(1,'2022-01','2022-01-01','网购',100),(1,'2022-01','2022-01-02','网购',200),(1,'2022-01','2022-01-03','网购',300),(1,'2022-01','2022-01-15','网购',100),(1,'2022-01','2022-01-16','网购',200),(2,'2022-01','2022-01-06','网购',500),(2,'2022-01','2022-01-07','网购',800),(1,'2022-02','2022-02-01','网购',100),(1,'2022-02','2022-02-02','网购',200),(1,'2022-02','2022-02-03','网购',300),(2,'2022-02','2022-02-06','网购',500),(2,'2022-02','2022-02-07','网购',800);
with t1 as (select distinct card_nbr,c_month,c_date from c_t),t2 as (select *,row_number() over (partition by card_nbr,c_month order by c_date) rn from t1  ),t3 as (select *,date_sub(c_date,rn) dt2 from t2  ),-- 此处与之前例题有区别，需要先拿到所有连续消费的情况，而不是直接筛选出连续消费大于N天的记录t4 as (select  dt2,card_nbr,c_month,count(1) as cnt from t3 group by dt2,card_nbr,c_month),-- 不再是去重，而是取出最大的，因为题目要求的是获取 每个月每张卡连续消费的最大天数t5 as ( select *,row_number() over (partition by card_nbr,c_month order by cnt desc) as rn from t4)
select card_nbr,c_month,cnt from t5 where rn=1