冷凡社长

分类

题解(2)

/ 注册

TA的专栏 0篇文章 0人订阅

0篇文章 0人学习

全部文章（共136篇）

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

select y_month as start_month,mau,month_add_uv, max(month_add_uv) over(order by y_month) as max_month_add_uv, sum(month_add_uv) over(ord...

Mysql

2022-08-23

1 274

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

select uid,exam_id, round(avg(case when count_score >1 then ((score-min_score)/(max_score-min_score))*100 else score end )) as avg_new_score from ...

Mysql

2022-08-23

2 319

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

select tag ,sum(case when start_year =2020 then exam_cnt else 0 end) as exam_cnt_20 ,sum(case when start_year =2021 then exam_cnt else 0 end) as exam_...

Mysql

2022-08-23

1 248

题解 | #未完成率较高的50%用户近三个月答卷情况#

select t1.uid,t1.month_d,count(*)as total_cnt,count(t1.submit_time) as complete_cnt from -- 先求出未完成率的排名 (select uid,count(submit_time is null...

Mysql

2022-08-23

1 287

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

select uid,sum(num1) as exam_complete_cnt from ( select uid,date_format(start_time,'%Y%m') as month_d , count(start_time) as...

Mysql

2022-08-23

2 385

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

with t as ( SELECT uid,date(start_time) as start_day,exam_id, row_number() over(partition by uid order by start_time) as ranking from exam_recor...

Mysql

2022-08-23

1 297

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

with t as ( SELECT uid,date(start_time) as start_day,exam_id, row_number() over(partition by uid order by start_time) as ranking from exam_recor...

Mysql

2022-08-23

1 341

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

select exam_id,duration,release_time from ( select exam_id,sum(case when rank_asc = 2 then time_1 else 0 end) as asc_d ,sum(case when ra...

Mysql

2022-08-23

1 320

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

SELECT * FROM ( SELECT tag AS tid, uid, ...

Mysql

2022-08-23

1 305

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

思路：使用一维表的思维，将试卷和答题结合起来，且区分来源，这样既可以方便计算总量，也方便根据类别计算。 select uid, count(distinct act_month) as act_month_total, count(distinct case when yea...

Mysql

2022-08-22

7 345