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

#diff / sec group by category
#having > 60%
#concat(round (..,2),"%")
# desc

with t1 as(
    select tu.video_id, tv.tag, case 
    when(time_to_sec(timediff(tu.end_time,tu.start_time))/tv.duration>=1) then 1.00
    when (time_to_sec(timediff(tu.end_time,tu.start_time))/tv.duration<1) then time_to_sec(timediff(tu.end_time,tu.start_time))/tv.duration end as progress
    from tb_user_video_log tu join tb_video_info tv on tu.video_id=tv.video_id
)
select t1.tag,concat(round(avg(t1.progress)*100,2),"%") as avg_play_progress
from t1
group by t1.tag
having avg(t1.progress)>0.6
order by avg(t1.progress) desc