SELECT exam_id,start_month,month_cnt, sum(month_cnt) over ( partition by exam_id order by start_month asc # 窗口函数over()中如果使用排序，则前面的聚合函数按照当前行为最后一行进行聚合 ) as cum_exam_cnt from ( select exam_id, DATE_FORMAT(start_time, '%Y%m') as start_month, COUNT(start_time) as month_cnt from exam_record group by exam_...

with cte_get_deviation as ( select uid, exam_id, score, if(max_score=min_score,score,(score-min_score)*100/(max_score-min_score)) as max_min # 计算归一化的分数时需要注意最大分数差为0或者只有一个分数的情况，使用原分数 from ( select uid, exam_id, score, min(score) over(partition by exam_id) min_score, max(score) over(partition by exam_i...

select uid,count(submit_time) as exam_complete_cnt from ( select uid,start_time,submit_time, dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as recent_month # 使用连续排名将每个月分组，按照时间倒序排序，并用顺序数字标记 from exam_record ) t1 where recent_month<=3 # 获取顺序倒数第3以内的三组 group by uid h...

with cte_get_timewindow as ( select uid, datediff(max(start_time),min(start_time))+1 as timediff,# 最长作答相隔天数 max(datediff(nextday,start_time))+1 as days_window, # 连续作答的相隔天数，即'时间窗' count(start_time) as count_exam # 作答总数量 from ( select uid,exam_id,start_time, lead(start_time) over(partition by uid orde...

with cte_get_duration as ( select exam_id, timestampdiff(minute,start_time,submit_time) as time_spand from exam_record where submit_time is not null ) # 获取做题时长 select exam_id, duration, release_time from ( SELECT exam_id,duration,release_time, sum(case when rank_desc=2 then time_spand when rank_asc=...

with cte_get_uid as ( select uid from user_info join exam_record using(uid) join examination_info using(exam_id) where tag = 'SQL' and difficulty = 'hard' and `level` = 7 group by uid having avg(score) >= 80 ) # 根据条件获取大佬uid select uid,exam_cnt, if(question_cnt is null,0,question_cnt) # 特殊处理一下如果为空...

select uid, 'activity1' as activity from examination_info join exam_record using(exam_id) where year(submit_time)=2021 group by uid having min(score) >= 85 union select distinct uid, 'activity2' as activity from examination_info join exam_record using(exam_id) where year(submit_time)=2021 and sco...

select * from ( select exam_id as tid, count(distinct uid) as uv, count(uid) as pv from exam_record group by exam_id order by uv desc, pv desc ) as t1 union all select * from ( select question_id as tid, count(distinct uid) as uv, count(uid) as pv from practice_record group by question_id order by u...

with cte_get_uid as ( select uid, level from user_info join exam_record using(uid) join examination_info using(exam_id) where tag='SQL' and score>80 ) # with 子查询获取符合条件的用户 select level, count(uid) as level_cnt # 对等级分组后计算每组uid数量 from cte_get_uid group by level order by level_cnt desc # 降序排序

with cte_get_uid as ( select exam_id,uid,submit_time,score from user_info join exam_record using(uid) join examination_info using(exam_id) where level>5 and tag='SQL' and date(submit_time) = date(release_time) ) # 使用with子查询获取符合条件的基本数据。感觉题目说的不清楚，因为是现根据SQL类别筛选之后要展示所有类型的试卷的数据，所以必须现在子查询里面筛选出所有需要的数据 s...

with cet_atc_uid as ( select uid from exam_record join examination_info using(exam_id) group by uid,month(submit_time) having count(submit_time)>=3 ) # 使用with子查询得出符合条件的用户uid select tag, count(start_time) as tag_cnt from exam_record left join examination_info using(exam_id) where uid in (select ui...

select uid, count(if(submit_time is null,1,null)) as incomplete_cnt, count(if(submit_time is null,null,1)) as complete_cnt, group_concat(distinct concat_ws(':',date(start_time),tag) separator ';') as detail from exam_record er left join examination_info ei using(exam_id) where year(start_time)=2021 ...

(select date_format(submit_time,'%Y%m') as submit_month, count(submit_time) as month_q_cnt, round(count(submit_time)/max(day(last_day(submit_time))),3) avg_day_q_cnt from practice_record where year(submit_time) = 2021 group by date_format(submit_time,'%Y%m')) union all (select '2021汇总' as submit_mon...

select DATE_FORMAT(submit_time,'%Y%m') month, round(count(distinct uid,DATE_FORMAT(submit_time,'%Y%m%d'))/count(distinct uid),2) as avg_active_days, # 这边改成count(distinct uid,DATE_FORMAT(submit_time,'%Y%m%d')对日期和uid进行组合才对了，原本是写成count(distinct DATE_FORMAT(submit_time,'%Y%m%d')+uid)组成唯一标识来进行统计的，但是发现不对。...