无上清颜

题解

/ 注册

全部文章 / 题解（共49篇）

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

with t as (SELECT uid,start_time,submit_time,DENSE_RANK()over(partition by uid order by DATE_FORMAT(start_time,"%y%m") desc) as rn from exam_record) ...

Mysql

2021-11-24

1 392

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

比较综合窗口函数：lead()over() first_value()over() last_value()over() 时间做差函数:datediff(大,小) 注意要的是2021年数据 with t as (select uid, date(start_time) a...

Mysql

2021-11-22

2 554

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

注意：相差时长大于等于限定时长的一半 with t as (select t.exam_id, TIMESTAMPDIFF(MINUTE,start_time,submit_time) as time_gap, row_number()over(partition ...

Mysql

2021-11-22

1 387

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

select * from (select tag,uid,ROW_NUMBER()over(partition by tag order by max(score) desc,min(score) desc,uid desc) as ranking from examination_info t ...

Mysql

2021-11-20

1 360

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

层层击破 with t as ( select uid,start_time,submit_time,exam_id from exam_record union all select uid,submit_time as start_time,submit_tim...

Mysql

2021-11-19

1 456

题解 | #满足条件的用户的试卷完成数和题目练习数#

四个条件： 1.高难度SQL试卷平均分大于80 2.7级红名dalao 3.统计2021年试卷完成数和题目练习数 4.只保留2021年有试卷完成记录的用户 select t.uid, count(distinct exam_id) as exam_cnt, count(distinct q...

Mysql

2021-11-19

3 470

题解 | #分别满足两个活动的人#

本题最大亮点就是TIMESTAMPDIFF函数 select * FROM (select uid,'activity1' as activity from exam_record where year(start_...

Mysql

2021-11-19

2 457

题解 | #作答试卷得分大于过80的人的用户等级分布#

SELECt level,count(1) as level_cnt from user_info t,examination_info t1,exam_record t2 where t.uid = t2.uid and t1.exam_id = t2.exam_id and tag='SQL' ...

Mysql

2021-10-31

1 444

不连表就是玩儿

select exam_id,count(distinct uid) as uv,round(avg(score),1) as avg_score from exam_record where exam_id in (select exam_id from examination_info wher...

Mysql

2021-10-30

30 2513

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

# 第一步求出MAU和总活跃人数不计天数重复 select date_format(start_time,"%Y%m") as month,round(count(distinct uid,date_format(start_time,"%Y%m%d"))/count(distinct uid),...

Mysql

2021-10-30

0 395