专栏首页猴子数据分析抖音面试题:遇到连续问题怎么办?
原创

抖音面试题:遇到连续问题怎么办?

【抖音面试题】

有一张“用户登陆记录表”,包含两个字段:用户id、日期。

【问题】查询2021年每个月,连续2天都有登陆的用户名单。

【解题步骤】

1.思路

怎么能知道连续登陆用户呢?

首先对用户连续登陆进行标记,也就是日期相同的打赏同一个标记(如下图)。

然后,用登陆日期的“天”和“每个月登陆顺序”的差值来做标记(如下图)。这样就可以知道,当登陆日期连续时,差值就是相同的,代表这些天用户是连续登陆。

根据上图的标记,怎么查询出每个用户每个月连续登陆的天数呢?

可以用分组汇总,也就是分组(group by 月,用户id),统计(对分组后每个组计数就是连续登陆的天数 count)

2. 子查询

1)获取登陆日期的天,需要用到day()函数;

2)获取登录日期的月,需要用到month()函数;

3)获取每个月登陆顺序,这类问题属于“每个+排序”,要想到用《猴子 从零学会SQL》里讲过的知识,用到窗口函数row_number();

4)筛选出2021年的数据。

把上面内容写成SQL就是:

1 select 用户id,日期,
2        month(日期) as 月,
3        day(日期) as 日,
4        row_number() over (partition by month(日期), 用户id
5                            order by 日期) as 每个月登陆顺序
6 from 用户登陆记录表
7 where 日期 between '2021-01-01' and '2021-12-31';

查询结果(把这个SQL记为子查询t1):

用“天”和“每个月登陆顺序”的差值来做标记,也就是上面我们说过的这个图:

1 select 月,日期,用户id,
2        (日 - 每个月登陆顺序) as 标记
3 from t1;

查询结果(把这个SQL记为子查询t2):

3. 汇总分析

1)分组汇总:查询每个月,每个用户,每一次连续登陆的天数。

也就是分组(group by 月,用户id,标记),统计(对分组后每个组计数就是连续登陆的天数 count)

1 select 月,用户id,标记,
2        count(*) as 连续登陆天数
3 from t2
4 group by 月,用户id,标记;

查询结果(把这个SQL记为子查询t3):

2)在上一步的基础上,用where子句筛选出连续2天都有登陆的用户:

1 select distinct 月,用户id
2 from t3
3 where 连续登陆天数 >= 2;

到这里我们已经得到了题目要求的结果,可以把前面的子查询t1、t2、t3代入上面的SQL语句,就得到了最终的SQL:

1 select distinct 月,用户id
2 from
3 (select 月,用户id,标记,
4        count(*) as 连续登陆天数
5 from
6 (select 月,日期,用户id,
7        (日 - 每个月登陆顺序) as 标记
8 from
9 (select 用户id,日期,
10        month(日期) as 月,
11        day(日期) as 日,
12        row_number() over (partition by month(日期), 用户id
13                            order by 日期) as 每个月登陆顺序
14 from 用户登陆记录表
15 where 日期 between '2021-01-01' and '2021-12-31'
16 ) as t1
17 ) as t2
18 group by 月,用户id,标记
19 ) as t3
20 where 连续登陆天数 >= 2;

查询结果:

【本题考点】

1.考查对复杂问题的拆解能力,可以使用逻辑树分析方法,将一个复杂问题拆解为可以解决的子问题,然后逐步解决

2.对子查询的应用,当一个复杂问题需要多个子问题来解决时候,可以把每个子问题写成一个子查询

3.考查对常见函数的了解:month()、day()、count();

4.考查对窗口函数的应用,窗口函数能解决的几类典型问题要能牢记;

【举一反三】

查询2021年每个月,连续5天都有登陆的用户数。

与原题的区别在于:

1)“连续2天”变成了“连续5天”:对最后的where条件进行修改;

2)查询“用户名单”变成了“用户数”:用group by按月分组,然后统计用户数和count(distinct 用户id)。

1 select 月,
2        count(distinct 用户id) as 连续5天登陆的用户数
3 from
4 (select 月,用户id,标记,
5        count(*) as 连续登陆天数
6 from
7 (select 月,日期,用户id,
8        (日 - 每个月登陆顺序) as 标记
9 from
10 (select 用户id,日期,
11        month(日期) as 月,
12        day(日期) as 日,
13        row_number() over (partition by month(日期), 用户id
14                            order by 日期) as 每个月登陆顺序
15 from 用户登陆记录表
16 where 日期 between '2021-01-01' and '2021-12-31'
17 ) as t1
18 ) as t2
19 group by 月,用户id,标记
20 ) as t3
21 where 连续登陆天数 >= 5
22 group by 月;

推荐:如何从零学会SQL?

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 面试遇到不会回答的问题,该怎么办?

    今天给大家讲讲面试过程当中最长遇到的窘境,也是最能体现一个候选人临场应变能力的地方,那就是当我们在面试的过程当中,遇到的问题回答不上来的时候,该怎么办。

    lyb-geek
  • 遇到技术问题搞不定,怎么办?

    最近在学习一个开源的 cmdb 系统 (配置管理系统) ,仓库链接:https://github.com/open-cmdb/cmdb,学习的这个开源项目有以下...

    somenzz
  • 开发过程中遇到问题该怎么办?

    日常开发过程中,总会遇到问题,那么遇到问题该怎么办呢? 首先我们把问题分成大的两类:业务问题、技术问题。

    执笔记忆的空白
  • Java:面试官上来就问:遇到异常怎么办?我懵了

    异常主要处理编译期不能捕获的错误。出现问题时能继续顺利执行下去,而不导致程序终止。确保程序的健壮性。Throwable 是所有 Java 程序中错误处理的父类 ...

    Java帮帮
  • 8.26头条/字节跳动数据分析面试面经

    笔试简单聊一下吧~我记得好像是4部分,第一部分是简答题,一共3题,都是业务分析,第一题是数学计算,好像是一款app,给了第一天日活,目标是30天后日活达到D30...

    牛客网
  • 2020字节跳动抖音架构组前端实习生面经(已到offer)

    个人背景: 武汉理工大学(211)计算机系大三下,准备找实习。最近找大佬内推,投了北京字节跳动,据说是非常核心的一个小组,要求很高。

    @超人
  • Java 服务遇到线上问题怎么办?这样排查很赞!

    线上问题不同于开发期间的 bug,与运行时环境、压力、并发情况、具体的业务相关。对于线上的问题利用线上环境可用的工具,收集必要信息 对定位问题十分重要。

    码农架构
  • 面试官问分布式技术面试题,一脸懵逼怎么办?

    2、当 redis 服务器初始化时,会预先分配 16 个数据库(该数量可以通过配置文件配置),所有数据库保存到结构 redisServer 的一个成员 redi...

    苏先生
  • 张轲:腾讯云H5语音通信QoE优化

        11月份,W3C发布了WebRTC的标准。另外一个专注于WebRTC的国际组织RETF在12月份也发布了第一个RFC8298,目前还没有成为真正的标准。...

    云加社区技术沙龙

扫码关注云+社区

领取腾讯云代金券