小亮来给他整个活

分类

/ 注册

全部文章（共123篇）

题解 | #注册当天就完成了试卷的名单第三页#

with cte_get_user as ( select uid,level,register_time,min(submit_time) as first_submit, max(score) as max_score from user_info join ex...

2024-07-21

0 167

题解 | #筛选昵称规则和试卷规则的作答记录#

select uid,exam_id,round(AVG(score)) as avg_score from user_info join exam_record using(uid) join examination_info using(exam_id) where submit_time is...

2024-07-21

0 88

题解 | #筛选限定昵称成就值活跃日期的用户#

with cte_get_active as ( select uid from( select uid,start_time as recent_act from exam_record union all selec...

2024-07-20

0 180

题解 | #0级用户高难度试卷的平均用时和平均得分#

with cte_time_cost as ( select uid, if(score is null,0,score) as new_socre, if(submit_time is not null,timestampdiff(minute,start_time,sub...

2024-07-19

0 143

题解 | #统计有未完成状态的试卷的未完成数和未完成率#

select exam_id, sum(if(submit_time is null,1,0)) incomplete_cnt, round(sum(if(submit_time is null,1,0))/count(start_time),3) as complete_rate from exa...

2024-07-19

0 117

题解 | #每份试卷每月作答数和截止当月的作答总数。#

SELECT exam_id,start_month,month_cnt, sum(month_cnt) over ( partition by exam_id order by start_month asc # 窗口函数over(...

2024-07-19

0 145

题解 | #对试卷得分做min-max归一化#

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 m...

2024-07-18

0 155

题解 | #近三个月未完成试卷数为0的用户完成情况#

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_...

2024-07-18

0 167

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

with cte_get_timewindow as ( select uid, datediff(max(start_time),min(start_time))+1 as timediff,# 最长作答相隔天数 max(datediff(nextday,start_tim...

2024-07-18

0 138

题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

with cte_get_duration as ( select exam_id, timestampdiff(minute,start_time,submit_time) as time_spand from exam_record where submit_ti...

2024-07-17

0 120