题解 | #各个视频的平均完播率#
各个视频的平均完播率
https://www.nowcoder.com/practice/96263162f69a48df9d84a93c71045753
为什么 case when不能通过呢？？mysql里都可以的
select tb_user_video_log.video_id,
round(count(distinct case when end_time-start_time>= duration then uid else null end )/
count(distinct uid) ,3)as avg_comp_play_rate
from tb_user_video_log
join tb_video_info
on tb_user_video_log.video_id=tb_video_info.video_id
where year(release_time)=2021
group by tb_user_video_log.video_id
order by avg_comp_play_rate desc
