大家好，我是老兵。

本期将为大家介绍大数据面试中常见的SQL真题。我尽量选取了几种不同场景的SQL计算方法，案例具有很强的代表性，在此基础可以衍生出很多场景，希望伙伴们看后可以进行思考。

文章旨在帮助大家抱团取暖，希望对小伙伴笔试会有所帮助。

1 app连续三天登录

题目介绍

用户登录行为日志中记录用户id、登录时间字段，统计日志中连续三天登录的用户数，数据示意如下:

题目分析

这是个很经典的SQL笔试题，也是出镜率最高的类型。连续登录的含义如上图（用户002在2022-11-01~2022-11-03均有记录)，指的是用户在某一时间段内每天均有登录记录。

通过分析可知连续登录时用户登录时间和其对应排名差值相等，这里使用uid开窗排序 + date_sub(dt,rank)来计算。

解题方法

1) 实现思路

• 将用户分组并按照时间排序，并记录rank排名
• 计算dt-rank的差值，差值与用户共同分组
• 统计count并找出 count > 3的用户

2）SQL代码

select   userid   ,min(dt) as start_date   ,max(dt) as end_date   ,count(1) as times from (   select      userid     ,dt     ,date_sub(dt, rn) as date_diff     from     (       select          userid         ,dt         ,row_number() over(partition by           userid order by dt) as rn       from          user_tables     ) ) group by    userid, date_diff having times >= 3 

2 日/周/月留存

题目介绍

用户登录行为日志中记录用户id、登陆时间、 登录app字段，计算app某天的3日/7日/14日/30日用户留存。数据示意如下:

题目分析

在互联网场景中用户留存是衡量APP用户活跃的重要指标之一，留存越高，则代表用户粘性越好。留存的含义为用户在时间t1活跃同时在时间t2再次活跃。

计算留存需要找到两个时间点，一个是统计日期t1（如2022-11-01），另一个点为留存时间t2(2022-11-01后的7日/14日/30日区间)，两段数据进行关联且均有记录，最后取日期差即可得到留存。

解题方法

1）实现思路

• 获取统计日期时间段数据并根据dt,appid,uid分组groupby
• 获取留存时间段数据并根据dt,appid,uid分组groupby
• join关联获取时间差，分别统计3日/14日/21日/30日留存

2）SQL代码

讯享网select    t1.appid,   count(t1.uid) as active_users,   count(case when datediff(t2.cu_dt,t1.cu_dt)=1 then t2.uid end) as day2_active_users,   count(case when datediff(t2.cu_dt,t1.cu_dt)=2 then t2.uid end) as day3_active_users,   count(case when datediff(t2.cu_dt,t1.cu_dt)=6 then t2.uid end) as day7_active_users,   count(case when datediff(t2.cu_dt,t1.cu_dt)=13 then t2.uid end) as day14_active_users,   count(case when datediff(t2.cu_dt,t1.cu_dt)=29 then t2.uid end) as day30_active_users,   t1.dt from  (select   apptypeid,   uid,   dt,   from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt from user_tables where dt='${startDate}' group by appid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') )t1 left join (select   apptypeid,   uid,   dt,   from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as cu_dt from user_tables where dt>'${startDate}' and dt<='${startDate+29d}' group by appid,uid,dt,from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') ) t2 on t1.appid=t2.appid and t1.uid=t2.uid group by t1.appid,t1.dt 

3 某时刻app最多在线人数

题目介绍

用户行为日志中记录用户id、appid、进入时间、退出时间字段，计算同一时刻的app的最大人数，数据示意如下:

讯享网

题目分析

此为经典状态计算SQL，常用于统计某时刻某状态的最大在线人数（直播、商场、车进出流等）。

此类问题最重要的是计算瞬时的最大计数，采用编码+合并的方法，将进标志为+1，出标志为-1，最后union结果并根据appid进行sum（）开窗并排序。

解题方法

1）实现思路

• 计算瞬时计数。查询进出记录同时设置标志位，结果union合并
• 根据appid开窗sum，组内根据dt升序和标志位降序

2）SQL代码

SELECT   app_id,   MAX(cnt) max_uv FROM (   SELECT     appid,     SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC) cnt   FROM (     SELECT        app_id, in_time dt, 1 diff     FROM user_tables     WHERE appid != 0     UNION ALL     SELECT        app_id, out_time dt, -1 diff     FROM user_tables     WHERE appid != 0) t1  ) t2 GROUP BY 1 ORDER BY 2 DESC 

4 计算部门除去最高和最低的平均收入

题目介绍

部门员工基本信息表中记录部门id、员工id、收入字段，计算部门员工的平均收入，数据示意如下:

题目分析

使用窗口函数降序和升序分别排序取出最高和最低收入，计算平均收入。

解题方法

1）实现思路

• 使用开窗函数，分别根据收入组内正序倒序排序
• 计算平均收入

2）SQL代码

讯享网select a.dep_no，avg(a.salary) from    (  select *, rank() over( partition by dep_no order by salary ) as rk_1  , rank() over( partition by dep_no order by salary desc) as rk_2   from user_tables  )  a  group by a.dep_no where a.rk_1 >1 and a.rk_2 >1  

5 计算分类top3销售商品

题目介绍

商品浏览表记录：商品id、用户id字段，计算每个商品类别下最受欢迎的Top3产品，数据示意如下:

题目分析

这是一个非常常见计算前几名TopN的场景，一般先计算每个商品被每个用户浏览的次数，最后开窗计算商品浏览次数排序。

解题方法

1）实现思路

• 计算每个商品下每个用户的浏览次数，放在临时表
• 开窗函数计算每个商品被浏览次数排名
• 计算每个商品浏览前3的用户

2）SQL代码

select p_id, uid, cnt from (select     p_id,     uid,     cnt,     rank() over(partition by p_id order by cnt) rn   from (      select        p_id,        uid,        count(*) cnt     from user_tables     group by        p_id,        uid   )t1 ) t2 where rn<=3; 

本期为笔试系列初试牛刀，后续将持续更新更多SQL笔试真题，欢迎大家踊跃投稿和建议。