/* * 完播率：完整播放的次数 / 总播放次数 */ SELECT video_id, ROUND(COUNT(IF(play_time >= duration, video_id, NULL)) / COUNT(video_id), 3) AS avg_comp_play_rate FROM ( -- 筛选2021年有播放记录的视频和播放时长 SELECT video_id, TIMESTAMPDIFF(SECOND, start_time, end_time) AS play_time FROM tb_user_video_log WHERE YEAR(start_time) = ...

SELECT uid, level, register_time, MAX(score) OVER(PARTITION BY uid) AS max_score FROM user_info JOIN exam_record USING(uid) JOIN examination_info USING(exam_id) WHERE job = '算法' AND tag = '算法' AND DATE_FORMAT(submit_time, '%Y%m%d') = DATE_FORMAT(register_time, '%Y%m%d') ORDER BY max_score DESC LIMIT...

SELECT uid, nick_name, register_time FROM user_info ORDER BY register_time LIMIT 3

-- 统计不同用户等级的人完成的试卷数 -- 统计不同用户等级的人各得分等级试卷数 SELECT level, score_grade, ROUND( COUNT(uid) / total_cnt, 3 ) AS ratio FROM ( SELECT level, uid, ( CASE WHEN score >= 90 THEN '优' WHEN score >= 75 THEN '良' WHEN score >= 60 THEN '中' ELSE '差' END ) AS score_grade, COUNT(*) OVER(PARTITION BY level) AS...

/* * 建立新表，记录用户试卷未完成数和未完成率 */ WITH incomplete_t AS ( SELECT uid, level, COUNT(IF(submit_time IS NULL, exam_id, NULL)) AS incomplete_cnt, ROUND( IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0), 3 ) AS incomplete_rate, COUNT(start_time) AS total_cnt FROM user_info LEFT JOIN exam_record USING(uid)...

WITH valid_user AS (SELECT uid FROM user_info WHERE nick_name REGEXP '^牛客\\d+号$' OR nick_name REGEXP '^\\d+$'), valid_exam AS (SELECT exam_id FROM exam_record JOIN examination_info USING(exam_id) WHERE tag LIKE 'C%' OR tag LIKE '%c') SELECT uid, exam_id, ROUND(AVG(score)) AS avg_score FROM exam_reco...

第一次提交代码的时候直接用JOIN连接exam_record和practice_record，导致有一条用例没通过，后发现因为该用例中用户1005没有题目作答记录，故没有筛选进连接后的表中，改为left join后成功通过。 SELECT DISTINCT uid, nick_name, achievement FROM ( SELECT uid, MAX(DATE_FORMAT(start_time, '%Y%m')) OVER(PARTITION BY uid) AS recent_act_exam, MAX(DATE_FORMAT(p_r.submit_time, '%Y%m')) OV...

SELECT uid, ROUND(AVG(score), 0) AS avg_score, ROUND(AVG(cost_time), 1) AS avg_time_took FROM ( SELECT uid, IF(submit_time IS NOT NULL, TIMESTAMPDIFF(MINUTE, start_time, submit_time), duration) AS cost_time, IF(score IS NOT NULL, score, 0) AS score FROM examination_info JOIN exam_record USING(exam_i...

SELECT exam_id, incomplete_cnt, ROUND(incomplete_cnt / record_cnt, 3) AS incomplete_rate FROM ( SELECT exam_id, COUNT(start_time) AS record_cnt, COUNT(IF(submit_time IS NULL, exam_id, NULL)) AS incomplete_cnt FROM exam_record GROUP BY exam_id )record_table WHERE incomplete_cnt > 0

/* * 月活用户数：COUNT(DISTINCT uid) * 新增用户数： * 找出每个用户第一次活跃的月份，当第一次活跃的月份等于当前月份时，即该用户为新增活跃用户 * 截止当月的单月最大新增用户数： * max(month_add_uv) over(ORDER BY start_month) * 截止当月的累积用户数： * 计算列 月新增用户数 的累计和即可 */ SELECT start_month, mau, month_add_uv, MAX(month_add_uv) OVER(ORDER BY start_month) AS max_month_add_uv, SUM(mon...

sum()over()计算累加和函数 SELECT exam_id, start_month, month_cnt, SUM(month_cnt)OVER(PARTITION BY exam_id ORDER BY start_month) FROM ( SELECT exam_id, DATE_FORMAT(start_time, '%Y%m') AS start_month, COUNT(start_time) AS month_cnt FROM exam_record GROUP BY exam_id, start_month )sel_t

-- 按uid, exam_id分组，计算平均值 SELECT uid, exam_id, ROUND(AVG(min_max_score), 0) AS avg_new_score FROM ( -- 在每类试卷作答记录内执行min-max归一化 SELECT uid, exam_id, score, IF(min_score = max_score, score, (score - min_score) * 100 / (max_score - min_score)) AS min_max_score FROM ( -- 关联两张表，取difficulty='hard'的数据，找出每类试卷...

SELECT tag, exam_cnt_20, exam_cnt_21, CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) / exam_cnt_20 * 100, 1), '%') AS growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, CAST(exam_cnt_rank_21 AS SIGNED) - CAST(exam_cnt_rank_20 AS SIGNED) AS rank_delta FROM ( -- 2020年上半年各类试卷做完次数排名 SELECT tag, exam_cnt_20, RAN...

计算sql试卷未完成率，并用ROW_NUMBER()OVER()对其进行排名  SELECT uid, ROW_NUMBER()OVER(ORDER BY incomplete_rate DESC, uid DESC) AS incomplete_rank FROM ( SELECT uid, incomplete_cnt / total_cnt AS incomplete_rate FROM ( SELECT uid, COUNT(CASE WHEN tag = 'SQL' AND submit_time IS NULL THEN exam_id END) AS incomplete_cnt...