首页
学习
活动
专区
圈层
工具
发布

SQL 每个用户最长连续活跃天数:连续问题经典解法(腾讯面试题)

核心思路: 按用户分组,按活跃日期排序,生成行号 row_num 使用 date_sub(active_date, row_num) 计算差值 —— 连续的日期减去连续递增的行号,得到的日期是相同的 按用户和差值分组...按用户分组,按活跃日期排序生成行号 执行SQL select user_id, active_date, row_number() over (partition by user_id...按用户和group_date分组,统计每段连续天数,取最大值 执行SQL select user_id, max(consecutive_days) as max_consecutive_days..."断签"天数——相邻两段之间隔了多少天 按周/月维度:用 date_trunc('week', active_date) 聚合后统计"连续活跃的周数",而非连续天数 七、知识点总结 考点 说明 ROW_NUMBER...+ date_sub 差值法 连续日期的分组标识:差值相同=同一连续段 GROUP BY user_id, group_date 按用户+分组标识统计每段连续天数 MAX + GROUP BY user_id

34110
  • 您找到你想要的搜索结果了吗?
    是的
    没有找到

    字节跳动大数据面试SQL-最大连续登录天数

    请找出每个用户的最大连续登录天数。...连续的日期会得到相同的差值 按差值分组,COUNT 求每组连续天数,MAX 取最大值 原理图解: 用户1 的日期: 01-01 → ROW_NUMBER=1 → 差值=01-01 - 1 = 12-31...ROW_NUMBER 这次咱们直接使用减完的日期作为分组字段,之前都是使用差值作为分组字段,其实差值用什么都可以,序号、差值、连续的开始日期等等,只要保证用户内不重复就可以。...),再套用差值法 当前连续天数:不只是历史最大,还要知道"截止今天正在连续的最近一段有多少天" 七、知识点总结 考点 说明 ROW_NUMBER + PARTITION BY 按用户分区编号 日期 -...ROW_NUMBER Gaps and Islands 差值法核心 相同差值 = 连续 连续日期的数学特性 DATE_SUB 日期减去整数天数 MAX + GROUP BY 取得每个用户的最长连续段

    39510

    阿里巴巴大数据面试SQL-连续7天以上有订单的商家

    连续出单天数比"累计出单天数"更能衡量商家的经营健康度——连续出单意味着商家每天都在积极运营店铺。 业务场景:平台有个"金牌卖家"认证体系,其中一个硬性条件就是"连续30天每天有订单"。...差值分组:date_sub(order_date, rn) —— 连续日期的差值相同,一旦断天差值改变 分组统计:按 shop_id + grp 分组,COUNT(*) 得到每段连续天数 筛选条件:HAVING...HAVING vs WHERE 的执行时机 筛选 consecutive_days >= 7 必须放在 GROUP BY 之后的 HAVING 或外层 WHERE 中,不能放在 GROUP BY 之前——因为连续天数在分组后才计算出来...六、举一反三 可变N天阈值:把 >= 7 改为参数 >= N,配合不同的 N(7/15/30)实现多级商家分层 最大连续未出单天数:反过来看——连续没有订单的天数,用于识别"即将流失"的商家 带品类维度的连续...差值法 连续日期产生相同 grp,断天时 grp 改变 GROUP BY + HAVING 分组统计连续天数后过滤 >=7 的组 三层子查询嵌套 逐层处理:按天聚合→打标→分组→筛选,逻辑清晰 八、

    33310

    SQL 游戏道具连续购买天数:ROW_NUMBER差值法分组求连续(腾讯面试题)

    请计算每个用户购买每种道具的最长连续购买天数,并输出该连续段的开始日期和结束日期。...解题步骤: 按 (user_id, item_id) 分组,按 purchase_date 排序,计算行号 rn; 使用 date_sub(purchase_date, rn) 计算分组标识——连续的日期会得到相同的基准日期...; 按 (user_id, item_id, 基准日期) 分组,统计每段连续天数、开始日期、结束日期; 按 (user_id, item_id) 取最大连续天数; 维度 评分 题目难度 ⭐️⭐️⭐️⭐️...按用户+道具+基准日期分组,统计每段连续天数 执行SQL select user_id, item_id, group_base, min(purchase_date...如果只写 PARTITION BY user_id,会把不同道具的购买日期混在一起,导致"跨道具的连续天数"被错误合并。

    26210

    腾讯面试题:你今天上班打卡了吗?

    image.png 问题:计算截至当前每个用户已经连续签到的天数(输出表仅包含当天签到的所有用户,计算其连续签到天数) 【解题步骤】 1.分组排序 先看问题,要求输出两个字段:当天签到的用户id和连续签到的天数...该问题是分组排序问题,这类问题要想到《猴子 从零学会SQL》里讲过的窗口函数。 先按用户id分组,找出每个用户id当天未签到的日期,再按日期降序排序。...因为在给出的示例数据中,用户id为1的用户每天都签到,没有未签到日期。那么这类用户的连续签到天数该如何计算呢?...我们可以查询用户签到表的开始日期,将那天作为该用户开始签到的日期,计算该日期和当天的间隔,然后加1,即为该用户的连续签到天数。...因此在以用户id关联两个表时,若在表b中用户id关联不到时,使用用户签到表的开始日期,计算连续签到天数。

    1.1K00

    电商项目分析用户购买行为案例一

    大家好,我是小瑄 在电商项目中经常需要对用户购买行为进行分析,比如需要求用户连续购买天数,用户这次购买与上次购买间隔天数。...这里是基于hive on spark来对数据进行分析的,所以使用sql进行讲解 使用sql求用户连续购买天数以及与上次购买间隔天数,按照下面步骤进行处理 对数据进行聚合/去重 对用户进行分组排序 日期与序号进行减法运算...获取开始连续的日期以及连续天数 使用Hive中lead函数 根据业务需求不一样,可能名称也不一样,主要是理解思想。...思路: 如果我的日期是连续的.这时日期减去序号天数,那么连续的日期会得到同一个日期,我们暂且称之为起始日期。...order_date ,rank_num ,date_sub(order_date,rank_num) as origin_date from TABLE_2 结果如下: 获取开始连续的日期以及连续天数

    2K41

    一文搞懂连续问题

    (具体是什么日期无所谓,只是以一个固定日期为锚点)的差得到排序值1(date_diff),然后使用row_number()函数根据用户分组,按照登陆日期进行排序得到排序值2(row_num),然后用两个排序值做差...统计分析得到最终结果在得到分组ID之后,根据分组ID与特定分组的列,进行分组,即可得到每个连续的段。然后使用聚合函数统计连续行数(连续天数)或者max或者min得到最大最小值等。...然后是在得到连续分组ID 之后,count()统计连续天数,并对统计结果进行筛选。2....百度大数据面试SQL-连续签到领金币该题目对连续条件判断上增加了难度,按月分组,在得到连续分组ID之后,计算出连续天数,还需要对天数进行重置,之后又对不同天数得到金币数量进行计算。...在得到连续分组ID之后 需要计算出连续登陆的最早和最晚日期,然后差值计算,还需要考虑到差值与登陆天数差天的细节。总结通过以上面试题目可以看出,只要找到连续分组ID,所以的题目都可以迎刃而解。

    2K00

    Spark SQL 函数分类导航

    返回会话时区日期加减场景函数说明加/减天date_add,dateadd,date_sub日期加减指定天数加月份add_months日期加减指定月数下一个星期Xnext_day返回下一个指定星期几的日期月末日期...last_day返回当月最后一天生成日期序列sequence生成连续日期/数字序列日期差值场景函数说明天数差datediff,date_diff两个日期之间的天数月数差months_between两个日期之间的月数...→秒时间戳unix_timestamp,to_unix_timestamp转为Unix秒级时间戳秒时间戳→字符串from_unixtimeUnix时间戳转格式化字符串日期→天数unix_date日期转为距...epoch天数天数→日期date_from_unix_date天数转为日期时间戳→秒/毫秒/微秒unix_seconds,unix_millis,unix_micros时间戳转为数值秒/毫秒/微秒→时间戳...grouping,grouping_id标识GROUPINGSETS中的分组级别窗口函数排序编号场景函数说明行号row_number分区内连续编号(1,2,3...)排名(跳号)rank并列相同名次,后续跳号

    40810

    PowerBI 连续天数通用计算模型

    业务过程中,经常遇到需要计算连续天数的场景,比如:连续打卡天数、连续评优的同学、连续下单的客户等。 那么,计算连续天数是否有通用的模型呢?答案是肯定的。...filterT = FILTER('ratings',[level]="A") 分组 这里用到了计算连续天数的一个算法: 将 业务日期距离某固定日期的天数 与 业务表中大于当前业务日期的天数进行求和,...如果得数相同,则相应的业务日期是连续的。...计算连续天数 上面已经计算了连续天数分组标记 group ,将分组标记进行 行计数,即可得到每个业务对象的连续分组天数 successiveDays = SUMMARIZE( 'groupT'...: name:业务对象 date:业务日期 然后,即可使用以上通用模型进行过滤、分组、计算。

    1.5K00

    HiveSQL练习题:计算近一个月活跃、连续活跃、沉默用户需求

    本小节利用row_number()开窗计算出每个设备deviceid的行号,再利用日期dt减去行号,求出相同的dt_sub即为连续登录的日期。...,按照 deviceid 和 dt_sub 进行分组,并计算了每个分组中的行数,即 count_n 列。...它使用了 date_sub() 函数来计算日期,并使用 >= 运算符筛选出符合条件的数据,即日期大于等于最近的 11 月 15 日前的 30 天。这样就可以获取最近一个月内的用户数据。...,并使用 COUNT(DISTINCT dt) 函数计算每个设备的唯一日期数,即活跃天数。...deviceid active_days deviceid1 9 deviceid2 9 四、需求三 4.1 最近一个月内,每个用户的最大连续登陆天数 select deviceid,max(count_n

    98210

    利用Python统计连续登录N天或以上用户

    在有些时候,我们需要统计连续登录N天或以上用户,这里采用python通过分组排序、分组计数等步骤实现该功能,具体如下: 导入需要的库 import pandas as pd import numpy as...pd.to_datetime(df["@timestamp"]) #将日期列转化为 时间格式 第三步,分组排序 分组排序是指将每个用户登录日期进行组内排序 采用groupby方法结合rank方法进行处理...第五步,分组计数 通过上一步,我们可以知道,计算每个用户date_sub列出现的次数即可算出该用户连续登录的天数 data = df.groupby(['role_id','date_sub']).count...第六步,计算每个用户连续登录最大天数 这里用到的是sort_values和first方法,对每个用户连续登录天数做组内排序(降序),再取第一个值即为该用户连续登录最大天数 data = data.sort_values...补充 当我们计算出每个用户在周期内的每个连续登录天数后,想计算连续登录N天或以上玩家清单就非常方便了,条件筛选即可。 同时,也可以自由计算连续登录最大天数 各玩家数等等。

    4.5K30

    『数据分析』pandas计算连续行为天数的几种思路

    这里我们用北京空气质量数据作为案例进行演示,需求是找出北京空气质量连续污染最长持续多久并确定其周期。 ?...图5:辅助列 步骤3:分组计数获得连续天数,分组求最小最大值获得连续 污染起止日期 t.groupby(groupids).agg({ 'time': lambda x:f'{x.min()}~...{x.max()}', # 求起止日期 '空气质量':"count", # 求连续天数 }).nlargest(5,'空气质量') # 取 空气质量 字段最大的前5组数据 ?...图7:辅助列值预览 我们可以发现,按照辅助列分组进行计数即可获得连续污染天数,如上红色标记区域。...图9:辅助列创建思路预览 我们也可以发现,按照辅助列分组计数即可获取空气质量连续天数(优良和污染均可),如上红色区域。

    8.9K11

    最近面试太难了。

    最近有位同学面试了几家,分享了一些觉得有些难度的SQL面试题:比如会让你用SQL实现行转列和列转行操作、用SQL计算留存、用SQL计算中位数、还有如何统计用户最大连续登录天数?...当然这种题变形也很多,连续打卡天数、连续学习天数,连续点击天数等等都是同一个类型,今天我们将会给大家分享SQL和Pandas的多种做法。让大家一次搞懂,下次面试不难!...SQL 8.0窗口函数 实现思路: 对用户ID和登录日期去重 对每个用户ID按照日期顺序进行编号 将登录日期减去编号对应的天数,使连续的日期转换为同一天 将连续日期转换为同一个日期之后就可以按照这个字段分组...ORDER BY role_id,$part_date ) a,(SELECT @prev_date:=NULL,@r:=0) t; 从结果可以看到,对于每个用户下连续的日期都给出了完全相同的分组编号...所以针对这取分组最大的问题还是使用rank函数效果更高一些。

    1.9K32

    5 步搞定 4 层嵌套 SQL 才能算清的股票连涨天数

    嵌套 4 层的 SQL“最长连续上涨天数”,这道题在数据面试里被反复用来考人,据说通过率不到 20%。...要算某支股票历史上收盘价连续上涨的最长天数。逻辑很简单:按日期排序,依次判断每一天是否比前一天上涨,把连续上涨的区间标出来,数每个区间有多长,取最大值。...连续上涨的区间内,这个累加值保持不变。第 2 层:按这个累加值分组,count(*) 数每个区间有多少天。第 1 层:取最大值。费了好大劲算是读懂了,似乎原理也明白了。但下次需求变了呢?...SQLazy 的自然写法SQLazy 的做法很简单:别把逻辑塞进嵌套里,按顺序写出来。最长连续上涨天数,用 SQLazy 的 workflow 写:5 步,和你在脑子里想的逻辑顺序完全一致。...把 partition CODE 加在需要按股票分组的操作上即可:仍然是顺序读下来的 5 步:sort:按股票代码和日期排序segment partition CODE:对每支股票分别按下跌分段summarize

    29320

    数分面试必考题:窗口函数

    2、连续登录问题 假设有一张含两列(用户id、登陆日期)的表,查询每个用户连续登陆的天数、最早登录时间、最晚登录时间和登录次数。...第二步,用user_id和辅助列作为分组依据,分到一组的就是连续登录的用户。在每一组中最小的日期就是最早的登陆日期,最大的日期就是最近的登陆日期,对每个组内的用户进行计数就是用户连续登录的天数。...若求解每个用户的最大登录天数。其实可以在以上的查询结果为基础,利用聚合函数就可以求出最大的登录天数问题。...示例:数据还是上题中的数据,求解连续登录五天的用户 第一步,用lead函数进行窗口偏移,查找每个用户5天后的登陆日期是多少,如果是空值,说明他没有登录。运行的代码为 ?...第二步,用datediff函数计算 (日期-第五次登陆日期)+1是否等于5,等于5证明用户是连续5天登录的,为空值或者大于5都不是5天连续登陆的用户。

    3.2K20

    懂Excel轻松入门Python数据分析包pandas(二十四):连续区域

    后来才发现,原来不是 Python 数据处理厉害,而是他有数据分析神器—— pandas 前言 本系列上一节已经介绍了最简单的 shift 方法应用,这一节将结合其他技巧,解决诸如"某城市一年最大连续没下雨天数...Excel 中的实现方式直观简单 如下一份简单的记录表: - 需要根据这份数据,得到最长连续下雨天数是多少,是几号到几号 - 上图红框是一部分符合条件的,其中最长的红框是需要的结果 按照惯例,先看看如果在...- 接下来只需要条件筛选+分组统计,即可简单求出结果 后面的条件筛选+分组不再用 Excel 操作了(因为操作比较麻烦) pandas 中的对应实现 现在关键是怎么在 pandas 中完成上述...= df.下雨) 相当于 Excel 操作中的 E列 - .cumsum() 相当于 Excel 操作中的 G列 接下来是分组统计,pandas 的分组其实不需要把辅助列加到 DataFrame 上的...: - 行4:筛选下雨的行的条件 - 行6:先对 df 过滤下雨的行,按 diff_nums 分组统计 - 结果是一下子统计出各个连续下雨的天数与日期范围 结果是需要得到其中 count 列的最大值的行

    1.9K30

    懂Excel轻松入门Python数据分析包pandas(二十四):连续区域

    后来才发现,原来不是 Python 数据处理厉害,而是他有数据分析神器—— pandas 前言 本系列上一节已经介绍了最简单的 shift 方法应用,这一节将结合其他技巧,解决诸如"某城市一年最大连续没下雨天数...Excel 中的实现方式直观简单 如下一份简单的记录表: - 需要根据这份数据,得到最长连续下雨天数是多少,是几号到几号 - 上图红框是一部分符合条件的,其中最长的红框是需要的结果 按照惯例,先看看如果在...- 接下来只需要条件筛选+分组统计,即可简单求出结果 后面的条件筛选+分组不再用 Excel 操作了(因为操作比较麻烦) pandas 中的对应实现 现在关键是怎么在 pandas 中完成上述...= df.下雨) 相当于 Excel 操作中的 E列 - .cumsum() 相当于 Excel 操作中的 G列 接下来是分组统计,pandas 的分组其实不需要把辅助列加到 DataFrame 上的...: - 行4:筛选下雨的行的条件 - 行6:先对 df 过滤下雨的行,按 diff_nums 分组统计 - 结果是一下子统计出各个连续下雨的天数与日期范围 结果是需要得到其中 count 列的最大值的行

    1.8K30

    【学习】用R和集算器计算连续上涨5天的股票

    目标:通过日交易数据,从多只蓝筹股中选出连续上涨5天的股票。   ...思路:导入数据;过滤出上个月的数据;按照股票代码分组;将数据按日期排序;计算出每天比上一天的收盘价的增长额;计算出连续正增长的天数;过滤出正增长天数大于等于5的那些股票。   ...A3:=A2.group(Code),按股票代码分组。这和R语言中的split函数功能类似。点击该单元格可以在右边看到计算结果: ?   每行是一个分组,点击其中一行,可以看到分组内的数据: ?...过滤,如果某只股票最大的连涨天数大于等于5,则它就是需要的优质股票。~代表每个分组,即每只股票,这样避免大部分循环语句的使用。结果如下: ?   A9:=A8.(~.Code)。...06-22:由于R不能像集算器那样方便的用~来操作每个分组,因此这里需要一个大循环,每次循环针对一个股票进行计算。   07:按日期排序。

    2.1K90
    领券