题解 | #连续两次作答试卷的最大时间窗#

连续两次作答试卷的最大时间窗

https://www.nowcoder.com/practice/9dcc0eebb8394e79ada1d4d4e979d73c

select uid,max(datediff(date,date_1)+1) days_window,round(max(datediff(date,date_1)+1)*count(exam_id)/(datediff(max(date),min(date))+1),2) avg_exam_cnt from
(
select uid,exam_id,date(start_time) date,lag(date(start_time))over(partition by uid order by date(start_time)) date_1 from exam_record
where uid in (
select uid from (
select uid,count(distinct date(start_time)) date_cnt  from exam_record
where year(start_time) = '2021'
and submit_time is not null
group by uid
) a
where date_cnt >=2
)
and year(start_time) = '2021'
) b
group by uid
order by days_window desc,avg_exam_cnt desc;

全部评论

相关推荐

评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务