分享-数据分析社招面试题-sql题汇总

[假装有目录][TOC]

引言

本人惭愧,在国企工作接近4年了,基本待废了,才开始认真的考虑跳槽找工作的事情,刚工作时没有考虑清楚想做什么,稀里糊涂做了数据运营,内心抗拒,但四顾茫然,浑浑噩噩,抬头发现已沧海桑田,唯有从头开始,及时止损,才能不辜负韶华和生命的意义。在牛客学习到的知识,都分享给各位,希望能对大家有些许微博的帮助。
本帖是为了汇总牛客网站上的数据分析的面试题,主要是社招方向的,难度稍微大一点,不会讲太多的基础知识,尽量以干活为主吧。

数据平台:hadoop+hive/spark
数据语言:HQL(非msql,语法结构大体相同)

1. 连续7日用户提取

泰科电子数据分析工程师社招面经
里面讲到一道题,是提取多少时间内的连续X天用户的问题,此类问题的核心解法都是:先对每天的用户去重(distinct day,user_id),然后开窗排序(row_numer()over(partition by user_id order by day) rn),最关键的一点来了,是用日期减去排序的序号(day-rn),如果是连续的日期,那么序号必然也是按顺序+1的。自然(day-rn)的值是一样的(相当于将用户的连续访问的时间段做了一个标记,一个时间段用一个数值来代表)。最后再统计一下每个用户差值的计数数量,就能筛选出大于X天的用户了。

day rn
20210301 1
20210302 2
20210303 3
20210305 4
20210306 5

#上面的例子里,3月1号-3月3号都是连续的,(day-rn)的差值都是20210300,计数项为3,3月4号-3月5号的(day-rn)的差值都是20210302,计数项为2

因此,参考代码为:

#求30天内的连续7天访问用户
select id,count(*) 
from 
    (select *,day-rn as result 
    from
        (select *,row_number() over(PARTITION by user_id order by day ) as rn
         from
             (select DISTINCT day ,user_id
                from tb_visit)a
               )b
    )c
GROUP BY user_id,result 
having count(*)>=7

问题扩展:
感谢@牛客800931114号 的提问:
当统计的时间范围大于1个月时,上述代码确实会遇到一个问题,就是跨月时间的连续访问判定问题。如果统计的范围很大,超过一个月,需要将日期转换为时间戳格式进行运算才能保证结果的正确性,因此更为严谨的完整版为:

select id,count(*) 
from 
    (select *,day-rn*60*60*24 as result 
    from
        (select *, to_unix_timestamp(day,'yyyy-MM-dd') day_stamp,
         row_number() over(PARTITION by user_id order by day ) as rn
         from
             (select DISTINCT day_stamp day ,user_id
                from tb_visit)a
               )b
    )c
GROUP BY user_id,result 
having count(*)>=7

当数据为如下格式时:

day rn
20210331 1
20210401 2

#通过 to_unix_timestamp('2021-03-31','yyyy-MM-dd') 将日期转换为unix时间戳:1617120000,然后多一天,就相当于多了86400秒,通过day-rn*60*60*24来保证连续时间的结果一致

顺便提一句,mysql8.0版本已经支持了诸如 row_number(),rank()等各种开窗函数,感觉已经将hive SQL中亮眼的功能都实现了,优秀了

2. 每日新增用户的次日留存率

感谢评论区网友的分享,让我找到了新的思路,真是惭愧啊,就算天天接触到指标统计,用的一直是笨的办法,如果后续大家有这方面的问题也可以留言,我慢慢补充这个帖子的问题和答案。

我都是用土办法先把每天的新增用户捞出来,再从每天的用户表里去匹配前一日的新增用户来算次日留存,评论区老兄的办法很好,先给每条记录打上一个最早访问时间的标签,再以该时间作为聚合字段,也就是将一个月内用户的所有活跃数据,都放到了第一次访问时间对应的块中,进行统计,然后在块中判断用户是否有相较于第一次访问时间的X天的记录,有就计数一次,没有就0次。

这个方法高明的地方就在于,用户是按第一次访问时间分类的,直接按天找到了新增用户,然后通过遍历一遍用户的访问数据,计算与第一次访问时间的时间差,就能知道是否有次日留存,而不需要多次关联变筛选数据后统计结果,虽然性能上不一定最优,但是解法思路上很高级,只不过要注意的是,遍历的是时候一定要先将用户的单条访问记录按天先汇总一下,不然遍历计算次日留存的时候,分母是去重用户数,分子就变成单条访问记录数了,结果完全不对。

with tmp as (
  select user_key,dt, min(dt) over(partition by user_key) as fst_log from  Activity
  where dt between 20210301 and 20210331 --假设为月表
  group by user_key,dt
)

select fst_log as install_dt, count(distinct user_key) as installs,
    round(sum(if((dt-fst_log)=1,1,0))/count(distinct user_key),2) as new_1Day_retention
from tmp 
group by fst_log

我跑了下某app的数据,每日新增是150w,次日留存是8-12%,当然大家可以吧判断语句的数值改下,就可以延伸为每日新增用户的X日留存率(如3日,5日,7日)。

3. 牛客练习题-订单分析5

问题链接:https://www.nowcoder.com/practice/348afda488554ceb922efd2f3effc427?tpId=82&tqId=37919&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey

题目看着有点绕,我总结一下,就是先从2025-10-15这个时间段后,选择一批用户的结果集(订购成功且不少于两次,课程为限定为JAVA、Python、和C++),将这些用户的第一次订购时间、第二次订购时间、以及订购成功的次数都展示出来,看代码顺理思路:

--先将结果集target_user 搞出来,但是我在这一步并没有对订单次数做过滤
with target_user as (
select  user_id,date,count(1) over(partition by user_id) user_cnt,
    row_number() over(partition by user_id order by date asc )rn
    from order_info
    where unix_timestamp(date) >unix_timestamp('2025-10-15')
    and product_name in ('C++','Python','Java')
    and status='completed'
)--接下来搞了两个子集,第一次订购结果集和第二次订购结果集
  ,first_buy as (
select user_id,date first_buy_date,user_cnt
    from target_user
    where user_cnt>=2
    and rn=1
)  ,second_buy as (
select user_id,date second_buy_date,user_cnt
    from target_user
    where user_cnt>=2
    and rn=2
)
select f.user_id,f.first_buy_date,s.second_buy_date,f.user_cnt
from first_buy f left join second_buy s on f.user_id=s.user_id
order by f.user_id

代码虽然长,但是逻辑还算清晰,后续维护和编辑也简单,但是中间搞了两个结果集确实有点不好看,于是我看到了别人的版本,对上述代码进行优化:

--先将结果集target_user 搞出来,但是我在这一步并没有对订单次数做过滤
with target_user as (
select  user_id,date,count(1) over(partition by user_id) user_cnt,
    row_number() over(partition by user_id order by date asc )rn
    from order_info
    where unix_timestamp(date) >unix_timestamp('2025-10-15')
    and product_name in ('C++','Python','Java')
    and status='completed'
)
--只筛选出两个记录,分别用最大最小时间来分开第一次和第二次记录,如果再多来一次就又要开窗来排序了
select t.user_id,
min(t.date ) first_buy_date,
max(t.date ) second_buy_date,
t.user_cnt
from target_user t 
where t.user_cnt>=2
and t.rn<=2
group by t.user_id,t.user_cnt--第一次提交的时候没有加group by,导致只出现了一条数据,没有加的话,min和max都取的是所有记录的最大值最小值,并且只显示一条记录,差点没看出来
order by t.user_id

3. 牛客练习题-考试分数(5)

问题链接:https://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512?tpId=82&tqId=35496&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking&tab=answerKey

此问题最大的难点在于中位数的定义:

  1. 当元素的总个数是偶数时(如N=4),MOD(N/2)=0,中位数选取的时候,将总数+1再除2,即5/2=2.5,要取靠近2.5的两边的两个数,即取第二个和第三个
  2. 当元素的总个数是奇数时(如N=3),MOD(N/2)=1,中位数要取2,即取第二个即可

解法如下:

#先分类别排序,排序的序号也方便后续拿出来,后取每个类别中序号最大的为元素个数做运算
select re.id,re.job,re.score,re.rn
from (select t.*,
     max(rn) over(partition by t.job ) max_rn
    from (select g.*,
        row_number() over(partition by g.job order by g.score desc)rn
        from grade g)t
    ) re
#     where (rn=ceil((re.max_rn+1)/2) or rn=floor((re.max_rn+1)/2))
    where abs(rn-(re.max_rn+1)/2) <1
    order by re.id

where条件有两个方式过滤;

  1. 方案1:(re.max_rn+1)/2的表达式下,元素个数是偶数,则ceil()和floor()计算的数据是一样的(N为奇数,选一个就行),选的都是同一个;元素个数是奇数,则ceil()和floor()计算的数据是不一样的(N为偶数,or的条件会把两个中位数都选出来),选出来的结果有两个
  2. 方案2:(rn-(re.max_rn+1)/2)的结果与上面一致,用了另外一个方式实现,大家稍微想想应该能明白
#面经##社招##数据分析工程师##数据分析师#
全部评论
m一下
1 回复
分享
发布于 2021-04-08 23:43
请问可以分享一下如何写每日新增用户的次日留存吗?
点赞 回复
分享
发布于 2021-04-02 23:13
小红书
校招火热招聘中
官网直投
连续7天访问的用户那里有点问题,应该用date_sub函数和dense_rank。如果直接减去row_number的话,对于跨月连续登录的用户会出问题。比如说0330,0331,0401这三天按照日期是连续登录的,但是直接减去row_number就变成0329,0329,0400,再通过聚合判断就只有两天是连续的了。
点赞 回复
分享
发布于 2021-04-06 09:12
次日留存的tmp里面dt不应该加between吧?因为dt不是每日新增人数中对应的每日,min(dt)才是。所以我觉得要像判断日期a到日期b中每日新增人数的次留,要在下面一段SQL中group by fst_log之前加个where fst_log between 日期a and 日期b
点赞 回复
分享
发布于 2021-04-06 09:23
m一下
点赞 回复
分享
发布于 2021-04-07 17:00
m
点赞 回复
分享
发布于 2021-05-09 22:37
楼主大大, 第二道题 group by user_key,dt 后,在 user_key,dt  后面min(dt),这个计算出来的min(dt)不就等于dt吗,因为它是在每一个不同的user_key + dt分组计算啊,并不是仅仅在不同的user_key  里面计算,求解释一下,谢谢
点赞 回复
分享
发布于 2021-05-12 22:17
点赞 回复
分享
发布于 2021-05-13 07:54

相关推荐

15 187 评论
分享
牛客网
牛客企业服务