mysql牛客刷题（SQL大厂面试真题）

某音短视频

SQL1 各个视频的平均完播率

先理解题目意思：有播放记录的视频的id号和其完播率。条件是：2021年和根据其完播率降序排序。
拆解复杂指标：这里需要拆解的是完播率这个指标 

注意：记得加上题目的限制条件

SELECT a.video_id ,
	   round(sum(if(end_time - start_time >= duration, 1, 0))/count(start_time ),3) as avg_comp_play_rate
FROM tb_user_video_log a
LEFT JOIN tb_video_info b
on a.video_id = b. video_id
WHERE year(start_time) = 2021
GROUP BY a.video_id 
ORDER BY avg_comp_play_rate DESC;

SQL2 平均播放进度大于60%的视频类别

平均播放进度大于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)
百分比格式化：CONCAT(x, ‘%’)
计算公式：
ROUND(AVG(
IF(TIMESTAMPDIFF(SECOND, start_time, end_time) > duration, 1,
TIMESTAMPDIFF(SECOND, start_time, end_time) / duration)
) * 100, 2) as avg_play_progress
筛选播放进度>60%的视频类别：HAVING avg_play_progress > 60
细节问题：
表头重命名：as
按播放进度倒序排序：ORDER BY avg_play_progress DESC;

select tag,concat(avg_play_progress,"%") as avg_play_progress
from(
select tag,
round(avg(if(timestampdiff(second,start_time,end_time)>duration,1,
            timestampdiff(second,start_time,end_time)/duration))*100,
     2) as avg_play_progress
from tb_user_video_log
    join tb_video_info using(video_id)
    group by tag
    having avg_play_progress>60
    order by avg_play_progress desc
)as t_progress

SQL3 每类视频近一个月的转发量/率

首先是题意，视频播放日期start_time和整体的日期最大值之差小于30的，叫做“视频在有用户互动的最近一个月”。 第二，mark一下，上面要写成(SELECT MAX(start_time) FROM tb_user_video_log)，而不能直接写MAX(start_time)。where后面不能跟集函数。顺便说一句，having后面倒是常跟集函数。

1.有交互的视频的最近一个月（就是有播放量的视频的最大日期 减去 30天）
2.每类视频在有用户互动的最近一个月（并不是对于各类视频计算最大值，而是整体的日期最大值）

select tag,sum(if_retweet),round(sum(if_retweet)/count(*),3) retweet_rate
from tb_user_video_log a
left join tb_video_info b
on a.video_id =b.video_id
where timestampdiff(day,start_time,(select max(start_time) from tb_user_video_log))<30
group by tag
order by retweet_rate desc

SQL4 每个创作者每月的涨粉率及截止当前的总粉丝量

第一个需要解决的地方是每个月状态2相当于掉粉，状态1相当于涨粉，这里不能用if去做，因为有多个状态，所以用case when去处理。

第二个需要解决的就是，我们需要计算每个月截止当前的粉丝量，这里自然地想到用窗口函数去处理即可，这里窗口函数需要去partition by author如果这样处理的话，如果有多个作者就会混乱。

SELECT 
    author, 
    date_format(start_time,'%Y-%m') month,
    round(sum(case when if_follow=1 then 1
             when if_follow=2