JoKerhhh

分类

/ 注册

全部文章（共34篇）

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

# 在试卷作答和题目练习记录表筛选满足最近一次作答在2021年9月 select uid,nick_name,achievement from user_info where nick_name like "牛客%号" AND uid in ( select uid...

2024-05-15

0 114

题解 | #每类视频近一个月的转发量/率#

select b.tag , sum(if_retweet) AS retweet_cnt, round(sum(if_retweet)/count(*),3) as retweet_rate FROM tb_user_video_log a LEFT JOIN tb_video_info b u...

2024-04-24

0 118

题解 | #平均播放进度大于60%的视频类别#

select tag,concat(avg_play_progress,"%") AS avg_play_progress FROM ( select tag, round(avg(if(TIMESTAMPDIFF(SECOND, start_time, end_time)>...

2024-04-24

0 122

题解 | #各个视频的平均完播率#

select tuvl.video_id, round(sum(if(tuvl.end_time - tuvl.start_time >= tvi.duration,1,0)) / COUNT(start_time),3) AS avg_comp_play_rate FROM tb_user_...

2024-04-24

0 104

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

select uid, COUNT(distinct act_month) AS act_month_total, COUNT(distinct if(YEAR(act_day) = '2021',act_day, NULL)) AS act_days_2021, COUNT(distinct if...

2024-01-31

0 231

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

#高难度SQL试卷得分平均值大于80并且是7级的红名大佬 2021年完成次数和题目练习次数 #不能把四个表连起来因为试卷id 和题目id 是没关联的 select ui.uid, COUNT(er.submit_time) AS exam_cnt, #题目练习总次数 COALESCE( ...

2024-01-30

0 220

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

select distinct uid, 'activity1' AS activity FROM exam_record WHERE YEAR(start_time) = '2021' GROUP BY uid HAVING MIN(score) >= 85 # 至少有一次用了一半时间就...

2024-01-30

0 179

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

# union合并 select exam_id AS tid, COUNT(distinct uid) AS uv, COUNT(start_time) AS pv FROM exam_record GROUP BY exam_id union select question_id AS tid...

2024-01-28

0 251

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

select level, COUNT(distinct er.uid) AS level_cnt FROM user_info AS ui JOIN exam_record AS er using(uid) JOIN examination_info AS ei using(exam_id) W...

2024-01-28

0 156

题解 | #试卷发布当天作答人数和平均分#

# 首先看需要哪些列 exam_id ,作答人数，平均分 # 限制为SQL类型的试卷按exam_id 进行分组 # 对uid进行限制 5级以上 SQL发布当天的作答 select exam_id, COUNT(distinct er.uid) AS uv, ROUND(AVG(er.scor...

2024-01-28

0 201