SQL语句每日一练七

1.截断平均值

描述： 牛客的运营同学想要查看大家在 SQL 类别中高难度试卷的得分情况。

请你帮她从exam_record数据表中计算所有用户完成 SQL 类别高难度试卷得分的截断平均值（去掉一个最大值和一个最小值后的平均值）。

示例数据：examination_info（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	算法	medium	80	2020-08-02 10:00:00

示例数据：exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1001	9001	2021-05-02 10:01:01	2021-05-02 10:30:01	81
3	1001	9001	2021-06-02 19:01:01	2021-06-02 19:31:01	84
4	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
5	1001	9001	2021-09-02 12:01:01	(NULL)	(NULL)
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
8	1002	9001	2021-05-05 18:01:01	2021-05-05 18:59:02	90
9	1003	9001	2021-09-07 12:01:01	2021-09-07 10:31:01	50
10	1004	9001	2021-09-06 10:01:01	(NULL)	(NULL)

根据输入你的查询结果如下：

tag	difficulty	clip_avg_score
SQL	hard	81.7

从examination_info表可知，试卷 9001 为高难度 SQL 试卷，该试卷被作答的得分有[80,81,84,90,50]，去除最高分和最低分后为[80,81,84]，平均分为 81.6666667，保留一位小数后为 81.7

select tag,difficuty,round(avg(score),1) as cli_avg_score from exam_record 
left join examination_info using(exam_id)
where exam_id 9001 and exam_id not in(
select max(score) from exam_record where exam_id = 9001
union all
select min(score) from exam_record where exam_id = 9001
);

select t1.tag,t1.difficuty,round((sum(t2.score)-min(t2.score)-t2.max(score))/(count(t2.score),1)) as cli_avg_score
from examination_info t1,exam_record t2
where t1.exam_id = t2.exam_id and t1.tag = 'SQL' and difficuty = 'hard';

2.统计作答次数

有一个试卷作答记录表 exam_record，请从中统计出总作答次数 total_pv、试卷已完成作答数 complete_pv、已完成的试卷数 complete_exam_cnt。

示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1001	9001	2021-05-02 10:01:01	2021-05-02 10:30:01	81
3	1001	9001	2021-06-02 19:01:01	2021-06-02 19:31:01	84
4	1001	9002	2021-09-05 19:01:01	2021-09-05 19:40:01	89
5	1001	9001	2021-09-02 12:01:01	(NULL)	(NULL)
6	1001	9002	2021-09-01 12:01:01	(NULL)	(NULL)
7	1002	9002	2021-02-02 19:01:01	2021-02-02 19:30:01	87
8	1002	9001	2021-05-05 18:01:01	2021-05-05 18:59:02	90
9	1003	9001	2021-09-07 12:01:01	2021-09-07 10:31:01	50
10	1004	9001	2021-09-06 10:01:01	(NULL)	(NULL)

示例输出：

total_pv	complete_pv	complete_exam_cnt
11	7	2

解释：表示截止当前，有 11 次试卷作答记录，已完成的作答次数为 7 次（中途退出的为未完成状态，其交卷时间和份数为 NULL），已完成的试卷有 9001 和 9002 两份。

select 
	count(*) as total_pv,
	(select count(*) from exam_record where submit_time is not null) as complete_pv,
	(select count(distinct exam_id,score is not null or null) from exam_record ) as complete_exam_cnt
from exam_record;

3.得分不小于平均分的最低分

描述： 请从试卷作答记录表中找到 SQL 试卷得分不小于该类试卷平均得分的用户最低得分。

示例数据 exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1002	9001	2021-09-05 19:01:01	2021-09-05 19:40:01	89
3	1002	9002	2021-09-02 12:01:01	(NULL)	(NULL)
4	1002	9003	2021-09-01 12:01:01	(NULL)	(NULL)
5	1002	9001	2021-02-02 19:01:01	2021-02-02 19:30:01	87
6	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
7	1003	9002	2021-02-06 12:01:01	(NULL)	(NULL)
8	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
9	1004	9003	2021-09-06 12:01:01	(NULL)	(NULL)

examination_info 表（exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	SQL	easy	60	2020-02-01 10:00:00
3	9003	算法	medium	80	2020-08-02 10:00:00

示例输出数据：

min_score_over_avg
87

解释：试卷 9001 和 9002 为 SQL 类别，作答这两份试卷的得分有[80,89,87,90]，平均分为 86.5，不小于平均分的最小分数为 87

#求出SQL类型试卷的平均分
select round(avg(score),1) as avg_score from exam_record t1,examination_info t2 
where t1.exam_id = t2.exam_id and t2.tag = 'SQL';
#再求大于平均值的最小值
select min(score) as min_score_over_avg from exam_record inner join examination_info using(exam_id)
where tag = 'SQL' and score > (select round(avg(score),1) as avg_score from exam_record t1,examination_info t2 
								where t1.exam_id = t2.exam_id and t2.tag = 'SQL');

4.平均活跃天数和月活人数

描述：用户在牛客试卷作答区作答记录存储在表 exam_record 中，内容如下：

exam_record 表（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-02 09:01:01	2020-01-02 09:21:01	80
2	1002	9001	2021-09-05 19:01:01	2021-09-05 19:40:01	89
3	1002	9002	2021-09-02 12:01:01	(NULL)	(NULL)
4	1002	9003	2021-09-01 12:01:01	(NULL)	(NULL)
5	1002	9001	2021-02-02 19:01:01	2021-02-02 19:30:01	87
6	1002	9002	2021-05-05 18:01:01	2021-05-05 18:59:02	90
7	1003	9002	2021-02-06 12:01:01	(NULL)	(NULL)
8	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
9	1004	9003	2021-09-06 12:01:01	(NULL)	(NULL)

请计算 2021 年每个月里试卷作答区用户平均月活跃天数 avg_active_days 和月度活跃人数 mau，上面数据的示例输出如下：

month	avg_active_days	mau
202107	1.50	2
202109	1.25	4

select date_format(submit_time,'%Y%m') month,
	   round(count(distinct UID,date_format(submit_time,'%Y%m%d'))/count(distinct UID),2) avg_active_days,
	   count(distinct UID) mau
from exam_record
where year(sumbit_time) = 2021
group by month;

5.月总刷题数和日均刷题数

描述：现有一张题目练习记录表 practice_record，示例内容如下：

id	uid	question_id	submit_time	score
1	1001	8001	2021-08-02 11:41:01	60
2	1002	8001	2021-09-02 19:30:01	50
3	1002	8001	2021-09-02 19:20:01	70
4	1002	8002	2021-09-02 19:38:01	70
5	1003	8002	2021-08-01 19:38:01	80

请从中统计出 2021 年每个月里用户的月总刷题数 month_q_cnt 和日均刷题数 avg_day_q_cnt（按月份升序排序）以及该年的总体情况，示例数据输出如下：

submit_month	month_q_cnt	avg_day_q_cnt
202108	2	0.065
202109	3	0.100
2021 汇总	5	0.161

#每月刷题数目
select month(submit_time) submit_month,count(question_id) month_q_cnt from exam_reords group by month(submit_time);
#求每月有多少天
select day(last_day('2023-01-01')) as days_in_month;
#最后
select date_format(submit_time,'%Y%m') as submit_month,
	   count(question_id) as month_q_cnt,
	   round(count(question_id) / day(last_day(submit_time)) , 3) as avg_day_q_cnt
from practice_record
where date_format(submit_time,'%Y') = 2021
group by submit_month
union all
select '2021汇总' as submit_month,
	   count(question_id) as month_q_cnt,
	   round(count(question_id) / 31 , 3) as avg_day_q_cnt
where date_format(submit_time,'%Y') = 2021
group by submit_month