[假装有目录][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)的结果与上面一致，用了另外一个方式实现，大家稍微想想应该能明白