题解 | #各个视频的平均完播率#

#各类，2021年上半年做完次数，2020年上半年做完次数，增长率，做完次数排名 with t1 as (select a.*,b.tag from (select * from exam_record)a left join (select exam_id,tag from examination_info)b on a.exam_id=b.exam_id)

select t3.tag ,num1,num2,concat(round((num2-num1)/num1*100,1),'%') rate ,cast(rn1 as signed),cast(rn2 as signed) 21rn ,cast(rn2 as signed)-cast(rn1 as signed) from (select tag,num1,rank() over(order by num1 desc) rn1 from (select tag,count(score) num1 from t1 where date_format(start_time,'%Y-%m') between '2020-01' and '2020-06' group by tag )t4 )t5 left join (select tag,num2,rank() over(order by num2 desc) rn2 from (select tag,count(score) num2 from t1 where date_format(start_time,'%Y-%m') between '2021-01' and '2021-06' group by tag )t2) t3 on t5.tag=t3.tag where num1 !=0 and num2!=0 order by rate desc,21rn desc