牛客SQL138——连续两次作答试卷的最大时间窗

题目描述
https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c

思路：请计算在2021年至少有两天作答过试卷的人中，计算该年连续两次作答试卷的最大时间窗days_window，那么根据该年的历史规律他在days_window天里平均会做多少套试卷，按最大时间窗和平均做答试卷套数倒序排序。由示例数据结果输出如下：
1.# 先找到在2021年至少做了两天试卷的人

select uid 
from exam_record 
where year(submit_time)=2021
group by uid
having count(distinct date_format(submit_time,"%Y%m%d"))>=2

---

2.计算时间窗，先找到连续第二次作答的时间

select id,uid,exam_id,date_format(start_time,"%Y%m%d"),date_format(submit_time,"%Y%m%d")as start_time,score,
lead(date_format(start_time,"%Y%m%d"),1) over(partition by uid order by date_format(start_time,"%Y%m%d")) as next_time
from exam_record
where uid in (
	select uid 
	from exam_record 
	where year(start_time)=2021
	group by uid
	having count(distinct date_format(submit_time,"%Y%m%d"))>=2
	and year(start_time)=2021 
	)

---

3.#计算最大时间窗,以及平均时间

# 计算最大时间窗,以及平均时间，
select uid,max(datediff(next_time,submit_time))+1 as days_window,
# select uid,count(exam_id),(datediff(max(start_time),min(start_time))+1),(max(datediff(next_time,submit_time))+1)
round((count(exam_id)/(datediff(max(start_time),min(start_time))+1))*(max(datediff(next_time,submit_time))+1),2) as avg_exam_cnt
from(
    select id,uid,exam_id,date_format(start_time,"%Y%m%d") as start_time,date_format(submit_time,"%Y%m%d") as submit_time,score,
    lead(date_format(submit_time,"%Y%m%d"),1) over(partition by uid order by date_format(submit_time,"%Y%m%d")) as next_time
    from exam_record
    where uid in (
        select uid 
        from exam_record 
        where year(submit_time)=2021
        group by uid
        having count(distinct date_format(submit_time,"%Y%m%d"))>=2
     ) and  year(start_time)=2021
) as t
group by uid
order by days_window desc,avg_exam_cnt desc