Vangen_Data_Analytics

分类

/ 注册

全部文章（共28篇）

题解 | 大小写混乱时的筛选统计

with t as( select exam_id,tag,count(start_time) as answer_cnt from exam_record left join examination_info using(exam_id) where upper(tag)!=tag group b...

2025-10-13

1 52

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

select distinct uid,nick_name,achievement from exam_record left join user_info using(uid) where date_format(start_time,'%Y%m')='202109' and nick_name ...

2025-10-13

1 41

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

with t as( select uid,exam_id,case when da!=xiao then (score-xiao)/(da-xiao)*100 else score end as dt from ( select uid,exam_id,score,max(score) over(...

2025-10-12

1 55

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

with t as( select uid,sum(if(submit_time is null,1,0)) as incomplete_cnt,count(start_time) as total_cnt,sum(if(submit_time is null,1,0))/count(start_t...

2025-10-12

1 47

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

with t as ( select uid,dt from( select uid,dt,row_number() over(partition by uid order by dt desc) as rk from( select distinct DATE_FORMAT(start_time,...

2025-10-12

1 39

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

with t as ( select exam_id,sum(case when fast=2 then dt end) as k,sum(case when slow=2 then dt end) as m from( select exam_id,dt,row_number() over(par...

2025-10-11

1 42

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

with t1 as(select distinct DATE_FORMAT(submit_time,'%Y-%m') as dt,a.uid from exam_record a left join user_info u on a.uid=u.uid where level in (6,7) h...

2025-10-11

1 34

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

with t as( select distinct uid from( SELECT DATE_FORMAT( start_time, '%Y-%m' ) AS dt, uid, count( CASE WHEN DATE_FORMAT(start_time,'%Y-%m' )= DATE_...

2025-10-10

1 58

题解 | 平均活跃天数和月活人数

with t0 as(select distinct date_format(start_time,'%Y%m') as month,uid,date_format(submit_time,'%Y-%m-%d') as dt from exam_record where score is not n...

2025-10-09

1 40

题解 | 每类试卷得分前3名

WITH t AS ( SELECT uid, tag, max( score ) AS da, min( score ) AS xiao FROM exam_record left join examination_info using(exam_id)where score is not nul...

2025-10-08

1 67