上次我们根据用户创建账号和登录日志进行了《用SQL进行用户留存率计算》,今天我们继续用这份用户登录日志来计算用户连续登录天数。 1....数据预览 这里我们用到的是用户登录的日志,其中用户每天可能存在多次登录。 用户登录日志 用户登录 以上案例数据 后台回复 955 可以在SQL文件夹里data领取 2....《『数据分析』pandas计算连续行为天数的几种思路》 《利用Python统计连续登录N天或以上用户》 今天,我们用SQL来进行本次的操作,大致分为以下几步: 数据去重 SELECT DISTINCT...(DATE_SUB),并按照用id和差值进行分组计数(这就是用户的连续登录天数) SELECT role_id , DATE_SUB(date,INTERVAL sort DAY) d_group ,...id的连续登录天数的最大值即可 3.
需求 每个用户连续登录最大天数 建表语句 create table login( id string, rq string ) row format delimited fields terminated...by '\t' ; 数据 #这里将数据分开,便于直观看到连续登录天数 insert into table login values (1,"2019-07-26"), (1,"2019-07-27")...id开窗 2、连续登录,所以时间信息,并按照升序,需要在窗口里面添加order by 3、核心逻辑——连续登录的判断是,通过排序添加序号,再用当前日期和当前序号做差, 如果得到日期相同,则表示是连续日期...2、第一次分组是每个用户每天只有一条数据,第二次分组是统计差值日期相同数量,第三次分组是统计每个用户最大连续登录天数 知识点 1、row_number添加序号,无论字段值是否相同 2、date_sub...(日期,数值),用日期-数值,即当前日期的前n天,返回值是日期字符串类型 分析中第3点在hive sql系列(三)中计算连续日活中也用到了日期差值,参考链接: hive sql(三)—— 求所有用户和活跃用户的总数及平均年龄
一、题目 现有各用户的登录记录表t_login_events如下,表中每行数据表达的信息是一个用户何时登录了平台。...现要求统计各用户最长的连续登录天数,间断一天也算作连续,例如:一个用户在1,3,5,6登录,则视为连续6天登录。...,但是这个连续不是真的连续,允许存在间隔一天,是对连续条件考察的升级版。...group_id 分组,计算每次连续登陆的天数,再根据用户分组计算最大连续天数 首先根据user_id和group_id分组,用datediff计算出出最大登陆日期和最小登陆日期,两者做差+1 得到每次连续登陆的天数...然后按照用户分组,使用max()计算每个用户最大连续天数。
业务场景:用户运营需要找出"高活跃用户"(连续登录天数最长的用户),给他们推送 VIP 权益。同时也要找出"即将流失的用户"(连续登录天数突然下降)。...二、题目 现有一张用户登录日志表 t17_zj_user_login,记录了用户每天的登录情况(每天每用户至多一条记录)。请找出每个用户的最大连续登录天数。...2025-01-05 | | 3 | 2025-01-03 | | 3 | 2025-01-04 | +----------+-------------+ 期望输出:每个用户的最大连续登录天数...),再套用差值法 当前连续天数:不只是历史最大,还要知道"截止今天正在连续的最近一段有多少天" 七、知识点总结 考点 说明 ROW_NUMBER + PARTITION BY 按用户分区编号 日期 -...ROW_NUMBER Gaps and Islands 差值法核心 相同差值 = 连续 连续日期的数学特性 DATE_SUB 日期减去整数天数 MAX + GROUP BY 取得每个用户的最长连续段
一、题目背景 这道题来自腾讯微信/QQ的用户活跃度分析岗面试。最长连续活跃天数是衡量用户"上瘾程度"的核心指标——累计活跃天数可以被一次促销活动撑起来,但连续活跃天数反映的是用户是否养成了使用习惯。...业务场景:微信的"连续登录打卡"功能和QQ的"活跃天数勋章"体系背后就是连续活跃天数的计算。产品团队用这个指标找到"可能流失"的用户——昨天还在活跃、今天突然中断了的用户,需要Push拉回。...二、题目 现有一张用户活跃日志表 t11_user_active_log,记录了用户每天的活跃情况(表中每个用户每天最多一条记录)。请计算每个用户的最长连续活跃天数。...,统计每组的天数,即为一个连续活跃段的天数 取每个用户的最大连续天数 维度 评分 题目难度 ⭐️⭐️⭐️⭐️ 题目清晰度 ⭐️⭐️⭐️⭐️⭐️ 业务常见度 ⭐️⭐️⭐️⭐️⭐️ 四、逐步推导 1....六、举一反三 当前连续天数:取 group_date 最大的一段(最近一段连续),而非所有段的最大值 连续N天以上筛选:HAVING 中加条件,找"连续7天活跃"的发勋章资格用户 最长连续不活跃天数:反过来计算
整体实现思路: 1.用户每天可能不止登陆一次,将登录日期去重,取出当日登陆成功的日期,row_number()函数分组排序并计数 2.日期减去计数得到值 3.根据每个用户count(值)判断连续登陆天数...4.最后取连续登陆天数大于等于7天的用户 示例: CREATE TABLE db_test.user_log_test( datestr string comment ‘日期’, uid string...count(值)判断连续登陆天数 select uid,count(*) cnt–连续登陆天数 from ( select uid,date_sub(datestr,num) date_rn...rn from db_test.user_log_test where status=1 ) a where rn=1 ) b)c group by uid,date_rn 4.最后取连续登陆天数大于等于...3连续登陆超过7天) 或者用窗口分析函数更快查询出来 –所有用户信息 select * from ( select uid ,datestr, lead(datestr,6,-1) over
一、题目 有用户登录日志表,包含日期、用户ID,当天是否登录,请查询出当天活跃的用户当前连续活跃天数; +-------------+----------+-----------+ | login_date...| | 2023-08-03 | 4 | 1 | +-------------+----------+-----------+ 二、分析 一眼看上去,连续登录问题...,然后balabla……(如果这样,就走弯路了,首先该题目中的原始数据经过清洗,其次需求只需要当前登录用户的最近联系登录的数据,而不是求历史所有的最大连续登录,千万别思维定式); 1.找到所有用户最后未登录日期...; 2.筛选出最后未登录日期的数据,(如果不存在未登录数据,则代表该用户一直连续登录) 3.对筛选结果进行分组统计; 维度 评分 题目难度 ⭐️⭐️⭐️ 题目清晰度 ⭐️⭐️⭐️⭐️⭐️ 业务常见度 ⭐️...用户3最后一天是未登录状态,所以结果记录中不存在用户3的登录记录。
总第208篇/张俊红 今天来解一道题面试中可能经常会被一些面试官拿来“刁难”的题,就是《如何统计连续打卡天数》,当然了这里面的打卡可以换成任意其他行为,比如连续登陆天数,连续学习天数,连续购买天数,这里的天数也是可以换成小时或者别的时间单位的...id,tdate是日期,is_flag是记录用户当天是否打卡,1为打卡,0为未打卡。...到这里,如果我们要获取连续打卡天数是不是就很容易了。 不过这里面还有一个问题,就是连续打卡天数是截止目前最近的一个 连续打卡天数还是历史坚持最长的打卡天数,这就是传说中的口径问题哈。.../2/3 1 1 2020/2/4 1 1 2020/2/5 1 1 2020/2/6 0 1 2020/2/7 1 1 2020/2/8 1 上面这个例子中,最近连续打卡天数是2,历史最长的连续打卡天数却是...好了,我们继续回到解题上,我们先获取每个用户历史所有连续过得的打卡情况,实现代码如下: select uid ,day_cha ,count(tdate) flag_days
数据 求连续登陆的天数 CREATE TABLE `t_login` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255)
业务过程中,经常遇到需要计算连续天数的场景,比如:连续打卡天数、连续评优的同学、连续下单的客户等。 那么,计算连续天数是否有通用的模型呢?答案是肯定的。...filterT = FILTER('ratings',[level]="A") 分组 这里用到了计算连续天数的一个算法: 将 业务日期距离某固定日期的天数 与 业务表中大于当前业务日期的天数进行求和,...计算连续天数 上面已经计算了连续天数分组标记 group ,将分组标记进行 行计数,即可得到每个业务对象的连续分组天数 successiveDays = SUMMARIZE( 'groupT'..., 'groupT'[name], 'groupT'[group], "rowCNT", COUNTROWS('groupT') ) 计算最大连续天数 某些需求场景下,需要计算每个业务对象的最大连续天数...连续天数通用计算模型 连续天数通用计算模型可以抽象为: _successiveDays = VAR filterT = FILTER('ratings', [level] = "A") // 根据实际场景进行调整
有一张销售表,需要统计:1 按客户的最大连续进货天数;2 连续2天及以上进货的客户数。解决方案首先计算当前进货的最近一次未进货日期,然后用当前进货日期减去这个日期就得到连续进货天数了。...有了连续进货天数,按日期可以得出最大连续进货天数,然后再按客户可以得出按客户的最大连续进货天数,最后取按客户的最大连续进货天数大于等于2的客户数就很容易了。...连续进货天数 = DATEDIFF([最近未进货日期],MAX('销售表'[日期]),DAY)按日期最大连续进货天数:取所选日期范围内,连续进货天数的最大值。...按日期最大连续进货天数 = MAXX(VALUES('日期表'[Date]),[连续进货天数])按客户最大连续进货天数:取所选客户中,每个客户的最大连续进货天数的最大值。...按客户最大连续进货天数 = MAXX(VALUES('客户表'[客户]),[按日期最大连续进货天数])连续进货2天以上的客户数:取所选客户中,每个客户的最大连续进货天数>=2的客户数。
第五步,分组计数 通过上一步,我们可以知道,计算每个用户date_sub列出现的次数即可算出该用户连续登录的天数 data = df.groupby(['role_id','date_sub']).count...自此,我们计算出了每个用户连续登录天数 修改辅助列名称 data = data[['role_id','date_sub','辅助列']].rename(columns={'辅助列':'连续登录天数'...第六步,计算每个用户连续登录最大天数 这里用到的是sort_values和first方法,对每个用户连续登录天数做组内排序(降序),再取第一个值即为该用户连续登录最大天数 data = data.sort_values...补充 当我们计算出每个用户在周期内的每个连续登录天数后,想计算连续登录N天或以上玩家清单就非常方便了,条件筛选即可。 同时,也可以自由计算连续登录最大天数 各玩家数等等。...(by='连续登录天数',ascending=False).groupby('role_id').first().reset_index() #计算每个玩家连续登录最大天数
还是昨天的问题,统计每周内问题小区连续出现天数,MySQL可以实现吗?答案是肯定的,就是过程很曲折。 数据库查询语言基本都是针对整列的,不像excel是单元格粒度的,要判断行与行之间的差异比较麻烦。...首先获取当前日期与一个较早的日期的差值 计算表中大于等于当前日期的行数 两个值求和,求和相等则为连续出现,否则为间隔出现 SQL 呼之欲出 mysql> select -> bc.cellid,...| +--------+--------+------+------+ 13 rows in set (0.00 sec) 得到了 diff 和 rows ,将两者相加,然后按照该列分组计数就是持续天数...,再加上周数,就是每周持续天数 完整代码如下: mysql> select -> bc.cellid, -> bc.cgi, -> count(*) as `con_days`,...---+----------+---------+------------+------------+------+ 7 rows in set (0.00 sec) 如上,利用MySQL获取了每周连续出现天数
昨晚上老同事聚会,一个同事说道一个面试问题没有一个人做出来,就是求连续日期登录次数最大的用户,同事说借助 rownumber即可求解,由于是喝酒聊天,也没有说详细的解决过程。...登录时间里面有详细的时分秒数据,而我们的题目只要求连续的天数,所以使用DATEDIFF函数可以解决, DATEDIFF(d,LoginTime,getdate()) as diffDate , 有多个用户都在登录...开始动手,先构造一个表,插入初始数据: /* 求连续登录次数最多的用户 */ create table UserLoginInfo( ID int IDENTITY primary key,...14 4 li 13 3 wang 14 2 wang 15 1 li 14 1 wang 13 1 这个问题也可以衍生出 求连续登录的用户...,或者求连续登录15天的用户(比如QQ的签到功能),是不是很熟悉呢?
向着自己心中的愿景勇敢前进,踏实走好每一步,终有一天生活会垂青于你 题目:连续5天登录用户 现在我们有一张用户登录日志表,记录用户每天的登录时间, 我们想要统计一下,用户每次连续登录的开始日期和结束日期...,以及连续登录天数。...用户ID 登录日期 1001 2017-01-01 1001 2017-01-02 1001 2017-01-04 1001 2017-01-06 1002 2017-01-02 1002 2017-01...0.01 sec) CREATE TABLE test.tmloginlog ( userid int, logindate date )ENGINE=InnoDB; -- 这里的数据是最简化的情况,每个用户每天只有一条登录信息
一、题目 现有用户登录记录表,已经按照用户日期进行去重处理。...请查询出用户连续登录中出现断点的所有日期 +----------+-------------+ | user_id | login_date | +----------+-------------+...05 | | bbb | 2023-12-06 | | bbb | 2023-12-10 | +----------+-------------+ 二、分析 本题看上去是一个连续登录问题...如果我们有一张每个用户每天登录的全量数据表,则求的是未出现在表中的记录。所以实际考察的是生成内容。...ID', login_date string COMMENT '登录日期' ) COMMENT '用户登录记录表' stored as orc ; --插入数据 insert into t_login_
1.题目 现有用户登录日志表 t_login_log,包含用户ID(user_id),登录日期(login_date)。...数据已经按照用户日期去重,请查出连续登录超过4天的用户ID 2.考点 row_number() 函数 、datediff()函数 、日期格式处理 对于连续登录问题处理逻辑,对于同一个用户,如果连续登录...,row_number排序和 登录日期与月初的日期差是一个恒定值,如果存在非连续,则值不同。...详见下图 可以看到,连续时diff值相同,不连续时diff值就出现变化。这也就说明,只要统计diff值相同的行数,就能计算出每次连续登录的天数。...yyyyMMdd'),'yyyy-MM-dd'),'2022-01-01') as date_diff from t_login_log; 执行结果 step2:row_number()开窗,计算每个用户每个登录日期的排序
(daysBetweenNum / cycleNum) : (daysBetweenNum / cycleNum) + 1; System.out.println("两日期间相隔的天数为
大家的疑问主要有两个 1.关于每个用户的起止时间问题,如果想要的是每个用户最早到最后登录日期中间断点登录,而不是固定周期; 2.生成全量用户的登录记录,这样数据量会不会太大,在实际应用场景中是否可行;...题目原始链接: 具体题目如下,这里把期望结果的数据做下调整,删除bbb的2023-12-10的登录结果。 现有用户登录记录表,已经按照用户日期进行去重处理。...请查询出用户连续登录中出现断点的所有日期 +----------+-------------+ | user_id | login_date | +----------+-------------+...维度 评分 题目难度 ⭐️⭐️⭐️⭐️ 题目清晰度 ⭐️⭐️⭐️⭐️ 业务常见度 ⭐️⭐️⭐️ 三、SQL 1.查询每个用户非连续登录后的首次登录记录,以及与上次登录的日期差,然后再减1。...0代表连续,-1代表为用户首次登录。
好奇心下得知,原来是面试官要求小六用sql实现手写“连续3天登录用户”代码。这个问题虽然说难不难,但说易也不简单,而且,偏受大小厂喜欢。...3.SQL实现 - 见证奇迹的时刻 连续3天登录的用户id sql如下: ? 我们再来看下结果: ? 4.反思总结 - 升华 现在看来,其实也不难嘛!