SQL语句每日一练七

1.截断平均值

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

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

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

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

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

从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 得分）

示例输出：

解释：表示截止当前，有 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 得分）：

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

示例输出数据：

解释：试卷 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 得分）

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

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，示例内容如下：

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

#每月刷题数目
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