运行时间超过96% 这道题逻辑很简单，分别用两个查询找出2021和2020的submit数量和排名，然后inner join起来就好了，再算一下growth_rate和delta_rank。 唯一要注意的就是算delta_rank的时候要把两个排名cast成unsigned格式。 with a as (select tag, exam_cnt_20, rank() over (order by exam_cnt_20 desc) exam_cnt_rank_20 from (select tag, count(submit_time) as exam_cnt_20 from examina...

首先找出未完成率前50%的人。 在子查询中算出未完成率：1-count(submit_time)/count(start_time)，此处要将exam_record和examination_info两表连接来将tag限制为"SQL"。然后用percent_rank得到每个用户未完成率的百分位，将这一层查询也作为子查询。最后选出uid并限制百分位大于等于0.5. 剩下的和上一题差不多。 with incom_user as (select uid from (select uid, percent_rank() over (order by incomplete_rate) as per_ra...

用dense_rank根据年月来排名，这样在同一个月里的日期都可以有相同的名次 select temp.uid, count(temp.submit_time) as exam_complete_count from (select *, dense_rank() over (partition by uid order by date_format(start_time, "%Y-%m") desc) as ranking from exam_record) as temp where temp.ranking <= 3 group by temp.uid having co...

首先是建一个表存每个用户的最大时间窗。这里用到的方法是用窗口函数lead得到每个start_time下一个时间，然后用datediff得到两个时间的天数差，这里记得要给结果+1.然后用max得到最大时间窗。 with temp as (select dc.uid, max(dc.diff) as days_window from (select uid, start_time, datediff(lead(start_time) over (partition by uid order by start_time), start_time)+1 as diff from exam_record...

学到了一个新聚合函数nth_value() with temp as (select exam_id, nth_value(timestampdiff(second, start_time, submit_time), 2) over(partition by exam_id order by timestampdiff(second, start_time, submit_time) desc) - nth_value(timestampdiff(second, start_time, submit_time), 2) over(partition by exam_i...

嵌套了两次，首先根据tag和uid分组并找出每组最大值最小值，然后根据最大值最小值以及uid使用row_number赋予排序标签，最后限制前三名 select * from (select tag, uid, row_number() over (partition by tag order by max_grade desc, min_grade desc, uid desc) as ranking from (select tag, uid, max(score) as max_grade, min(score) as min_grade from examination_info joi...

首先建一个新表temp合并exam_record和practice_record并加了一个label字段方便区分两个表。 with temp as (select uid, exam_id as try_id, start_time as try_time, "exam" as label from exam_record union all select uid, question_id as try_id, submit_time as try_time, "prac" as label from practice_record) 然后连接temp和user_info，这里用的是r...

我无语了，他这计数标准根本没说清楚。但是对2021年这个条件的限制很值得思考，我没太整明白。。。 with temp as (select uid from exam_record inner join examination_info using(exam_id) inner join user_info using(uid) where level = 7 and tag = "SQL" and difficulty = "hard" group by uid having avg(score) > 80) select er.uid, count(distinct er.ex...

整体挺简单的，需要关注的是timestampdiff这个function，别的像是datediff和timediff都是第一个输入时间减去第二个输入时间，但是它是第二个输入时间减去第一个输入时间。 select distinct uid, "activity1" as act from exam_record where score >= 85 union all select distinct uid, "activity2" as act from examination_info join exam_record using(exam_id) where difficulty...

这道题的坑在于两个order by的query不能用union连接，只可以连接起来后再统一进行排序。所以我把原本的查询变成子查询，套个壳就好了 select a.tid, a.uv, a.pv from (select exam_id as tid, count(distinct uid) as uv, count(start_time) as pv from exam_record group by tid order by uv desc, pv DESC) as a union all select b.tid, b.uv, b.pv from (select question_id...

这题有个大坑,他没说level_cnt一样的情况下level怎么排序，实际上应该按level_cnt和level降序排列。 select level, count(distinct uid) as level_cnt from user_info join exam_record using(uid) where score > 80 and exam_id = 9001 group by level order by level_cnt desc, level desc

这道题把逻辑理顺就很好做了。 首先是将三张表连接起来，examination_info和exam_record根据exam_id还有release_time和start_time的配对来进行连接。这里要注意的是只需要对两个时间的日期进行匹配。exam_record和user_info根据uid连接。 然后用where限制level>5并根据exam_id进行分组。选出exam_id并用count和avg算出每组作答人数和平均分。最后order by根据作答人数降序排列，根据平均分升序排列。 select er.exam_id, count(distinct er.uid) as uv, ...

这道题本身挺简单，但是这个月均完成数的定义很模糊。这道题里的定义是 所有完成数/有完成题的月数。 首先是用subquery将月均完成数大于等于3的用户找出来，然后通过join将exam_record和examination连接并限制uid为月均完成数大于等于3的用户。 然后根据tag分组，select出tag并用count算出每组作答数量。最后根据作答数量倒序排序。 select ei.tag, count(er.start_time) as tag_cnt from exam_record as er inner join (select uid from exam_record gro...

首先建一个新表temp，连接exam_record和examination_info提取出uid等所需字段，用concat_ws连接start_time和tag两字段作为sub_detail字段，start_time要format成%Y-%m-%d形式。这里要注意的是，题目中要的是所有作答过的试卷信息而不是完成了的试卷信息。 with temp as (select er.uid, er.start_time, er.submit_time, concat_ws( ":", date_format(er.start_time,"%Y-%m-%d"), ...