哈喽大家好，我是数据攻略的六哥~
继上篇文章分享了「抖音电商」二面面经
来兑现文末答应大家的承诺了：
接着分享『数据分析求职』主题
——之 大厂面经系列
本篇文章是「快手」数据分析岗

Ps.由于大部分厂校招季招聘，已接近尾声
所以不久会回归到「日常工作干货」的主题分享

老样子，面试问题我将分为3个部分：
手写sql、深挖简历、开放性Case问题
逐一讲解原题、解析思路、注意事项
由于篇幅较长，共分为上下两篇文章来完成
非常建议大家其中case题部分可先自测
不放过任何一个可以实操练习的好机会

一、手写sql

▼ 题目预览：

• 题目1：假设你现在负责直播电商业务，请统计今年双十一开始第一天2024.10.14，该天从零点开始累积到当天结束，进入过大促官方活动直播间的用户数量？
• 题目2：现有一张用户成交金额汇总表，如何不用中位数函数，求解成交订单GMV的中位数？
  
  
  

面试题目1

假设你现在负责直播电商业务，请统计今年双十一开始第一天2024.10.14，该天从零点开始累积到当天结束，进入过大促官方活动直播间的用户数量？user_login_log表结构如下：

• user_id：用户id
• login_time：进入时间

• dt：日期分区

--举例数据如下: 
user_id     login_time            dt
10001   2024-10-14 10:10:10   2024-10-14
10001   2024-10-14 10:17:10   2024-10-14
10002   2024-10-14 12:12:12   2024-10-14
10003   2024-10-14 13:13:13   2024-10-14
10004   2024-10-14 14:14:14   2024-10-14
10005   2024-10-14 16:10:10   2024-10-14
10002   2024-10-15 12:15:10   2024-10-15
10003   2024-10-15 13:18:30   2024-10-15

常见解题思路，共有2种：
第①种简单但易忽视细节，也是高频易错题
第②种考虑更全面，是通用解法

🔻方式①思路解析：

• 目标确认:求解目标是每小时累计uv，
• step1：由于是日志表，同一个用户可能进出多次，故先去重
• step2：由于要求解小时累计进入用户，利用计数开窗函数COUNT()OVER()来实现，因统计累计粒度为小时，所以没有开窗列，直接以小时作为order by排序标准做用户计数，即表示从开始行到当前行的累计计数值。注意：需利用DISTINCT去重，可能存在用户一天在不同时间段都访问过的情况。
  
  

❌易错点：该方式是大家常用逻辑，面试中很多人采用此思路求解，但依据题意和数据源表样式，有一些细节未考虑清楚的话，该方式是有问题的，比如：当日志表中每小时不连续时，该方式无法求得小时中断期间的累计用户数（见下文举例）。

--step2:利用计数开窗函数COUNT()OVER()来实现累计计数
SELECT  td_hr
       ,COUNT(DISTINCT user_id)over(ORDER BY td_hr) AS cume_uv
FROM
(--step1:先对日志表去重
 SELECT  hour(log_time) AS td_hr
        ,user_id
 FROM user_login_log
 WHERE dt = '2024-10-14'
 GROUP BY  hour(log_time)
          ,user_id
)t
;

根据上述提到的易错点：
本题用以上方式只能求得：10、12、13、14、16的累计用户数，故难点在于没有的小时数据需要给它补全了，例如针对11点、15点，出现分段需要补齐时间和数据，希望得到的输出样式为:

🔻方式②思路解析：

• step1：首先对底层日志表去重，以当天小时td_hr+用户user_id为主键做group by
• step2：利用函数SPACE结合POSEXPLODE、 LATERAL VIEW 获取完整连续的小时
• SPACE()函数用法：SPACE(23)返回一个长度为23的空格字符串;
• SPACE与SPLIT函数结合用法：可以得到包含24个空格字符串的数组;
• POSEXPLODE用法：将数组展开，每个值为一行，每行两列分别含原始数据的索引位置（从0开始）和数组值
• LATERAL VIEW POSEXPLODE 结合上述用法：生成连续数字;
• step3：拿原表和上述生成的连续数字pos做计算，补全缺失小时数据

• step4：做累积用户计数，因每天24小时，需用WHERE过滤无意义的统计，且注意DISTINCT用户去重，防止多算。

  
  

--step4：做累积用户计数，因每天24小时，需用WHERE过滤无意义的统计，且注意DISTINCT用户去重，防止多算
SELECT  after_hr
       ,COUNT(DISTINCT user_id) AS uv
FROM
(--step3：关联后利用pos做计算补全缺失小时数据
 SELECT  *
        ,int(td_hr)+pos AS after_hr
 FROM
 (--step1：首先对底层日志表去重，以当天小时td_hr+用户user_id为主键做group by
  SELECT  hour(log_time) AS td_hr
         ,user_id
  FROM user_login_log
  WHERE dt = '2024-10-14'
  GROUP BY  hour(log_time)
           ,user_id
 )t LATERAL VIEW POSEXPLODE(SPLIT(SPACE(23), '')) t AS pos , var  --step2：获取完整连续小时
  
)tt
WHERE after_hr <= 23
GROUP BY  after_hr
;

面试题目2：

现有一张用户成交金额汇总表，如何不用中位数函数，求解成交订单GMV的中位数？usr_order表结构如下：

• user_id：用户ID，主键

• order_gmv：订单金额

--举例数据如下: 
user_id  order_gmv
10001     10
10002     20
10003     40
10004     65

🔻思路解析：

• step1:先正序对每个用户利用ROW_NUMBER()窗口函数做并列排序，同时利用计数窗口函数COUNT()OVER计算总用户数
• step2：求解中位数，利用向下和向上取整函数FLOOR()、CEIL()，找到中位数位置，然后利用AVG()函数对符合位置要求的数据做平均，即可实现。
• 中位数含义：即一组数据排序后处于中间位置上的值，为奇数个数时，中位数即为该位置数据本身；为偶数值时，为中间前后两位置数据均值
• FLOOR()、CEIL()函数用法：FLOOR()函数可以将一个数值向下退，取整为最接近的整数，如FLOOR(4.5)=4；CEIL()函数可以将一个数值向上进，取整为最接近的整数，如FLOOR(4.5)=5，正好可以实现如上取中位数位置的目的。

--step2:利用向下和向上取整函数FLOOR()、CEIL()，找到中位数位置后对值做平均：求中位数值
SELECT  AVG(order_gmv) AS median_gmv
FROM
(--step1:对order_gmv列从小到大进行排列，并计算Y用户数
 SELECT  user_id
        ,order_gmv
        ,ROW_NUMBER() OVER (ORDER BY  order_gmv) AS rn
        ,COUNT(1) OVER()                         AS total_cnt
 FROM usr_order
)t
WHERE rn IN (FLOOR(total_cnt / 2) + 1, CEIL(total_cnt / 2) + 1); 

二、深挖简历

• 题目1：视频的完播率，请简述检验统计量的构造思路是什么？
• 题目2：假如在实际工程中，遇到极大数据体量，有什么方法可以高效计算方差？
  
  

三、开放性Case问题

▼ 题目预览（简历问题延展）：

• 题目1：对于快手短视频，如何构建一套指标体系去监控业务的表现情况？
• 题目2：如何理解并定义流失用户？
  

由于面经解析篇幅较长，所以
第二、三部分的解析咱们放到下篇讲~
本文点赞+在看过20，下周更新此部分😎🍻