视频号数据分析组外包招聘笔试题（45分钟）

题目：根据 3 张表表结构，写出具体求解的 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_creat_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，曝光用户数，点赞用户数，曝光次数、点赞次数

with t as (
    select tvd.video_user_id,action_type,uva.user_id,
       dense_rank() over (partition by tvd.video_user_id order by tvd.video_creat_time desc ) pm
       from t_user_video_action_d uva
       join t_video_d tvd on uva.video_id=tvd.video_id
       join t_video_user_d tvud on tvd.video_user_id = tvud.video_user_id
       where (action_type='搞笑' or video_user_type='搞笑') and uva.`timestamp`>=(unix_timestamp()-7*24*3600)
) select t.video_user_id,
         count(distinct if(action_type=1,user_id,null)) `曝光用户数`,
         count(distinct if(action_type=2,user_id,null)) `点赞用户数`,
         count(if(action_type=1,1,null)) `曝光次数`,
         count(if(action_type=2,1,null)) `点赞次数`
         from t where pm<=3 group by t.video_user_id;

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

with t as(
   select video_id,
          if(video_description like '%搞笑段子%' or video_description like '%脱口秀%','搞笑','非搞笑') lx
          from t_video_d where video_creat_time=20221103
)select lx,count( distinct video_id) from t group by lx;

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

 select user_id,
         ds,
         min(`timestamp`)
         from t_user_video_action_d where action_type=1 group by user_id,ds;