题解 | #近三个月未完成试卷数为0的用户完成情况#

写点不一样的解法

	uid,
	exam_complete_cnt
from
	(
	select
		r.uid, sum(submit_time is not null) exam_complete_cnt
	from
		exam_record r
	where
		(uid, date_format(start_time, '%Y-%m')) in (
		select
			uid, `month`
		from
			(
			select
				uid, `month`, `rank`
			from
				(
				select
					uid, date_format(start_time, '%Y-%m') `month`, dense_rank() over (partition by uid
				order by
					date_format(start_time, '%Y-%m') desc) `rank`
				from
					exam_record)s
			where
				`rank` <= 3)m)
	group by
		r.uid)t
where
	t.uid not in (
	select
		r.uid
	from
		exam_record r
	where
		r.submit_time is null
		and (uid, date_format(start_time, '%Y-%m')) in (
		select
			uid, `month`
		from
			(
			select
				uid, `month`, `rank`
			from
				(
				select
					uid, date_format(start_time, '%Y-%m') `month`, dense_rank() over (partition by uid
				order by
					date_format(start_time, '%Y-%m') desc) `rank`
				from
					exam_record)s
			where
				`rank` <= 3)m))
order by
	exam_complete_cnt desc,
	uid desc;