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

select
  t3.tag,
  concat(round(sum(x2) / count(*), 2), '%') avg_play_progress
from
(
    select
      t1.uid,
      t1.video_id,
      TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time) x1,
      t2.video_id video_id2,
      t2.duration,
      t2.tag tag,
      case
        when TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time) >= t2.duration then 1
        else TIMESTAMPDIFF(SECOND, t1.start_time, t1.end_time) / t2.duration
      end * 100 x2
    from
      tb_user_video_log t1
      left join tb_video_info t2 on t1.video_id = t2.video_id
  ) t3
group by
  t3.tag
having
  sum(x2) / count(*) > 60
order by
  sum(x2) / count(*) desc