牛客368501572号

分类

题解(22)

/ 注册

全部文章（共22篇）

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

with tmp as( select exam_id, timestampdiff(second,start_time,submit_time) times , row_number()over(partition by exam_...

Mysql

2022-01-02

0 418

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

```select * from ( select tag,uid, row_number()over(partition by tag order by dd desc, xx desc,uid desc ) ranki...

Mysql

2021-12-29

0 341

题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#

#连续性标签，id减去id排序，结果相同代表对应id是互相连续的 select t4.author_id, author_level, days_cnt from ( select distinct author_id, -- 去重 ...

Mysql

2021-12-27

0 389

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

```select uid, 'activity1' activity from exam_record where year(start_time) group by uid having min(score)>=85 union select uid, ...

Mysql

2021-12-19

0 281

题解 | #每个题目和每份试卷被作答的人数和次数#

```select exam_id tid, count(distinct uid) uv, count(exam_id) pv from exam_record group by tid union select question_id tid, cou...

Mysql

2021-12-19

0 323

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

```select level, count(*) level_cnt from user_info left join exam_record using(uid) left join examination_info using(exam_id) where score>8...

Mysql

2021-12-19

0 352

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

select uid, sum(if(submit_time is null ,1,0)) incomplete_cnt, sum(if(submit_time is not null ,1,0)) complete_cnt, group_concat(distinct...

Mysql

2021-12-17

0 348

题解 | #月总刷题数和日均刷题数#

select date_format(submit_time,'%Y%m') submit_month, count(*) as month_q_cnt, round(count(*) / day(last_day(submit_time)) ,3) avg_day_q_cnt from pr...

Mysql

2021-12-17

0 322

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

```# count(distinct uid,date(submit_time) 注意一个人可能存在一天提交2次交券，故去重 select date_format(submit_time,'%Y%m') month, round(count(distinct uid,date(...

Mysql

2021-12-17

0 380

题解 | #得分不小于平均分的最低分#

min(score) min_score_over_avg from exam_record join examination_info using(exam_id) where tag='SQL' and score >= -- 筛选试卷类别为tag，且分数大于平均分，然后去最小的分...

Mysql

2021-12-17

1 480