SQL笔试：短视频场景

取数所需的表

• 用户视频互动表 tb_user_video_log

注：uid-用户ID, video_id-视频ID, start_time-开始观看时间, end_time-结束观看时间, if_follow-是否关注, if_like-是否点赞, if_retweet-是否转发, comment_id-评论ID

• 短视频信息表 tb_video_info

注：video_id-视频ID, author-创作者ID, tag-类别标签, duration-视频时长（秒）, release_time-发布时间

1、计算2021年里有播放记录的每个视频的完播率(结果保留3位小数)，并按完播率降序排序

注：视频完播率=完成播放次数/总播放次数，结束观看时间与开始观看时间的差大于等于视频时长，视为完成播放

代码：

输出：

2、计算各类视频的平均播放进度，将进度大于60%的类别输出

注：播放进度=播放时长/视频时长*100%，当播放时长大于视频时长时，播放进度均记为100%，结果保留两位小数，并按播放进度倒序排序

代码：

输出：

3、统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）

注：转发率 转发量/播放量。结果按转发率降序排序

代码：

输出：

4、统计2021年里每个创作者每月的涨粉率及截止当月的总粉丝量

备注：涨粉率=(加粉量 - 掉粉量) / 播放量，结果按创作者ID、总粉丝量升序排序。

注：if_follow-是否关注为1表示用户观看视频中关注了视频创作者，为0表示此次互动前后关注状态未发生变化，为2表示本次观看过程中取消了关注

代码：

输出：

5、统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录

代码：

输出：

以上，希望对你有所帮助。

你好，我是Bottle！做过社交产品、写过品牌策划、也闹腾过用户增长，现在是一位上市公司的数据分析师，主要负责数据产品规划以及经营分析相关。

在这里写的文章类型不固定，主要是数据分析方法论、读书笔记及生活感悟等，希望能与你一起从零搭建数据分析师知识体系，见证彼此成长！

有问题留言，欢迎交流！

全平台同名：数分Bottle