【Kay】某音SQL题目

 1.各个视频平均完播率

计算有播放记录的视频的完播率(结果保留三位小数)，并按完播率降序排序

SELECT u.video_id,
       ROUND(SUM(IF(TIMESTAMPDIFF(SECOND,start_time,end_time) >= duration,1,0))/COUNT(start_time),3) AS avg_comp_play_rate
FROM tb_user_video_log u
INNER JOIN tb_video_info v
ON u.video_id = v.video_id
GROUP BY u.video_id
ORDER BY avg_comp_play_rate DESC

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

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

SELECT tag,
    CONCAT(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 u
INNER JOIN tb_video_info v
ON u.video_id = v.video_id
GROUP BY tag
HAVING AVG(IF(TIMESTAMPDIFF(SECOND,start_time,end_time)>duration,1,
              TIMESTAMPDIFF(SECOND,start_time,end_time)/duration)) > 0.6
ORDER BY avg_play_progress DESC

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

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

输出试例：

筛选近30天内的代码

WHERE TIMESTAMPDIFF(DAY,date(start_time),date((select max(start_time) from tb_user_video_log))) <= 29

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

计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 （假设粉丝量从0开始）

select author, month,
round(add_fans/counts,3) fans_growth_rate, 
sum(add_fans) over (partition by author order by month) total_fans
from (
    
    select author, 
     DATE_FORMAT(start_time,'%Y-%m') month, 
     sum(case when if_follow=2 then -1 else if_follow end) add_fans,
     count(start_time) counts
     from tb_user_video_log t1
     join tb_video_info t2 
     on t1.video_id=t2.video_id
     where year(start_time)=2021
     group by author, month) a

ORDER BY author, total_fans

5.国庆期间每类视频点赞量和转发量

 统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序

select tag,dt,like_cnt,retweet_cnt
FROM
(select tag,day0 dt,
    sum(like1) over(partition by tag order by day0  ROWS between 6 preceding and current row) like_cnt,
    max(retweet1) over(partition by tag order by day0  ROWS between 6 preceding and current row) retweet_cnt
from(
    SELECT tag,
        date_format(start_time,'%Y-%m-%d') day0,
        sum(if_like) like1,
        sum(if_retweet) retweet1
    
    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 tag,day0) lchid
group BY tag,day0) child

where dt between '2021-10-01' and '2021-10-03'
group BY tag,dt
order by tag desc,dt 

over  ( order   by   month    rows between 1 preceding and 3 following ) 

1行前，3行后

over  ( order   by   month    rows between 6 preceding and current row ) 

6行前，到当前行