sql面试题：有关视频点赞、曝光情况的查询

1. 目录

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

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

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

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

   拓展：如何将字符串转为时间类型（20240910-->2024-09-10）

   ---

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

with t as (
    select user_id,action_type,row_number() over (partition by user_id order by video_create_time desc) px from t_user_video_action_d tva,t_video_d tvd where tva.user_id=tvd.video_user_id
      and tva.video_id=tvd.video_id and tvd.video_type='搞笑' and video_create_time >= to_date(date_format(date_sub(`current_date`(),7),'yyyyMMdd'))
)
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=2,1,null)) `点赞次数`
       from t where t.px <=3 group by user_id;

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

第一种写法：

select "搞笑类视频" `类别` ,count(*) `数量` 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(*) 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';

第二种写法：

select sum(case when video_description like '%搞笑段子%' or video_description like '%脱口秀%' then 1 else 0 end) `搞笑类视频`,
       sum(case when video_description not like '%搞笑段子%' or 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，时间戳

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 日留存率先查看曝光的用户和日期

with t as (
    select ds,user_id  from t_user_video_action_d where action_type = 1 group by user_id,ds
)
, 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');

再用函数from_unixtime() 将时间戳转换为我们需要的日期类型

select from_unixtime(unix_timestamp("20240910",'yyyyMMdd'),'yyyy-MM-dd');

方案二：通过拼接的方法将20240910-->2024-09-10，然后用函数to_date()将字符串转为日期类型

select to_date(concat(substr('20240910',1,4),'-',lpad(substr('20240910',5,2),2,'0'),'-',lpad(substr('20240910',7,2),2,'0')));