2024-12 快手 中等
 需求  表结构  输出示例  参考答案  讨论  举一反三

定义一个互动指数为点赞 (if_like) + 评论 (comment_id IS NOT NULL) + 转发 (if_retweet) + 收藏 (if_fav) 的总和。

编写一个查询来统计每个作者发布的视频的平均互动指数，并按照平均互动指数降序排列。

短视频指标体系 聚合函数 多表连接 数学运算符 group by inner join case when distinct count round + / null not

ks_video_inf，视频信息表

字段名 数据类型 描述
video_id VARCHAR(20) 视频的唯一标识符
author_id VARCHAR(20) 创建该视频的用户的唯一标识符
title TEXT 视频的标题
tag TEXT 视频的标签，多个标签之间用空格分隔
screen_type ENUM('p', 'l') 屏幕方向：'p' 表示竖屏（portrait），'l' 表示横屏（landscape）
if_AI_talking BOOLEAN 是否AI配音：1 表示是，0 表示否
if_hint BOOLEAN 是否有字幕：1 表示有字幕，0 表示无字幕
duration INT 视频时长，单位为秒
release_time DATETIME 视频发布的时间
release_loc VARCHAR(50) 视频发布的地点

示例数据如下：

video_id author_id title tag screen_type if_AI_talking if_hint duration release_time release_loc
v1001 a1012 是金子迟早会发光，小红升职就是的例子 红楼梦 87版红楼梦 王熙凤 p 1 1 187 2024-10-01 23:23:00 北京
v1002 a2021 制造业为何利润低 认知 经济 制造业 p 0 1 324 2024-10-08 23:34:00 北京

 

ks_video_wat_log，用户视频播放日志表

字段名 数据类型 描述
uid VARCHAR(20) 用户的唯一标识符
video_id VARCHAR(20) 视频的唯一标识符
start_time DATETIME 用户开始观看视频的时间
end_time DATETIME 用户结束观看视频的时间
if_follow BOOLEAN 用户是否关注了视频作者：1 表示是，0 表示否
if_like BOOLEAN 用户是否点赞了视频：1 表示是，0 表示否
if_retweet BOOLEAN 用户是否转发了视频：1 表示是，0 表示否
if_fav BOOLEAN 用户是否收藏了视频：1 表示是，0 表示否
if_comment BOOLEAN 用户是否评论了视频：1 表示是，0 表示否
click_from ENUM('1', '2', '3') 点击来源：1 - 信息流推荐列表；2 - 朋友转发；3 - 评论区视频链接

示例数据如下：

uid video_id start_time end_time if_follow if_like if_retweet if_fav comment_id click_from
u000157 v1002 2024-11-26 17:54:00 2024-11-26 18:04:03 1 1 1 1 c0203 1
u000157 v1002 2024-11-26 21:04:00 2024-11-26 21:07:56 1 0 0 1 null 3
u000157 v1002 2024-11-26 21:44:00 2024-11-26 21:47:57 1 1 1 1 null 2
u000224 v1002 2024-11-27 11:17:00 2024-11-27 11:22:26 1 1 1 1 c20342 1

 成为会员，解锁300道大厂数分SQL真题

ID124 餐厅的用户复购率  
中等 group by left join case when distinct count round > CTE或SubQuery 客户订单分析 美团
ID203 小结-从不缺考的学生  
入门 group by case when count having null 经典-学生表课程表 小猿搜题
ID227 快手面试真题（4）按购买金额统计用户数  
中等 group by inner join between and case when distinct round date_format 时间日期 聚合函数 百分比计算 CTE或SubQuery 客户订单分析 2025春招面试真题 快手
ID255 用户听歌多元化标签  
简单 group by inner join case when distinct count 聚合函数 多表连接 音乐偏好分析 QQ音乐
ID265 非港台歌手的专辑数量  
简单 inner join count in not 聚合函数 音乐偏好分析 QQ音乐

MySQL 8.0

00:00