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

select 
DISTINCT tag,
CONCAT(ROUND(sum(jindu)/sum(duration)*100,2),'%') avg_play_progress
FROM 
(select 
DISTINCT 
a.uid,
a.video_id,
case when timestampdiff(second,a.start_time,a.end_time)< b.duration THEN timestampdiff(second,a.start_time,a.end_time) ELSE b.duration end as jindu,
b.tag,
b.duration
FROM
tb_user_video_log a
left join
tb_video_info b
on a.video_id = b.video_id
) h
group by tag
having 
sum(jindu)/sum(duration)>0.6
order by sum(jindu)/sum(duration) DESC