SQL笔试题目解析：视频号数据分析组外包招聘

        在数据分析领域，SQL是进行数据查询和操作的核心技术。本次视频号数据分析组外包招聘的笔试题目，旨在考察应聘者对SQL的熟练程度以及解决实际问题的能力。以下是对笔试题目的详细解析和SQL代码示例。

题目概览

        本次笔试共有四个题目，涉及对视频数据的多维度分析，包括搞笑品类视频分析、视频内容分类、用户行为分析以及留存率计算。

 

本题的建表语句：

create table t_user_video_action_d(
    ds int,
    user_id string,
    video_id string,
    action_type int,
    `timestamp` bigint
)
row format delimited
fields terminated by ',';

create table t_video_d(
    ds int ,
    video_id string,
    video_type string,
    video_user_id string,
    video_create_time bigint,
    video_description string
)
row format delimited
fields terminated by ',';

create table t_video_user_d(
    ds int,
    video_user_id string,
    video_user_name string,
    video_user_type string

)
row format delimited
fields terminated by ',';

题目 1：

        输出一张搞笑品类 dws 表，用于分析作者近 3 条视频情况：输出每个视频创建者user_id 最近发布的 3 个视频，在过去一周内的曝光，点赞总数；需要产出字段：视频创建者 user_id，曝光用户数，点赞用户数，曝光次数、点赞次数。

本题求：搞笑品类视频分析

目标：

        分析每个视频创建者最近发布的3个视频在过去一周内的曝光和点赞情况。

解释：

• 使用窗口函数ROW_NUMBER()对每个视频创建者的视频按创建时间降序排列，以便选择每个创建者最近发布的3个视频。
• 筛选条件包括视频分类或视频创建者分类为“搞笑”。
• 聚合函数COUNT()和SUM()用于计算曝光用户数、点赞用户数、曝光次数和点赞次数。
• CASE语句用于区分不同的操作类型（曝光或点赞）。

select `current_date`();
select unix_timestamp();
select unix_timestamp()-7*24*3600;
select from_unixtime(1725326963+8*3600);
with t as (
   select uva.user_id,action_type,row_number() over (partition by uva.user_id order by video_create_time desc ) px from t_user_video_action_d uva join t_video_d  tvd on uva.user_id=tvd.video_user_id
            and uva.video_id = tvd.video_id  where tvd.video_type='搞笑' and video_create_time >= (unix_timestamp()-7*24*3600)
)
select
    user_id,
    count(if(t.action_type=1,t.user_id,null) ) `曝光用户数`,
    count(if(t.action_type=2,t.user_id,null) ) `点赞用户数`,
    count(if(t.action_type=1,1,null) ) `曝光次数`,
    count(if(t.action_type=1,1,null) ) `点赞次数`
    from t where t.px <= 3 group by user_id;

题目 2：

        输出一个取数结果：在 20221103 日创建的视频中，如果视频描述中带有"搞笑段子”，“脱口秀”两个关键词即为“搞笑类内容”，不带有这两个关键词为“非搞笑类内容”，最终输出 20221103 日搞笑与非搞笑视频去重数量。

本题求：视频内容分类

目标：

        统计20221103日创建的视频中，含有特定关键词的视频数量。

解释：

• 使用LIKE操作符筛选出视频描述中包含“搞笑段子”或“脱口秀”的视频。
• 使用COUNT(DISTINCT video_id)确保统计的是去重后的视频数量。

-- 假如你创建的是分区表，其实可以使用分区表字段进行判断  ds = '20221103'
select "搞笑类视频" `类别`,count(1) `数量` from t_video_d
         where video_description like '%搞笑段子%' or video_description like '%脱口秀%'
           and  from_unixtime(t_video_d.video_create_time+8*3600,'yyyyMMdd') ='20221103'
union
select "非搞笑类视频",count(1)  from t_video_d
         where video_description not like '%搞笑段子%' and  video_description not  like '%脱口秀%'
         and  from_unixtime(t_video_d.video_create_time+8*3600,'yyyyMMdd') ='20221103';

-- 第二种写法
select
    sum(case when video_description like '%搞笑段子%' or video_description like '%脱口秀%' then 1 else 0 end)  `搞笑视频数量`,
    sum(case when video_description not like '%搞笑段子%' and  video_description not  like '%脱口秀%' then 1 else 0 end) `非搞笑视频数量`
   from t_video_d where  from_unixtime(t_video_d.video_create_time+8*3600,'yyyyMMdd') ='20221103';

题目 3：

        计算每个用户每天第一次曝光视频的时间戳，运行速度越快越好，输出字段日期，用户 id，时间戳。

本题求：用户首次曝光视频时间戳

目标：

        计算每个用户每天第一次曝光视频的时间戳。

解释：

• 筛选出操作类型为曝光（action_type = 1）的记录。
• 使用MIN(timestamp)获取每个用户每天的最早曝光时间。

-- 一个人一天刷了很多抖音，他刷第一个抖音的时候是几点几分。
select user_id,ds,min(`timestamp`) from
     t_user_video_action_d where action_type = 1 group by user_id,ds;

题目 4：

        输出一张 dws 表，查询过去任意日期的曝光活跃用户的 7 日留存率，输出字段日
期，用户 id，7 日留存率。

留存率说明：

        留存率是衡量产品用户粘性和用户忠诚度的重要指标。例如，如果某天有100个新用户，7天后这些用户中还有30人活跃，则7日留存率为30%。这表明这些用户在首次使用产品后的7天内至少回来使用了一次。留存率的计算不要求用户活跃的天数是连续的。

留存用户：某段时间内的新增用户，经过一段时间后，又继续使用应用的被认作是留存用户。

留存率：留存用户占新增用户的比例即是留存率。

本题求：曝光活跃用户7日留存率

目标：

查询过去任意日期的曝光活跃用户的7日留存率。

解释：

• 留存率是指在一段时间内新增的用户，在经过一段时间后仍然活跃的比例。
• 首先，通过WITH子句创建一个临时表first_active，记录每个用户首次活跃的日期。
• 然后，计算每个用户在首次活跃后的7天内的活跃天数。
• 最后，计算留存率，即在7天内至少活跃1天的用户数除以总的新增用户数。

-- 先查看曝光的用户和日期
with t as (
    -- 9-10  zhangsan
    -- 9-10  lisi
    -- 9-17  zhangsan
    select ds,user_id  from t_user_video_action_d where action_type = 1 group by user_id,ds
)
-- 让7天前的数据 处于 当前数据 = 留存率  ds=20240910
, t2 as (
    select ds,user_id  from t where ds =
       date_add(from_unixtime(unix_timestamp("20240910",'yyyyMMdd'),'yyyy-MM-dd'),7)
)
select
    t.user_id,count(t2.user_id)/count(t.user_id) `七日留存率`
    from t left join t2 on t.user_id = t2.user_id group by t.user_id;

-- 如何将20240910 --> 2024-09-10
-- 字符串转时间戳
select unix_timestamp("20240910",'yyyyMMdd');
select from_unixtime(unix_timestamp("20240910",'yyyyMMdd'),'yyyy-MM-dd');
select date_add(to_date('2024-09-10'),7);

-- 拓展  通过拼接的方式将20240910 --> 2024-09-10
SELECT   
  concat(  
    substr('20240910', 1, 4),   
    '-',   
    lpad(substr('20240910', 5, 2), 2, '0'),   
    '-',   
    lpad(substr('20240910', 7, 2), 2, '0')  
  ) as formatted_date;