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

select
  b.tag,
  concat(
    round(
      avg(
        case
          when timestampdiff(second, start_time, end_time) / b.duration > 1 then 1
          else timestampdiff(second, start_time, end_time) / b.duration
        end
      ) * 100,
      2
    ),
    '%'
  ) as avg_play_progress
from
  tb_user_video_log a,
  tb_video_info b
where
  a.video_id = b.video_id
group by
  tag
having
  avg_play_progress > 60
order by
  avg_play_progress desc;