Tencent飞

题解

/ 注册

全部文章 / 题解（共28篇）

题解 | #月均完成试卷数不小于3的用户爱作答的类别#

select e2.tag , count(tag) as tag_cnt from exam_record e1 left join examination_info e2 using(exam_id) where e1.uid in (select e1.uid from ex...

Mysql

2022-03-15

0 402

题解 | #创建索引#

create index idx_duration on examination_info(duration); create unique index uniq_idx_exam_id on examination_info(exam_id); create fulltext index full...

Mysql

2022-03-14

0 276

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

select tag,concat(round(avg(case when TIMESTAMPDIFF(second, start_time, end_time)<=duration then TIMESTAMPDIFF(second, start_tim...

Mysql

2022-03-12

0 340

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

SELECT a.video_id , round(sum(if(end_time - start_time >= duration, 1, 0))/count(start_time ),3) as avg_comp_play_rate FROM tb_user_video_log a LE...

Mysql

2022-03-12

0 362

题解 | #得分不小于平均分的最低分#

select min(e_r.score) as min_score_over_avg from exam_record e_r join examination_info e_i on e_r.exam_id = e_i.exam_id where e_i.tag = 'SQL' ...

Mysql

2022-03-12

0 373

题解 | #统计作答次数#

select COUNT(exam_id) as total_pv, count(submit_time) as complete_pv, # 先去重exam_id 并且分数不为空 count(distinct exam_id and score is not...

Mysql

2022-03-12

0 324

题解 | #SQL类别高难度试卷得分的截断平均值#

SELECT e.tag, e.difficulty, round( (SUM(er.score) - max(er.score) - min(er.score)) / (count(er.score) - 2), 1) as clip_avg_score from examination_inf...

Mysql

2022-03-12

0 339

题解 | #更新记录（二）#

# 2021年9月1日之前开始作答的未完成记录 => 抽象出来就是开始作答时间在2021年9月1日之前且有作答，也就是有分数，只是没有完成整张试卷而已 UPDATE exam_record set score = 0 , submit_time = '2099-01-01 00:00:00' ...

Mysql

2022-03-12

0 387

题解 | #更新记录（一）#

update examination_info set tag = replace(tag, 'PYTHON', 'Python') where tag = 'PYTHON'

Mysql

2022-03-12

0 326

题解 | #插入记录（三）#

# 这个表有主键，可以用replace into # replace into examination_info (id, exam_id,tag, difficulty, duration, release_time) # values (NULL, 9003, 'SQL', 'hard', 90...

Mysql

2022-03-12

0 335