题目：https://www.nowcoder.com/practice/c60242566ad94bc29959de0cdc6d95ef?tpId=268&tqId=2285039&ru=/exam/oj&qru=/ta/sql-factory-interview/question-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D268
方法1：
select
a.tag,
avg_play_progress
from (
select
tag,
concat(round(avg(if(timestampdiff(second,start_time,end_time)>=duration,
1,
timestampdiff(second,start_time,end_time)/duration)*100),2),
"%") as avg_play_progress
from tb_user_video_log log
join tb_video_info info
on log.video_id=info.video_id
group by tag
) a
where replace(avg_play_progress,"%","")>60
order by avg_play_progress desc
方法2：
#第一步 取出需要的数据，主要是取出每条视频的播放时间
with t as (select tag,TIMESTAMPDIFF(second,start_time,end_time) times,duration d
from tb_user_video_log tl left join tb_video_info ti on tl.video_id = ti.video_id)
#第二步，利用if函数计算每条视频的播放率，在利用avg()函数计算各类视频的平均完播率
select tag, concat(round(avg(if(times <= d,times/d*100,100)),2),'%') avg_play_progress
from t
group by tag
having avg(if(times <= d,times/d*100,100)) > 60
order by avg_play_progress desc
