2021年11月每天新用户的次日留存率 明确题意： 统计2021年11月每天新用户的次日留存率（保留2位小数），次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。 如果in_time和out_time跨天了，在两天里都记为该用户活跃过，结果按日期升序。 问题分解： 计算用户活跃日期集合，分别考虑进入和离开时间（可能跨天）： WITH t_uid_dt as ( SELECT distinct uid, DATE(in_time) as dt FROM tb_user_log UNION SELECT distinct uid, DATE(out_time)...

每篇文章同一时刻最大在看人数 明确题意： 统计每篇文章同一时刻最大在看人数，如果同一时刻有进入也有离开时，先记录用户数增加再记录减少，结果按最大人数降序 问题分解： 计算每篇文章当前时刻的在看人数（当前已进入-当前已离开=当前在看）： 跟踪每一时刻的人数变动： 每条记录的进入时间表示进入人数多了1：SELECT artical_id, in_time as at_time, 1 as uv FROM tb_user_log 每条记录的离开时间表示离开人数多了1：SELECT artical_id, out_time as at_time, -1 as uv FROM tb_user_l...

2021年11月每天的人均浏览文章时长 明确题意： 统计2021年11月每天的人均浏览文章时长（秒数），结果保留1位小数，并按时长由短到长排序 问题分解： 计算每次文章浏览的时长和日期： 过滤目标时间窗的有效浏览记录：WHERE artical_id != 0 AND DATE_FORMAT(in_time, "%Y%m") = "202111" 将进入时间转化为日期：DATE(in_time) as dt 计算浏览时长：TIMESTAMPDIFF(SECOND, in_time, out_time) as view_len_sec 按日期分组：GROUP BY dt 计算人均时长（...

近一个月发布的视频中热度最高的top3视频 明确题意： 请找出近一个月发布的视频中热度最高的top3视频。 假设热度计算公式简化为：热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；新鲜度=1/(最近无播放天数+1)； 结果中热度保留为整数，并按热度降序排序。 问题分解： 计算每个视频的各项指标： 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)； 在每一行上追加当前日期列： LEFT JOIN ( SELECT MAX(DATE(end_time)) as cur_date FRO...

国庆期间每类视频点赞量和转发量 明确题意： 计算2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序 问题分解： 计算每类视频每天的点赞量和转发量： 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)； 筛选必要时间窗的记录（因为最终只需要2021国庆头三天的近7日量，所以只需要这三天+前6天的数据即可，提前过滤提高效率）：WHERE DATE(start_time) between '2021-09-25' and '2021-10-03' 按视频类别和日期分组：GROUP BY...

每个创作者每月的涨粉率及截止当前的总粉丝量 明确题意： 计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量，涨粉率=(加粉量 - 掉粉量) / 播放量。结果按创作者ID、总粉丝量升序排序 问题分解： 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)； 筛选2021年的记录：where YEAR(start_time) = 2021 计算每个创作者每个月的涨粉量和播放量： 按创作者和月份分组：group by author, DATE_FORMAT(start_time, "%Y-%m") 计算涨粉量（特殊情况if_foll...

每类视频近一个月的转发量/率 明确题意： 计算每类视频在有用户互动的最近一个月（按包含当天在内的近30天算）中的转发量和转发率。结果按转发率降序排序 问题分解： 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)； 筛选最近30天的记录： 找到今天的日期：MAX(DATE(start_time) 往过去推移30天：DATE_SUB(MAX(DATE(start_time)), INTERVAL 30 DAY) 筛选最近的：WHERE DATE(start_time) > (SELECT DATE_SUB(MAX(DATE(st...

平均播放进度大于60%的视频类别 明确题意： 计算各类视频的平均播放进度，将进度大于60%的类别输出 问题分解： 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)； 按视频类别分组：GROUP BY tag 计算每个类别的平均播放进度： 播放进度=播放时长÷视频时长*100% 播放时长=TIMESTAMPDIFF(SECOND, start_time, end_time)；特殊情况：播放时长大于视频时长时，播放进度为100%（加个IF判断） 平均进度=AVG(每个进度) 结果保留2位小数：ROUND(x, 2) 百分比格式化：CO...

各个视频的平均完播率 明确题意： 计算2021年里有播放记录的每个视频的完播率，并按完播率降序排序 问题分解： 关联用户-视频互动记录和短视频信息表：JOIN tb_video_info USING(video_id)； 筛选2021年的记录：WHERE YEAR(start_time)=2021 按视频id分组：GROUP BY video_id 计算每条播放记录是否完成播放：IF(TIMESTAMPDIFF(SECOND, start_time, end_time)>=duration, 1, 0) 计算完播率，完成播放为1，未完成播放为0，取平均即为完播率AVG() 保留3位小...

明确考点： 索引创建、删除与使用： 1.1 create方式创建索引： CREATE [UNIQUE -- 唯一索引 | FULLTEXT -- 全文索引 ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引 (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引 1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content) ...

明确考点： 索引创建、删除与使用： 1.1 create方式创建索引： CREATE [UNIQUE -- 唯一索引 | FULLTEXT -- 全文索引 ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引 (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引 1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content) ...

明确考点： 表的创建、修改与删除： 1.1 直接创建表： CREATE TABLE [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过 (column_name1 data_type1 -- 列名和类型必选 [ PRIMARY KEY -- 可选的约束，主键 | FOREIGN KEY -- 外键，引用其他表的键值 | AUTO_INCREMENT -- 自增ID | COMMENT comment -- 列注释（评论） | DEFAULT default_value -- 默认值 | UNIQUE -- 唯一性约束，不允许两条...

明确考点： 表的创建、修改与删除： 1.1 直接创建表： CREATE TABLE [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过 (column_name1 data_type1 -- 列名和类型必选 [ PRIMARY KEY -- 可选的约束，主键 | FOREIGN KEY -- 外键，引用其他表的键值 | AUTO_INCREMENT -- 自增ID | COMMENT comment -- 列注释（评论） | DEFAULT default_value -- 默认值 | UNIQUE -- 唯一性约束，不允许两条...

明确考点： 表的创建、修改与删除： 1.1 直接创建表： CREATE TABLE [IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过 (column_name1 data_type1 -- 列名和类型必选 [ PRIMARY KEY -- 可选的约束，主键 | FOREIGN KEY -- 外键，引用其他表的键值 | AUTO_INCREMENT -- 自增ID | COMMENT comment -- 列注释（评论） | DEFAULT default_value -- 默认值 | UNIQUE -- 唯一性约束，不允许两条...