2.连续N天登录问题

连续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语句查询出连续三天登录的人员姓名

name	date
张三	2021-01-01
张三	2021-01-02
张三	2021-01-03
张三	2021-01-02
李四	2021-01-01
李四	2021-01-02
王五	2021-01-03
王五	2021-01-02
王五	2021-01-02

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`) rn
         from 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 date3
          from 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 date3
          from t1
    )
select distinct name from t2 where datediff(date3,`date`)=2 ;

例题二： 脉脉

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

表形式如下：maimai.dau

d	uid	module	active_duration	列说明
2020-01-01	1	jobs	324	d：活跃的日期uid：用户的唯一编码module：用户活跃模块active_duration：该模块下对应的活跃时长（单位：s）
2020-01-01	2	feeds	445
2020-01-01	3	im	345
2020-01-02	2	network	765
2020-01-02	3	jobs	342
…	…	…	…

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

-- 建表
-- 表 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), -- 增加伪列rn
     t3 as ( select *,
                    date_sub(d,rn) d2 from t2), -- 增加伪列d2
     t4 as ( select uid
             from t3
             where d <= current_date() -- 题目要求是一个月内的，故用where过滤一下
                  and d >= date_sub(current_date(),30)
             group by uid,d2
            having count(1) >= 2)
select distinct uid from t4;

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

例题三：广州银行

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

字段名	字段类型	字段含义
CARD_NBR	VARCHAR2	卡号
C_MONTH	NUMBER	消费月份
C_DATE	DATE	消费日期
C_TYPEVAR	CHAR2	消费类型
C_ATM	NUMBER	消费金额

每个月每张卡连续消费的最大天数（如卡在当月只有一次消费则为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