找工作好难玉玉了😇

分类

/ 注册

全部文章（共97篇）

题解 | #对试卷得分做min-max归一化#

# 先对每个试卷进行分数的归一化计算，然后按用户和试卷进行分组，计算均值，只保留整数 WITH temp_0 AS( SELECT b.id, b.uid, a.exam_id, b.score, MAX(score) OVER(PARTITION BY exam_id) ...

2024-01-20

0 189

题解 | #试卷完成数同比2020年的增长率及排名变化#

# 把21年和22年的先都放成示例的样子 # 临时表0，把各类试卷的20年和21年上半年的完成数提取出来 WITH temp_0 AS( SELECT tag, SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN...

2024-01-19

0 228

题解 | #未完成率较高的50%用户近三个月答卷情况#

# 先找到sql试卷未完成率较高的前一半用户，筛选出level是6、7的 # 这些用户的有作答记录的近三个月，分组统计每月的答卷数和完成数 WITH temp AS ( # 联结表 SELECT a.uid, c.start_time, c.score, a.level, b.ta...

2024-01-18

0 241

题解 | #近三个月未完成试卷数为0的用户完成情况#

# 检查每个用户近三个月的作答月份，再检查这些月份的作答数和完成数是否一致，不一致按完成数输出 WITH temp AS ( SELECT uid, DATE_FORMAT(start_time, "%Y-%m") start_month, submit_t...

2024-01-18

0 221

题解 | #连续两次作答试卷的最大时间窗#

SELECT uid, days_window, ROUND((total/max_diff) * days_window, 2) AS avg_exam_cnt FROM ( SELECT uid, COUNT(start_time) total, ...

2024-01-15

0 271

题解 | #第二快/慢用时之差大于试卷时长一半的试卷#

# 计算时差 # 按时差排两个序，正序、倒序 # 链接分组查询 WITH TD AS ( SELECT exam_id, TIMESTAMPDIFF(MINUTE, start_time, submit_time) AS cost, dur...

2024-01-15

0 198

题解 | #每类试卷得分前3名#

SELECT * FROM (SELECT a.tag tid, b.uid uid, ROW_NUMBER() OVER(PARTITION BY a.tag ORDER BY MAX(b.score) DESC, MIN(b.score) DESC, MAX(b.u...

2024-01-14

0 190

题解 | #每个6/7级用户活跃情况#

select u_i.uid as uid, count(distinct act_month) as act_month_total, # 所有的活跃月 count(distinct case when year(act_time) = 20...

2024-01-12

0 204

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

SELECT a.uid, a.exam_cnt, COALESCE(b.question_cnt, 0) FROM ( # 试卷完成情况 SELECT uid, COUNT(score) exam_cnt FROM exam_record WHER...

2024-01-12

0 215

题解 | #分别满足两个活动的人#

SELECT * FROM (SELECT uid, 'activity1' activity FROM exam_record WHERE YEAR(submit_time) = 2021 GROUP BY uid HAVING MIN(score) >= 85) a UNION AL...

2024-01-10

0 242