SQL大厂面试真题

SQL1 各个视频的平均完播率

select video_id,round(sum(if_over)/count(if_over),3) as rate
from (
    select video_id,if(diff>=duration,1,0) as if_over
    from
    (
        select 
            t1.video_id,timestampdiff(second,start_time,end_time) as diff,duration 
        from tb_user_video_log t1
        left join tb_video_info t2 on t1.video_id = t2.video_id
        where year(start_time) = 2021
    ) t 
) t 
group by video_id
order by rate desc

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

select 
    tag,
    concat(round((round(sum(player_rate)/count(player_rate),4)*100),2),'%') as rate
from (
    select tag,if(diff/duration>1,1,diff/duration) as player_rate
    from
    (
        select
            t1.video_id,t2.tag,timestampdiff(second,start_time,end_time) as diff,duration
        from tb_user_video_log t1
        left join tb_video_info t2 on t1.video_id = t2.video_id
    ) t
) t 
group by tag
having round(sum(player_rate)/count(player_rate),2) >0.6
order by rate desc

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

select tag,sum(if_retweet),round(sum(if_retweet)/count(if_retweet),3) as rate
from tb_user_video_log t1
left join tb_video_info t2
on t1.video_id = t2.video_id
where datediff(date((select max(start_time) FROM tb_user_video_log)), date(t1.start_time)) < 30
group by tag
order by rate desc

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

select 
    author,
    month,
    round(sum(if_follow)/count(if_follow),3) as fans_growth_rate,
    total_fans
from (
    select 
        *,sum(if_follow) over(partition by author order by month) as total_fans
    from (
        select 
            t1.video_id,
            date_format(end_time,'%Y-%m') as month,
            if(if_follow=2,-1,if_follow) as if_follow,
            author
        from tb_user_video_log t1
        left join tb_video_info t2 on t1.video_id = t2.video_id
        where year(end_time) = 2021
        ) t
    ) t
group by author,month,total_fans
order by author,total_fans