题解 | #平均播放进度大于60%的视频类别#

select
tag
,concat(round(sum(if(ts>duration,duration,ts))/sum(duration) * 100,2),'%') as avg_play_progress
from
(
select *
,timestampdiff(second,start_time,end_time) ts
from tb_user_video_log
# where left(start_time,4) = 2021 and left(end_time,4) = 2021
) a
left join
(
select * from
tb_video_info
) b
on a.video_id = b.video_id
group by 1
having left(avg_play_progress,4) > '60.0'
order by 2 desc
;