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

#未完成率>50%，6级和7级用户近3个月的作答sql试卷次数和完成次数 with t1 as ( select a.* ,b.tag,c.level from (select * from exam_record) a left join (select exam_id ,tag from examination_info ) b on a.exam_id =b.exam_id left join (select uid,level from user_info )c on c.uid=a.uid)

select uid,mon,total,complete from (select t2.uid,mon,total,complete,mup, row_number() over(partition by uid order by mon desc) rn from (select uid from (select uid, row_number() over(order by uncom_rate asc) rn2 from( select uid,(1-count(score)/count(1)) uncom_rate from t1 where tag='SQL' group by uid)v)j where rn2>=(((select count(distinct uid) from t1)+1)/2))t2 inner join (select uid,date_format(start_time,'%Y%m') mon,count(1) total ,count(score) complete,(1-count(score)/count(1)) mup from t1 where level in ('6','7') group by uid,date_format(start_time,'%Y%m'))t3 on t2.uid=t3.uid )t4 where rn<=3 order by uid,mon