找工作好难玉玉了😇

分类

/ 注册

全部文章（共97篇）

题解 | #每个题目和每份试卷被作答的人数和次数#

SELECT * FROM ( SELECT exam_id tid, COUNT(DISTINCT uid) uv, COUNT(start_time) pv FROM exam_record GROUP BY tid ORDER BY uv DESC, pv DESC ) m ...

2024-01-10

0 271

题解 | #作答试卷得分大于过80的人的用户等级分布#

SELECT level, COUNT(DISTINCT uid) level_cnt FROM exam_record a JOIN user_info b USING(uid) JOIN examination_info c USING(exam_id) WHERE ...

2024-01-09

0 201

题解 | #试卷发布当天作答人数和平均分#

SELECT b.exam_id exam_id, COUNT(DISTINCT uid) uv, ROUND(AVG(score), 1) avg_score FROM exam_record c JOIN user_info a USING(uid) JOI...

2024-01-08

0 225

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

SELECT tag, COUNT(tag) tag_cnt FROM exam_record a JOIN examination_info b USING(exam_id) WHERE uid in (SELECT uid FROM exam_record WHERE...

2024-01-04

0 316

题解 | #统计作答次数#

SELECT COUNT(start_time) total_pv, COUNT(score) complete_pv, # COUNT(DISTINCT (CASE WHEN score is null THEN null ELSE exam_id END)) c...

2024-01-02

0 273

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

SELECT tag, difficulty, ROUND((SUM(score)-MIN(score)-MAX(score))/(COUNT(score)-2), 1) FROM examination_info a JOIN exam_record b # using(exam_id) ON a...

2024-01-02

0 243

题解 | #创建索引#

CREATE INDEX idx_duration ON examination_info(duration); CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id); # CREATE FULLTEXT INDEX fu...

2024-01-02

0 253

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

WITH temp AS( SELECT a.video_id AS video_id, (end_time - start_time) AS real_dur, duration FROM tb_user_video_log a LEFT JOIN tb_video...

2023-12-12

0 278

题解 | #未完成试卷数大于1的有效用户#

SELECT uid, COUNT(if(submit_time, NULL, 1)) incomplete_cnt, COUNT(if(submit_time, 1, NULL)) complete_cnt, LEFT(REPLACE(GROUP_CONCAT(DISTINCT CONCAT_...

2023-10-13

1 283

题解 | #月总刷题数和日均刷题数#

SELECT submit_month, month_q_cnt, ROUND(month_q_cnt/day_month, 3) avg_day_q_cnt FROM ( SELECT DATE_FORMAT(submit_time, '%Y%m') submit_month, C...

2023-10-06

0 297