select t2.uid,days_window,round(exams*days,2) avg_exam_cnt
from(
						select uid,
										max(diff)+1 days_window,
										1/(DATEDIFF(max(start_days),min(start_days))+1) *(max(diff)+1) days
						-- uid,count(start_days),max(start_days),min(start_days),DATEDIFF(max(start_days),min(start_days)) days_window,max(diff) diff2
						from (
									select uid,start_days,lead(start_days,1) over(PARTITION by uid  order by uid) start_days2,DATEDIFF(lead(start_days,1) over(PARTITION by uid order by uid,start_days asc), start_days) diff
									from(
												select distinct uid,DATE_FORMAT(start_time,'%Y-%m-%d') start_days
												from exam_record
												where left(start_time,4)='2021'
												group by uid,start_days
												order by 1,2
											) t1
						 ) t
						group by uid
						having DATEDIFF(max(start_days),min(start_days))>0
			
		) t2

left join
(
			select uid,count( exam_id) exams
			from exam_record
			where left(start_time,4)='2021'
			group by uid
) c
on t2.uid=c.uid
where c.uid is not null
order by 2 desc,3 desc


1、时间因素
需要year=2021年

2、排序问题,出来两个条件降序

3、开窗函数
往上lead
lead(字段,2) over(partition by uid order by uid) rn