题解 | #各个视频的平均完播率#
各个视频的平均完播率
http://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
select
u.video_id,
round((sum(if(user_duration>=duration,1,0))/count(u.video_id)),3) as avg_comp_play_rate
from
(
select
video_id,
(unix_timestamp(end_time) - unix_timestamp(start_time)) as user_duration
from
tb_user_video_log
-- where date_format(start_time,'yyyy') = '2021' //hive
where year(start_time) = '2021'//mysql
) u
inner join
(
select
video_id,
duration
from
tb_video_info
-- where date_format(release_time,'yyyy') = '2021'
) v
on u.video_id = v.video_id
group by video_id
order by avg_comp_play_rate desc
滴滴一面338人在聊
投递数字马力等公司7个岗位
影石Insta360公司福利 490人发布