【抖音面试题】
有一张“用户登陆记录表”，包含两个字段：用户id、日期。
【问题】查询2021年每个月，连续2天都有登陆的用户名单。
【解题步骤】
1.思路
怎么能知道连续登陆用户呢？
首先对用户连续登陆进行标记，也就是日期相同的打赏同一个标记（如下图）。
然后，用登陆日期的“天”和“每个月登陆顺序”的差值来做标记（如下图）。这样就可以知道，当登陆日期连续时，差值就是相同的，代表这些天用户是连续登陆。
根据上图的标记，怎么查询出每个用户每个月连续登陆的天数呢？
可以用分组汇总，也就是分组（group by 月,用户id），统计（对分组后每个组计数就是连续登陆的天数 count）
2. 子查询
1）获取登陆日期的天，需要用到day()函数；
2）获取登录日期的月，需要用到month()函数；
3）获取每个月登陆顺序，这类问题属于“每个+排序”，要想到用《猴子 从零学会SQL》里讲过的知识，用到窗口函数row_number()；
4）筛选出2021年的数据。
把上面内容写成SQL就是：
select 用户id,日期,month(日期) as 月,day(日期) as 日,row_number() over (partition by month(日期), 用户idorder by 日期) as 每个月登陆顺序from 用户登陆记录表where 日期 between '2021-01-01' and '2021-12-31';
查询结果（把这个SQL记为子查询t1）：
用“天”和“每个月登陆顺序”的差值来做标记，也就是上面我们说过的这个图：
select 月,日期,用户id,(日 - 每个月登陆顺序) as 标记from t1;
查询结果（把这个SQL记为子查询t2）：
3. 汇总分析
1）分组汇总：查询每个月，每个用户，每一次连续登陆的天数。
也就是分组（group by 月,用户id,标记），统计（对分组后每个组计数就是连续登陆的天数 count）
select 月,用户id,标记,count(*) as 连续登陆天数from t2group by 月,用户id,标记;
查询结果（把这个SQL记为子查询t3）：
2）在上一步的基础上，用where子句筛选出连续2天都有登陆的用户：
select distinct 月,用户idfrom t3where 连续登陆天数 >= 2;
到这里我们已经得到了题目要求的结果，可以把前面的子查询t1、t2、t3代入上面的SQL语句，就得到了最终的SQL：
select distinct 月,用户idfrom(select 月,用户id,标记,count(*) as 连续登陆天数from(select 月,日期,用户id,(日 - 每个月登陆顺序) as 标记from(select 用户id,日期,month(日期) as 月,day(日期) as 日,row_number() over (partition by month(日期), 用户idorder by 日期) as 每个月登陆顺序from 用户登陆记录表where 日期 between '2021-01-01' and '2021-12-31') as t1) as t2group by 月,用户id,标记) as t3where 连续登陆天数 >= 2;
查询结果：
【本题考点】
1.考查对复杂问题的拆解能力，可以使用逻辑树分析方法，将一个复杂问题拆解为可以解决的子问题，然后逐步解决
2.对子查询的应用，当一个复杂问题需要多个子问题来解决时候，可以把每个子问题写成一个子查询
3.考查对常见函数的了解：month()、day()、count()；
4.考查对窗口函数的应用，窗口函数能解决的几类典型问题要能牢记；
【举一反三】
查询2021年每个月，连续5天都有登陆的用户数。
与原题的区别在于：
1）“连续2天”变成了“连续5天”：对最后的where条件进行修改；
2）查询“用户名单”变成了“用户数”：用group by按月分组，然后统计用户数和count(distinct 用户id)。
select 月,count(distinct 用户id) as 连续5天登陆的用户数from(select 月,用户id,标记,count(*) as 连续登陆天数from(select 月,日期,用户id,(日 - 每个月登陆顺序) as 标记from(select 用户id,日期,month(日期) as 月,day(日期) as 日,row_number() over (partition by month(日期), 用户idorder by 日期) as 每个月登陆顺序from 用户登陆记录表where 日期 between '2021-01-01' and '2021-12-31') as t1) as t2group by 月,用户id,标记) as t3where 连续登陆天数 >= 5group by 月;
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
