社畜张二狗

分类

题解(18)

/ 注册

全部文章（共18篇）

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

select uid,exam_id,round(avg(new_score),0) as avg_new_score from( select *, case when cnt_score=1 then score else (score - min_score)/(max_score - min...

Mysql

2022-01-16

0 213

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

select tag,cnt_20,cnt_21,growth,ra_20,ra_21,cast(ra_21 as signed) - cast(ra_20 as signed) as ra_delta from( select tag,cnt_20,cnt_21, concat(round((c...

Mysql

2022-01-16

0 239

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

select uid,m,t_cnt,com_cnt from( select *, dense_rank() over(order by incom_rate asc,uid) as dra_incom_rate_a, dense_rank() over(order by incom_rate d...

Mysql

2022-01-16

0 216

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

select uid,max(timestampdiff(day,t,next_t)+1) as maxt, round( max(timestampdiff(day,t,next_t)+1) * sum(cnt)/ (timestampdiff(day,min(t),max(...

Mysql

2022-01-15

0 188

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

select distinct a.exam_id,b.duration,b.release_time from( select exam_id, timestampdiff(minute,start_time,submit_time) as t, dense_rank() over(par...

Mysql

2022-01-15

0 201

题解 | #未完成试卷数大于1的有效用户#

select uid,total-finish as nonfinish,finish, group_concat( distinct concat(t,':',tag) separator ';' ) as detail from( select distinct a.uid, date(a.st...

Mysql

2022-01-15

0 198

题解 | #国庆期间近7日日均取消订单量#

select * from( select t, round(avg(finish_order) over w ,2) as avg_f_order, round(avg(nonfinish_order) over w ,2) as avg_nf_order from( select...

Mysql

2022-01-15

0 290

题解 | #10月的新户客单价和获客成本#

select round(avg(totalprice),1), round(avg(buyprice-totalprice),1) from( select distinct a.uid, date(a.event_time) as t, a.order_id, avg(a....

Mysql

2022-01-15

0 234