会编程的发呆爱好者加麻加辣

分类

/ 注册

全部文章（共208篇）

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

select ui.uid,er.exam_id, round(avg(score),0) as avg_score from user_info ui join exam_record er on ui.uid = er.uid join examination_info ei on ei.e...

2025-05-12

0 31

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

#活跃时间 with a as( select uid,max(dt) as latest from( select uid,date_format(start_time,'%Y%m') as dt from exam_record union select...

2025-05-11

0 32

题解 | 每月及截止当月的答题情况

#首次登录 with a as( select uid,date_format(min(start_time),'%Y%m') as first_month from exam_record group by uid ) select date_format(start_time,'%Y%m') ...

2025-05-10

0 31

题解 | 试卷完成数同比2020年的增长率及排名变化

with info_20 as( select tag,year(start_time) as start_year_20, count(submit_time) as exam_cnt_20, rank() over( order by count(submit_time) desc) as ...

2025-05-09

0 29

题解 | 未完成率top50%用户近三个月答卷情况

#SQL试卷上未完成率较高的50%用户 with goal as( select uid from (select uid, row_number() over(order by complete_rate) as rk, count(*) over () as num from( select u...

2025-05-08

0 32

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

select uid, max(days_w) as days_window, round(count(exam_id)/(timestampdiff(day,min(dt),max(dt))+1)*max(days_w),2) as avg_exam_cnt from( select b....

2025-05-07

0 33

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

with a as (select * from( select exam_id, row_number() over(partition by exam_id order by timestampdiff(minute,start_time,submit_time) desc) as rk...

2025-05-06

0 25

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

with a as( select uid,date(start_time) dt from exam_record union select uid,date(submit_time) dt from practice_record order by uid ) ...

2025-05-05

0 26

题解 | 月均完成试卷数不小于3的用户爱作答的类别

with a as( select uid, month(start_time) as m, count(submit_time) as cnt from exam_record er join examination_info ei on er.exam_id = ei.exam_id gr...

2025-05-04

0 25

题解 | SQL类别高难度试卷得分的截断平均值

select tag,difficulty, round(avg(score),1) as clip_avg_score from examination_info ei join exam_record er on ei.exam_id = er.exam_id where tag='SQL'...

2025-05-03

0 25