题解 | #未完成率较高的50%用户近三个月答卷情况#

# 统计近三个月中，每个月的答卷数目和完成数目

# 近三个月，先求月份，在对月份进行排序。生成排序子表

# 求月份：date_format()

# 月份排序：dense_rank()

# 从子表统计答卷数目和完成数目:

# 答卷数目：count(start_time)

# 完成数目：count（submit_time）

# 条件：uid满足：where...and...

# （1）SQL试卷上未完成率较高的50%用户

# （2）6级和7级用户

# 分组：group by uid,月份

# 排序

# 其中满足的条件部分

# （1）SQL试卷上未完成率较高的50%用户

# 先统计每个用户的未完成率,生成子表t1

# 对未完成率进行排序,子表t2:percen_rank()over(order by 未完成率)

# 筛选排序>0.5的用户

# （2）6级和7级用户,直接从user_info筛选

select 
	uid,
	start_month,
  	count(start_time) as total_cnt,
	count(submit_time) as complete_cnt
from (
	select 
		*,DATE_FORMAT(start_time,"%Y%m") as start_month,
		dense_rank() over(partition by uid order by DATE_FORMAT(start_time,"%Y%m") desc) as month_rank
	 from exam_record
) t3
where month_rank<=3
and uid in(select uid from user_info where level in(6,7))
and uid in(
	-- -- 用户的未完成率较高的50%用户。
	select 
		uid
	from(
		-- 用户的未完成率
		SELECT
			uid,
			percent_rank()over(order by in_complete_rate) as in_complete_rate
		from(
			SELECT
				uid,
				sum(case when submit_time is null then 1 else 0 end) as _in_complete,
				sum(case when submit_time is null then 1 else 0 end)/count(start_time) as in_complete_rate
			from exam_record
			where exam_id in(select exam_id from examination_info where tag="SQL")
			group by uid 
		)t1
	)t2
	where in_complete_rate>=0.5
)
group by uid,start_month
order by uid,start_month