SQL笔试题（5）：某音春招数据分析岗真题详解

分析师小帅比

数据求职教练, ds_offer_master 免费个人评估

今天来解析某音数分岗几道sql笔试题。我们将从考点、业务背景及涉及到的知识点三方面入手，对题目进行拆解并延伸。

相关文章： 小帅比：SQL笔试题（6）：某团数分岗笔试真题详解

题目（1）

有用户表行为记录表t_act_records表，包含两个字段：uid（用户ID），imp_date（日期）

1. 计算2020年每个月，每个用户连续签到的最多天数

2. 计算2020年每个月，连续2天都有登陆的用户名单

3. 计算2020年每个月，连续5天都有登陆的用户数

难度： ★★★★★

<1> 计算2020年每个月，每个用户连续签到的最多天数

考点：1. 连续时间问题；2. 时间限定；3. 聚类

第一步：从时间上限定出2020年数据

where imp_date between 20200101 and 20201231

第二步：解决连续时间问题

排序：row_number() over (partition by month(imp_date), uid) as rank
相减：date_diff(imp_date, rank) as sign

第三步：按月聚类求出最大连续签到天数

组装构成答案

select month
    ,uid
    ,max(cnt) 
from (
        select month(imp_date) as month
            ,imp_date
            ,uid
            ,date_sub(imp_date, rank) as sign
            ,count(1) as cnt
        from(
                select uid
                    ,imp_date
                    ,row_number() over (partition by month(imp_date), uid order by imp_date) as rank 
                from t_act_records
                where imp_date between 20200101 and 20201231
            group by month(imp_date)
                ,imp_date
                ,uid
                ,date_sub(imp_date, rank)
group by month
    ,uid

<2> 计算2020年每个月，连续2天都有登陆的用户名单

考点：1. 连续时间问题；2. 时间限定；3. 聚类

不同点：与上题考点相似，唯一不同点为要求连续两天都有登陆

count(diff)>=2

组装构成答案

select month(imp_date) as month
    ,uid
from ( 
        select uid
            ,imp_date
            ,date_sub(imp_date, rank) as diff
        from(
                select uid
                    ,imp_date
                    ,row_number() over (partition by month(imp_date), uid) as rank 
                from t_act_records
                where imp_date between 20200101 and 20201231
group by month(imp_date)
    ,uid
having count(diff)>=2;

<3> 计算2020年每个月，连续5天都有登陆的用户数

考点：1. 连续时间问题；2. 时间限定；3. 聚类

不同点：与上个考点基本相似，不同点为：1. 求用户数，2. 连续登陆5天

count(distinct uid)
count(diff)>=5

组装构成答案

select month(imp_date) as month
    ,count(distinct uid) as num
from ( 
        select uid
            ,imp_date
            ,date_sub(imp_date, rank) as diff
        from(
                select uid
                    ,imp_date
                    ,row_number() over (partition by month(imp_date), uid) as rank 
                from t_act_records
                where imp_date between 20200101 and 20201231
group by month(imp_date)
having count(diff)>=5;

此题最主要的考点为 对于连续时间 理解的考察，关于这部分内容我们在以前的开篇中就有过详细的讲解：小帅比：SQL笔试题（1）：求连续时间问题（必考难题）

再重复一下要点：

按要求排序：row_number() over (partition by xxx order by) as rank
求时间差：date_sub(date, rank) as diff
根据题目要求对diff进行限制

题目（2）

有课程销售订单表t_order_records表，包含四个字段：uid（用户ID），order_time（日期），order_id（订单ID），subject（学科）

求出每个用户第一个订单的记录，如果同时下单了包含多个课程的订单，则按照：语文、数学、英语顺序排序
统计每天的订单量和截止到当天的订单量

难度： ★★★☆☆

<1> 求出每个用户第一个订单的记录，如果同时下单了包含多个课程的订单，则按照：语文、数学、英语顺序排序

考点：1. 最早时间；2. 有条件排序

第一步：聚类，限制最早时间

min(order_time) group by uid, order_id, subject

第二步：有条件排序

order by FIELD('subject', '语文', '数学', '英语')

组装构成答案

select uid
    ,subject 
    ,min(order_time) as time
from t_order_records
group by uid
    ,subject 
order by FIELD('subject', '语文', '数学', '英语')

<2> 统计每天的订单量和截止到当天的订单量

考点：1. 分天聚合；2. 累积求和；3. 拼接

第一步：分天聚合

count(order_id) group by order_time

第二步：累积求和

sum(count(order_id)) over (partition by order_time) as count_sum

第三步：拼接

select * from A left join B on ()

组装构成答案

select a.order_time as date
    ,count_today
    ,count_sum
from(
        select order_time 
            ,count(order_id) as count_today
        from order
        group by order_time
left join
    SELECT order_time
      ,SUM(count(order_id)) OVER (PARTITION BY order_time) as count_sum
    from order 
) b on (a.order_time = b.order_time)

本题总体难度不大，但是第一部分考察了一个不是很常用的排序函数，第二部分考察了拼接和汇总排序两个知识点，拉高了难度。

碰到此类题，尤其是第二部分，先一个一个字段解决，再最终考虑将数据拼接在一起。

题目（3）

学生成绩表t_student_score表，包含四个字段：class_id（班级ID），student_id（学生ID），course_time（课程ID），score（成绩）

求出每个学生成绩最高的三条记录
求出每个班，每个课程，高于课程平均分的学生

难度： ★★★☆☆

<1> 求出每个学生成绩最高的三条记录

考点：排序取前三

select class_id
    ,student_id
    ,course_id 
    ,score 
from (
        select student_id
            ,course_id 
            ,score 
            ,dense_rank() over (partition by class_id,student_id order by score) as rank
        from t_student_score
where rank<=3

<2> 求出每个班，每个课程，高于课程平均分的学生

考点：1. 排序取前三；2. 匹配；3. 筛选

第一步：增加一列（各学科平均值）

avg(score) as avg_score group by class_id, student_id, course_id

第二步：拼接

left join on ()

第三步：筛选

where score > avg_score

组装构成答案

select class_id
    ,student_id
    ,course_id
    ,score
from (
        select a.class_id as class_id
            ,a.student_id as student_id
            ,a.course_id as course_id
            ,score
            ,avg_score
            ,case when score>avg_score then 1 else 0 end as tag
        from t_student_score a
        left join(
                    select class_id
                        ,student_id