每日SQL day1

题目来源：牛客网sql大厂面试真题 01某音短视频

问题1 

select
    t1.video_id,
    round(sum(if(t1.end_time-t1.start_time >= t2.duration, 1,0))/count(t1.start_time),3) 
    as avg_comp_play_rate
from
(select * from tb_user_video_log where year(start_time) = 2021)t1
left join 
(select * from tb_video_info)t2
on t1.video_id = t2.video_id
group by video_id
order by avg_comp_play_rate desc

 

问题2

select
    t2.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 t1
left join 
tb_video_info t2
on t1.video_id = t2.video_id
group by t2.tag
having avg(timestampdiff(second,t1.start_time,t1.end_time)/t2.duration)>0.6
order by avg_play_progress desc

问题3

select
    t2.tag,
    sum(t1.if_retweet) as retweet_cut,
    round(sum(t1.if_retweet)/count(1),3) as retweet_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)) <= 29
group by t2.tag
order by retweet_rate desc

问题4

select
    author,
    month,
    round(fan_add_cnt/play_cnt,3) as fans_growth_rate,
    sum(fan_add_cnt) over(partition by author order by month) as total_fans
from(
    select
        author,
        date_format(start_time, "%Y-%m") as month,
        sum(if(if_follow=2,-1,if_follow))as fan_add_cnt,    #涨粉量
        count(1) as play_cnt                               #播放量
    from tb_user_video_log t1
    left join tb_video_info t2
    on t1.video_id = t2.video_id
    where year(t1.start_time) = 2021
    group by author,month
    order by author
) as t3
order by author,total_fans

问题5

select * 
from(
    select
        tag,
        dt,
        sum(like_cnt) over(partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
        max(retweet_cnt) over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
    from(
        select 
            tag,
            date(start_time) as dt,
            sum(if_like) as like_cnt,
            sum(if_retweet) as retweet_cnt
        from tb_user_video_log t1
        left join tb_video_info t2 using(video_id)
        where year(start_time) = 2021
        group by tag,dt
    ) as t3
)as t4
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt

问题6

 

select
    video_id,
    round((100*comp_play_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)/(fresh_index+1),0) as hot_index
from(
    select
        video_id,
        sum(if(timestampdiff(second,start_time,end_time)-duration>=0,1,0))/count(video_id) as comp_play_rate,#完播率
        sum(if_like) as like_cnt,                   #点赞数
        count(comment_id) as comment_cnt,           #评论数
        sum(if_retweet)as retweet_cnt,              #转发数
        if(count(video_id)=0,datediff(date((select max(end_time) from tb_user_video_log)),date(release_time)),
              datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time)))) as fresh_index #新鲜度
    from tb_user_video_log t1
    left join tb_video_info t2 using(video_id)
    where datediff(date((select max(end_time) from tb_user_video_log)),date(t2.release_time))<=29
    group by video_id
)as t3
order by hot_index desc
limit 3