题解 | #每个6/7级用户活跃情况#

select
  uid,
  days_window,
  round(dotime* days_window / daydiff, 2) as avg_exam_cnt
from
  (
    select
      uid,
      count(start_time) as dotime,
      datediff(max(start_time), min(start_time)) + 1 as daydiff,
      max(datediff(next_time, start_time)) + 1 as days_window
    from
      (
        select
          uid,
          exam_id,
          start_time,
          lead(start_time, 1) 
          over(partition by uid order by start_time)
          as next_time
        from
          exam_record
        where
          year(start_time) = 2021
      ) as t1
    group by uid
  ) as t2
where
  daydiff > 1
order by
  days_window desc,
  avg_exam_cnt desc

1、首先建立子表筛选出start_time和相邻的下一次时间next_time用窗口函数中的lead函数；

2、其次从1中找到相邻时间最大的窗口——days_window，以及首尾次做题的时间差；

3、题目要求的最后一列计算公式为（总次数/总天数）*最大时间窗口