咖啡加不加糖
咖啡加不加糖
全部文章
题解
归档
标签
去牛客网
登录
/
注册
咖啡加不加糖的博客
全部文章
/ 题解
(共53篇)
题解 | #统计有未完成状态的试卷的未完成数和未完成率#
select * from ( select exam_id, sum(if(submit_time is null,1,0)) as incomplete_cnt ,round(sum(if(submit_time is null,1,0))/count(*),3) as incomplete_r...
Mysql
2021-12-31
0
262
题解 | #每月及截止当月的答题情况#
select e2.month_date,ifnull(t2.mau,0),ifnull(t3.month_add_uv,0), max(t3.month_add_uv) over(order by e2.month_date) , sum(t3.month_add_uv) over (order ...
Mysql
2021-12-30
0
239
题解 | #每份试卷每月作答数和截止当月的作答总数。#
select t1.exam_id,t1.month_date,t1.cnt,sum(t1.cnt) over(partition by t1.exam_id ...
Mysql
2021-12-28
0
243
题解 | #对试卷得分做min-max归一化#
select t2.uid,t2.exam_id , round(avg(new_score),0) as avg_new_score from ( select t1.uid,t1.exam_id, if(max_score=min_score,t1.score,(t1.score-min_sco...
Mysql
2021-12-27
0
222
题解 | #近三个月未完成试卷数为0的用户完成情况#
select t2.uid,count(t2.month_submit) as exam_complete_cnt from (SELECT t1.uid,t1.month_submit from( select uid,date_format(start_time,'%Y-%m') as mo...
Mysql
2021-12-27
0
179
题解 | #满足条件的用户的试卷完成数和题目练习数#
# 思路: # 查询完成高难度SQL得分平均值大于80分且7级的用户 # 统计该用户2021年试卷完成次数和题目总练习次数 # 由于有些用户完成试卷,但是没有练习;有些用户练习了但是没有完成试卷,因此用左右连接后union拼起来 select * from ( select t5.uid,ifnul...
Mysql
2021-12-26
0
227
题解 | #每类试卷得分前3名#
# 思路: # 1、生成每个tag每个uid的最大分数、最小分数 # 2、利用row_number按照tag分组,按照最大分数、最小分数、uid排序生成排名 # 3、查询排名小于等于3的信息 select t2.tag,t2.uid,t2.rank_score from ( select t1.ta...
Mysql
2021-12-26
0
269
题解 | #分别满足两个活动的人#
# 获取活动1的用户 select t2.uid,'activity1' as activity from exam_record as t2 where year(t2.submit_time) = 2021 group by t2.uid having min(t2.score) >=...
Mysql
2021-12-25
0
212
题解 | #每个题目和每份试卷被作答的人数和次数#
select * from ( select exam_id as tid,count(distinct uid) as uv, count(start_time) as pv from exam_record group by exam_id order by uv desc ,pv desc)...
Mysql
2021-12-25
0
145
题解 | #月总刷题数和日均刷题数#
select t1.submit_time,count(*) as month_q_cnt, round(count(*)/t1.cnt_day,3) as avg_day_q_cnt from (select uid,question_id,date_format(submit_time,'%...
Mysql
2021-12-22
0
244
首页
上一页
1
2
3
4
5
6
下一页
末页