牛客sql_SQL进阶挑战

做题方法：
对于难度较大的题目，建议采用三步走的方法

• 第一步：看懂题目，了解需求，看一下别人的代码，理解思路
• 第二步：自己基本上能够把代码写下来，但是还存在一定的问题
• 第三步：能够完整无错误写出代码

SQL1 插入记录（一）

• 注解
  1、insert into…values 如果要插入多行，指令是 values ,…多行之间用逗号隔开；
  2、Auto Increment类型的列，在插入时用null/default关键字补位，在插入时sql会自动计算各行应有的值；
  3、interval 时间间隔关键字，常和date_add() 或 date_sub()搭配使用。

insert into exam_record values
(null,1001,9001,"2021-09-01 22:11:12","2021-09-01 22:11:12"+interval 50 minute,90),
(null,1002,9002,"2021-09-04 07:01:02",null,null);

SQL2 插入记录（二）

insert into exam_record_before_2021
select null,uid,exam_id,start_time,submit_time,score
from exam_record
where score is not null and year(submit_time)<2021

SQL3 插入记录（三）

• 注解
  REPLACE INTO 跟 INSERT 功能类似
  不同点在于：REPLACE INTO 首先尝试插入数据到表中,
  如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，
  然后插入新的数据;否则，直接插入新数据。
  要注意的是：插入数据的表必须有主键或者是唯一索引！
  否则的话，REPLACE INTO 会直接插入数据，这将导致表中出现重复的数据。

replace into examination_info
(exam_id,tag,difficulty,duration,release_time) values
(9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00')；

SQL4 更新记录（一）

update examination_info
set tag = 'Python'
where tag = 'PYTHON'

SQL5 更新记录（二）

• 注解
  date_format(start_time,"%Y-%m-%d")<“2021-09-01”

update exam_record
set submit_time='2099-01-01 00:00:00',score=0
where  date_format(start_time,"%Y-%m-%d")<"2021-09-01" and submit_time is null;

SQL6 删除记录（一）

• 注解
  date_sub(start_time,interval -5 minute) sub -5 就是负负得正的意思
  date_add(start_time,interval +5 minute)
  时间差函数timestampdiff(minute,start_time,submit_time)
  timestampdiff函数返回begin-end的结果，其中begin和end是DATE或DATETIME表达式。

timestampdiff函数允许其参数具有混合类型，例如，begin是DATE值，end可以是DATETIME值。 如果使用DATE值，则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。

unit参数是确定(end-begin)的结果的单位，表示为整数。 以下是有效单位：
microsecond/second/minute/hour/day/week/month

• 解法一

delete 
from exam_record
where submit_time < date_sub(start_time,interval -5 minute) and score < 60

• 解法二

delete 
from exam_record
where submit_time < date_add(start_time,interval +5 minute) and score < 60

• 解法三

delete 
from exam_record
where timestampdiff(minute, start_time, submit_time ) < 5 
and score < 60;

SQL7 删除记录（二）

delete
from exam_record
where timestampdiff(minute,start_time,submit_time ) < 5 or submit_time is null
order by start_time asc
limit 3

SQL8 删除记录（三）

• DROP TABLE, TRUNCATE TABLE, DELETE TABLE　三种删除语句的区别
  1.DROP TABLE　清除数据并且销毁表，是一种数据库定义语言(DDL), 执行后不能撤销，被删除表格的关系，索引，权限等等都会被永久删除。
  2.TRUNCATE TABLE　只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据，是一种数据库定义语言(DDL） ，执行后不能回滚，删除后没有返回值，不能加where 条件。
  3.DELETE TABLE　删除（符合某些条件的）数据，是一种数据操纵语言(DML Data Manipulation Language)，执行后可以回滚，删除后有返回值，可以加where 条件。
  4.运行速度一般DROP最快，DELETE最慢，但是DELETE最安全。
  5.假如要删除的表中有自增长列，如果用delete 删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始。

truncate table exam_record

SQL9 创建一张新表

create table if not exists user_info_vip(
    id int(11) primary key auto_increment comment '自增ID',
    uid int(11) unique key not null  comment '用户ID',
    nick_name varchar(64) comment '昵称',
    achievement int(11) default 0 comment '成就值',
    level int(11) comment '用户等级',
    job varchar(32) comment '职业方向',
    register_time datetime  default CURRENT_TIMESTAMP comment'注册时间'
)

SQL10 修改表

alter table user_info add school varchar(15) after level;
-- 增加列在某列之后
-- alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)

alter table user_info change job profession varchar(10);
-- 更换列的名称及数据类型
-- alter table user_info change 原列名 修改列名 修改数据类型

alter table user_info modify achievement int(11) default 0;
-- 更改数据类型
-- alter table 表名 modify 修改列名称 数据类型 默认值等

SQL11 删除表

drop table if exists exam_record_2011;
drop table if exists exam_record_2012;
drop table if exists exam_record_2013;
drop table if exists exam_record_2014;

SQL12 创建索引

-- 唯一索引
alter table examination_info
add unique index uniq_idx_exam_id(exam_id);

-- 全文索引
alter table examination_info
add fulltext index full_idx_tag(tag);

-- 普通索引
alter table examination_info
add index idx_duration(duration);

SQL13 删除索引

• 方法一

drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;

• 方法二

alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

SQL14 SQL类别高难度试卷得分的截断平均值

select 
    i.tag
    ,i.difficulty
    ,round((sum(r.score)-max(r.score)-min(r.score))/(count(r.score)-2),1)
from examination_info i 
left join exam_record r on i.exam_id=r.exam_id
where i.tag='SQL' and i.difficulty='hard' and r.score is not NULL;

SQL15 统计作答次数

select 
    count(1) total_pv
    ,count(score) complete_pv
    ,count(distinct exam_id and score is not null) complete_exam_cnt
from exam_record

SQL16 得分不小于平均分的最低分

• 解法一

-- 方法1:用窗口函数直接求出avg，作为一列。然后score与avg比较
select min(t.score)
from (
	select 
		r.score
		,avg(r.score) over() avg_score
	from exam_record r 
	left join examination_info i on r.`exam_id`=i.`exam_id`
	where i.`tag`='SQL' and r.score is not null
	) t
where t.score>=t.avg_score

• 解法二

-- 方法2：聚合函数求出平均值，作为表中的一个筛选条件
select min(score)
from exam_record r
left join examination_info i on r.exam_id=i.exam_id
where i.tag="SQL" and r.score is not null 
and r.score>=(
	select avg(score)
	from exam_record r
	left join examination_info i on r.exam_id=i.exam_id
	where i.tag="SQL" and r.score is not null 
)

SQL17 平均活跃天数和月活人数

• 注解
  主要难的一点是天数的计算。到底是count(distinctuid,date_format(start_time,"%Y%m%d"))
  还是count(start_time)作为分子呢？？？
  关键是理解题目的意思是：天数。
  假设一个uid 比如1001在2021-07-06这一天有二个记录，如果是count(start_time)那么就是天数是2
  但是如果是count(distinct uid,date_format(start_time,"%Y%m%d"))天数就是1了，因为只有2021-07-06只有一天存在！
  所以关键是明白一天二个不同时间段是一天，还是二天

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 score is not null  and year(submit_time)=2021
group by date_format(submit_time,"%Y%m")

SQL18 月总刷题数和日均刷题数

• 注解
  day(last_day(submit_time) 该函数用于求当月的总天数

select 
    date_format(submit_time,"%Y%m") submit_month
    ,count(score) month_q_cnt
    ,round(count(score)/day(last_day(submit_time)) ,3) avg_day_q_cnt
from practice_record
where year(submit_time) =2021
group by date_format(submit_time,"%Y%m")
union all
select
    "2021汇总" submit_month
    ,count(1) month_q_cnt
    ,round(count(score)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time) =2021
order by submit_month

SQL19 未完成试卷数大于1的有效用户

• 注解
  1、统计未完成的列的时候要用submit_time,有的试卷没有提交确是有分数的
  2、函数group_concat()
  group_concat( distinct concat(date(e.start_time),’:’,i.tag)
  order by e.start_time separator ‘;’)

• 解法一

-- 有效用户
select uid
from exam_record
where year(start_time) = 2021
group by uid
having count(score)>=1 and (count(1)-count(score))<5

-- 完整代码
select 
	e.uid
	,(count(1)-count(e.submit_time))
	,count(e.submit_time)
	,group_concat(distinct concat(date_format(e.start_time,'%Y-%m-%d'),':',i.tag) order by e.start_time separator ';') detail
from exam_record e 
left join examination_info i on e.exam_id=i.exam_id
where uid in (
	select uid
	from exam_record
	where year(start_time) = 2021
	group by uid
	having count(score)>=1 and (count(1)-count(score))<5
) and year(e.start_time) = 2021
group by e.uid
having (count(1)-count(e.score))>1
order by e.uid desc

• 解法二

select  e.uid
	,sum(case when e.submit_time is null then 1 else 0 end) as imcomplete_cnt
	,sum(case when e.submit_time is not null then 1 else 0 end) as complete_cnt
	,group_concat( distinct concat(date(e.start_time),':',i.tag) 
			order by e.start_time separator ';') as detail
from exam_record e
left join examination_info i on e.exam_id=i.exam_id
where year(e.start_time)=2021
group by e.uid
having imcomplete_cnt>1 and complete_cnt>=1 and imcomplete_cnt<5
order by imcomplete_cnt desc;

SQL20 月均完成试卷数不小于3的用户爱作答的类别

select i.tag,count(i.tag) tag_cnt
from exam_record e
left join examination_info i on e.exam_id=i.exam_id
where e.uid in (
    select uid
    from exam_record 
    where submit_time is not null 
    group by uid,month(start_time)
    having count(submit_time)>=3
)   -- and e.submit_time is not null 错误：理解题目意思，只要满足前面条件就可以，后面不需要满足
group by i.tag
order by tag_cnt desc

SQL21 试卷发布当天作答人数和平均分

select 
    i.exam_id
    ,count(distinct t.uid) uv
    ,round(avg(r.score),1) avg_score
from examination_info i left join exam_record r
on i.exam_id=r.exam_id 
left join (select uid
            from user_info
            where level>5) t
on t.uid=r.uid
where date_format(i.release_time,"%Y-%m-%D")=date_format(r.submit_time,"%Y-%m-%D")
and t.uid is not null
group by i.exam_id
order by uv desc,avg_score asc

SQL22 作答试卷得分大于过80的人的用户等级分布

select u.level level,count(level) level_cnt
from examination_info i 
left join exam_record r on i.exam_id=r.exam_id
left join user_info u on u.uid = r.uid
where i.tag='SQL' and r.score>80 and u.uid is not null
group by u.level
order by level_cnt desc,level desc

SQL23 每个题目和每份试卷被作答的人数和次数

• 注解
  left(str,len)函数:从左边取len个字符
  right(str,len)函数从右边取len个字符

select 
    exam_id tid
    ,count(distinct uid)  uv
    ,count(1) pv
from exam_record
group by exam_id
union all 
select
    question_id tid
    ,count(distinct uid) uv
    ,count(submit_time) pv
from practice_record
group by question_id
order by left(tid,1) desc,uv desc,pv desc

SQL24 分别满足两个活动的人

• 注解
  1、“activity1” as activity这种写法
  2、timestampdiff函数中不能是minute而具体到秒才可以

select 
    uid
    ,"activity1" as activity
from exam_record
group by uid
having avg(score) >= 85 and min(score)>=85
union all
select
    distinct uid
    ,"activity2" as activity
from exam_record r 
left join examination_info i on r.exam_id=i.exam_id
where timestampdiff(second,r.start_time,r.submit_time)<=i.duration * 30 and r.score>=80 and i.difficulty='hard'
order by uid

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

• 注解
  注意加上2021年限制条件，排序问题，后面就一直子查询嵌套

select 
    t1.uid
    ,t1.exam_cnt
    ,ifnull(t2.question_cnt,0)
from(
select uid,count(score) exam_cnt
from exam_record
    where uid in (
    select uid
    from exam_record
    where uid in (
        select uid
        from user_info
        where level=7
    ) and exam_id in (
        select exam_id
        from examination_info
        where tag='SQL' and difficulty='hard'
    ) 
    group by uid
    having avg(score)>80
)and year(submit_time) =2021
group by uid
) t1
left join 
(
select uid,count(1) question_cnt
from practice_record
where uid in (
    select uid
    from exam_record
    where uid in (
        select uid
        from user_info
        where level=7
    ) and exam_id in (
        select exam_id
        from examination_info
        where tag='SQL' and difficulty='hard'
    ) 
    group by uid
    having avg(score)>80
) and year(submit_time) =2021
group by uid
) t2 
on t1.uid=t2.uid
order by exam_cnt asc,right(t1.uid,1) desc;

SQL26 每个6/7级用户活跃情况

select user_info.uid,
        count(distinct act_month) as act_month_total,
        count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
        count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
        count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question
from (SELECT uid,exam_id as ans_id,start_time as act_time,date_format(start_time,'%Y%m') as act_month, date_format(start_time,'%Y%m%d') as act_day,'exam' as tag from exam_record
     UNION ALL
     select uid,question_id as ans_id,submit_time as act_time,date_format(submit_time,'%Y%m') as act_month, date_format(submit_time,'%Y%m%d') as act_day,'question' as tag from practice_record ) total 
     right join user_info on total.uid=user_info.uid
where user_info.level in (6,7)
group by total.uid
order by act_month_total desc,act_days_2021 desc

SQL27 每类试卷得分前3名

• 注解
  如果查询的其他部分（WHERE，GROUP BY，HAVING）需要窗口函数，
  请使用子查询，然后在子查询中在使用窗口函数
  1、专用窗口函数，如rank, dense_rank, row_number等专用窗口函数。
  2、聚合函数，如sum. avg, count, max, min等

select t1.tag,t1.uid,t1.rk as ranking
from(
    select
        t.tag
        ,t.uid
        ,row_number() over(partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) as rk
    from(
        select
            i.tag
            ,e.uid
            ,max(e.score) max_score
            ,min(e.score) min_score
        from exam_record e
        inner join examination_info i on e.exam_id=i.exam_id
        group by i.tag,e.uid
    ) t
    group by t.tag,t.uid
) t1
where rk<=3

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

• 解法一
  先将用时时间排序，正序和倒叙两种，然后取出正序第二，倒叙第二，将两张表关联，然后用倒叙第二减正序第二大于试卷时长一半即可，最后根据试卷id倒叙

select 
	t2.exam_id
	,i.duration
	,i.release_time
from(
	select 
		exam_id
		,max(minute_diff)-min(minute_diff) as diff
	from(
		select 
			exam_id
			,minute_diff
			,row_number() over(partition by exam_id order by minute_diff asc) rk1
			,row_number() over(partition by exam_id order by minute_diff desc) rk2
		from(
			select 
				exam_id
				,timestampdiff(second,start_time,submit_time) as minute_diff
			from exam_record
			where submit_time is not null
			) as t 
		) t1
	where rk1=2 or rk2=2
	group by exam_id
	) t2
left join examination_info i on i.exam_id=t2.exam_id 
where t2.diff >i.duration*30
order by t2.exam_id desc

• 解法二
  NTH_VALUE()函数返回expression窗口框架第N行的值。如果第N行不存在，则函数返回NULL。N必须是正整数，例如1,2和3。

select 
	distinct c.exam_id
	,duration
	,release_time 
from 
	(select 
		a.exam_id
		,nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc ) as low_2
		,nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) asc) as fast_2
		,duration
		,release_time
	from exam_record a 
	left join examination_info b on a.exam_id = b.exam_id
	) c 
where low_2-fast_2>duration*30
order by exam_id desc;

SQL29 连续两次作答试卷的最大时间窗

-- 日期相减必须用函数timestampdiff或者datediff,若直接相减会出现保留两位小数的情况
with temp as (
select
	uid
	,date_day
	,lag(date_day,1) over(partition by uid order by date_day) as last_1_time
from (
    	select 
            uid
            ,date_format(start_time,'%Y%m%d') date_day
        from exam_record
        where uid in (
                select uid
                from exam_record
                group by uid
                having count(distinct date_format(start_time,'%Y%m%d'))>=2
        ) 
    ) t
where year(date_day)=2021 
)

select 
	uid
	,max(datediff(date_day,last_1_time))+1 as days_window -- 最大窗口
	,round((count(uid)/(datediff(max(date_day),min(date_day))+1))*(max(datediff(date_day,last_1_time))+1),2) as avg_exam_cnt  -- 平均答题数
from temp
group by uid
order by days_window desc,avg_exam_cnt desc

SQL30 近三个月未完成试卷数为0的用户完成情况

-- 理解题意，看解释
with temp as(
    select
    *
    ,dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as rk
    from exam_record
)

select 
    uid
    ,count(start_time) as exam_complete_cnt
from temp
where rk <= 3
group by uid
having count(score) = count(id)
order by exam_complete_cnt desc,uid desc

SQL31 未完成率较高的50%用户近三个月答卷情况

with rank_user as (
    select
        uid
        ,count(start_time)-count(submit_time) as incomplete_cnt
        ,count(start_time) as total_cnt 
        ,(count(start_time)-count(submit_time))/count(start_time) incomplete_rate
        ,row_number() over(order by (count(start_time)-count(submit_time))/count(start_time))as rk
    from exam_record
    left join examination_info using(exam_id) 
    where tag='sql'
    group by uid
) 

select uid,start_month,total_cnt,complete_cnt
from
(
    select 
        uid
        ,date_format(start_time,'%Y%m') start_month
        ,row_number() over(partition by uid order by date_format(start_time,'%Y%m') desc) rn
        ,count(start_time) as total_cnt 
        ,count(submit_time) as complete_cnt
    from exam_record
    where uid in (
                    select uid
                    from(
                        select 
                        *,rk/(max(rk) over()) as ranking
                        from rank_user
                    ) t
                    where ranking>0.5 and uid in(
                                                    select uid
                                                    from user_info
                                                    where level=6 or level=7
                                                    ) 
    )
    group by uid,start_month
) temp
where rn<=3
order by uid asc,start_month asc

SQL32 试卷完成数同比2020年的增长率及排名变化

-- unsign
-- cast(uid as signed) 类型转换函数，将整型转换成无符号整型
with temp as(
    select
        tag
        ,year(start_time) start_year
        ,count(start_time) exam_cnt
        ,rank() over(partition by year(start_time) order by count(start_time) desc) exam_cnt_rank
    from exam_record
    join examination_info using(exam_id)
    where submit_time is not null and month(start_time)<=6
    group by tag,start_year
)

select 
    t1.tag as tag
    ,t1.exam_cnt exam_cnt_20
    ,t2.exam_cnt exam_cnt_21
    ,concat(if(cast(t1.exam_cnt as signed)>cast(t2.exam_cnt as signed),'-',''),round(abs(cast(t1.exam_cnt as signed)-cast(t2.exam_cnt as signed))/cast(t1.exam_cnt as signed)*100,1),'%')as growth_rate
    ,t1.exam_cnt_rank exam_cnt_20
    ,t2.exam_cnt_rank exam_cnt_21
    ,cast(t2.exam_cnt_rank as signed) - cast(t1.exam_cnt_rank as signed) rank_delta
from temp t1
join temp t2 
on t1.tag=t2.tag and (t1.start_year+1)=t2.start_year
order by growth_rate desc,rank_delta desc

SQL33 对试卷得分做min-max归一化

with temp as(
    select
        uid
        ,exam_id
        ,case
        when count(exam_id) over(partition by exam_id)=1 then score
        else
        (((score-min(score) over(partition by exam_id))/((max(score) over(partition by exam_id))-(min(score) over(partition by exam_id)))))*100 
        end as min_max
    from exam_record  
    left join examination_info using(exam_id)
    where difficulty='hard' and score is not null
)   

select 
    uid
    ,exam_id
    ,round(avg(min_max),0) avg_new_score
from temp
group by uid,exam_id
order by exam_id asc,avg_new_score desc;

SQL34 每份试卷每月作答数和截止当月的作答总数。

with temp as (
    select 
        exam_id
        ,date_format(start_time,'%Y%m') start_month
    from exam_record
    where start_time is not null
)

select 
    exam_id
    ,start_month
    ,sum(count(start_month)) over(partition by exam_id,start_month) month_cnt
    ,sum(count(start_month)) over(partition by exam_id order by start_month) cum_exam_cnt
from temp
group by exam_id,start_month

SQL35 每月及截止当月的答题情况

with temp as (
    select start_month,uid,mau
    from(
        -- 最早登录表
        select
            uid
            ,min(date_format(start_time,'%Y%m')) start_month 
        from exam_record
        group by uid
    ) t1 right join (
        -- mau
        select
            date_format(start_time,'%Y%m') start_month
            ,count(distinct uid) mau
        from exam_record
        group by date_format(start_time,'%Y%m')
    ) t2 using(start_month)
)

select 
    start_month
    ,mau
    ,count(uid) month_add_uv
    ,max(count(uid)) over(order by start_month) max_month_add_uv
    ,sum(count(uid)) over(order by start_month)cum_sum_uv
from temp
group by start_month;

SQL36 统计有未完成状态的试卷的未完成数和未完成率

select 
    exam_id 
    ,sum(if(score is null,1,0)) as incomplete_cnt
    ,round(avg(if(score is null,1,0)),3) as incomplete_rate
from exam_record
group by exam_id
having incomplete_cnt != 0

SQL37 0级用户高难度试卷的平均用时和平均得分

select 
    uid
    ,round(avg(if(score is null,0,score))) as avg_score
    ,round(avg(if(submit_time is null,duration,timestampdiff(minute,start_time,submit_time))),1) as avg_time_took
from exam_record
left join examination_info
using(exam_id)
where difficulty='hard' and uid in (select uid
                            from user_info
                            where level =0)
group by uid

SQL38 筛选限定昵称成就值活跃日期的用户

select
    i.uid
    ,i.nick_name
    ,i.achievement
from user_info i
left join exam_record er on er.uid=i.uid
left join practice_record pr on pr.uid=i.uid
where i.nick_name like "牛客%号" and i.achievement between 1200 and 2500
group by i.uid
having  max(date_format(er.start_time,"%Y%m")) = 202109 
or max(date_format(pr.submit_time,"%Y%m")) = 202109 

SQL39 筛选昵称规则和试卷规则的作答记录

select 
    uid
    ,exam_id
    ,round(avg(score),0) avg_score
from exam_record
where uid in(
    select uid
    from user_info
    where nick_name rlike '^牛客[0-9]+号$' or nick_name rlike '^[0-9]+$'
) and exam_id in (
    select exam_id
    from examination_info
    where tag like 'c%' or tag like 'C%')
group by uid,exam_id
having avg_score is not null
order by uid,avg_score

SQL40 根据指定记录是否存在输出不同情况

• 注解
  1、sql语句中的USING($field_name)的用法
  SELECT * FROM student LEFT JOIN sc USING(sno)
  等同于SELECT * FROM student LEFT JOIN sc ON student.sno=sc.sno
  2、EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

WITH t_tag_count as (
    SELECT uid, `level`,
        COUNT(start_time) - COUNT(submit_time) as incomplete_cnt, -- 未完成数
        ROUND(
            IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0),
            3) as incomplete_rate, -- 此人未完成率
        COUNT(start_time) as total_cnt -- 总作答数
    FROM exam_record
    RIGHT JOIN user_info USING(uid)
    GROUP BY uid
)

SELECT uid, incomplete_cnt , incomplete_rate
FROM t_tag_count
WHERE EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND `level` = 0
UNION ALL
SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE NOT EXISTS (
    SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND total_cnt > 0
ORDER BY incomplete_rate;

SQL41 各用户等级的不同得分表现占比

with temp_grade as(
    select
        uid
        ,exam_id
        ,score
        ,level
        ,case
        when score>=90 then '优'
        when score>=75 then '良'
        when score>=60 then '中'
        else '差'
        end as score_grade
        ,count(level) over(partition by level) as level_count
    from exam_record
    left join user_info using(uid)
    where score is not null
    )
    
select
    level
    ,score_grade
    ,round(count(*)/level_count,3) as ratio
from temp_grade
group by level,score_grade
order by level desc,ratio desc;

SQL42 注册时间最早的三个人

select uid,nick_name,register_time 
from user_info
order by register_time 
limit 3

SQL43 注册当天就完成了试卷的名单第三页

select 
    u.uid
    ,u.level
    ,u.register_time
    ,max(r.score) max_score
from user_info u 
join exam_record r on r.uid=u.uid
join examination_info e on e.exam_id=r.exam_id
where u.job='算法' and e.tag='算法' and date_format(u.register_time,'%Y%m')=date_format(r.submit_time,'%Y%m')
group by u.uid
order by max_score desc
limit 6,3

SQL44 修复串列了的记录

select 
    exam_id
    ,substring_index(tag,',',1) tag
    ,substring_index(substring_index(tag,',',2) ,',',-1) difficulty
    ,substring_index(tag,',',-1) duration 
from examination_info
where difficulty ='';

SQL45 对过长的昵称截取处理

select 
    uid
    ,if(char_length(nick_name)>13,concat(left(nick_name,10),'...'),nick_name)
from user_info 
where char_length(nick_name)>10

SQL46 大小写混乱时的筛选统计

-- 如果转换后tag并没有发生变化，不输出该条结果
-- 所谓tag没有变换是指里面原有的tag没有新增加数据

select 
    t1.tag
    ,t2.answer_cnt
from (
    select
    tag
    ,count(start_time) as answer_cnt
    from examination_info i
    join exam_record r on i.exam_id=r.exam_id
    group by tag
) t1 join (
    select
    tag
    ,count(start_time) as answer_cnt
    from examination_info i
    join exam_record r on i.exam_id=r.exam_id
    group by tag
) t2 
on t1.tag!=t2.tag 
and upper(t1.tag)=t2.tag
and t1.answer_cnt<3