### 解题思路：limit 计算分页的方式方法，limit （页码-1）*每页的条数,每页的条数 # 找到所有完成了考试的用户的所有考试的最高得分排名。并筛选出 求职方向为算法工程师，并且注册当天就完成了算法类试卷的人。 select A.UID, UI.level, UI.register_time, max(ER.score) max_score from ( select distinct e.UID from exam_record e join user_info u using(uid) join examination_info EI using(exam_id) where ...

1. count(A.score) scoreCnt 用来计算 每个层级下分数等级的数量，sum(count(A.score)) over(partition BY A.level) 窗口函数统计每个层级下的分数个数。 select A.level , A.score_grade, #count(A.score) scoreCnt , #sum(count(A.score)) over(partition by A.level) levelCnt , round(count(A.score)/(sum(count(A.score)) over(partition by A.level)),3)...

1. 使用with建立临时表，筛选出所有用户的等级，ID，以及对应的未完成题数和未完成率；然后通过where exists 和 where not exists 子句判断 等级为0未完成数大于2 的两种情况下的数据集合 ，由于这两个条件互斥，所以可以使用union all 进行关联取交集。 with t_tag_count as ( select UI.uid , UI.level , count(start_time)-count(submit_time) incomplete_cnt , count(start_time) exam_cnt, round(IFNULL(count(if(s...

# 单表查询然后 过滤条件，然后得到 平均分。 select ER.uid, ER.exam_id, round(avg(ER.score)) avg_score from exam_record ER join user_info UI using(uid) join examination_info EI using(exam_id) where (UI.nick_name regexp '^[0-9]+$' or UI.nick_name regexp '^牛客[0-9]+号$') and (EI.tag like 'C%' or EI.tag like 'c%') and ER.sco...

1. 首先筛选出 试卷区和题目区最近一次的活跃月份处于2021年9月的活跃用户。然后从user_Info 表中筛选用户。 # 筛选限定昵称成就值活跃日期的用户 select UI.uid, UI.nick_name, UI.achievement from user_info UI where UI.nick_name like '牛客%号' and UI.achievement between 1200 and 2500 and UI.uid in ( select A.uid from ( select ER.uid, ER.start_time exam_time, PR.submit_...

# 统计出每月新增的月活跃用户才是关键的核心所在。 # 世上无难事，只怕有心人 不关心用户ID，不关心试卷ID，只关心 每月的作答纪录情况 /* # 统计每月的 月活用户数，新增用户数，截止当月的单月最大新增用户数。 select C.start_month , C.mau , (case when C.lastMau is null then C.mau when C.lastMau is not null and C.mau>=C.lastMau then (C.mau-C.lastMau) else 0 end ) month_add_uv, max(C.mau-if(C.last...

1. 使用sum(month_cnt) 的一个窗口函数来计算每月份的累计作答次数。 # 每份试卷每月作答数和截止当月的作答数 。 select A.exam_id, A.start_month, A.month_cnt, sum(A.month_cnt) over(partition by exam_id order by start_month) cum_exam_cnt from ( select ER.exam_id , date_format(start_time,'%Y%m') start_month, count(start_time) month_cnt from exam_re...

1. 通过exam_record.exam_id分组 计算每张试卷的最大分数最小分数作为基础表数据，然后通过基础表数据计算每个用户的每张试卷的经过归化公式之后的的得分情况；有两种情况：如果试卷得分的最大值和最小值相等则表示试卷ID只有一个分数，如果不相等，去归化公式的得分；第三步，根据试卷ID，用户ID分组得到每个人的归化平均分数并按照试卷ID升序，平均归化分数降序排序。 # 计算每张高难度试卷 归化之后所得的分数，并按照归化分数升序，得到归化平均分。 select B.uid, B.exam_id, round(avg(B.min_max_score)) avg_new_score from...

1. 统计所有试卷按tag 分类得到的2020年总作答数和2021年总作答数；然后通过2020年和2021年的作答总数分别得到各自年份所在试卷内部的排名，以及增长率的变化，第三步的得到排名的变化，并记得过滤2020年和2021年作答总数均大于0 的试卷条件过滤，最后按照增长率和2021年排名降序排列。 # 统计所有年份的 上半年各类试卷的做完次数与做完的排名 select tag, exam_cnt_20, exam_cnt_21, growth_rate, exam_cnt_rank_20, exam_cnt_rank_21, cast(exam_cnt_rank_21 as signed)...

1. row_number() ,rank(),dense_rank() 三个函数的用法是不同的，row_number() 无论考试的分数是否相同 ，都会对考试的结果进行排序，并按照顺序排序，如1，2，3，4，5；rank() 按照考试的结果排序，相同的分数按照相同的结果排序，不同的分数进行跳序排列，如 1，2，2，4，5，5，5，8……；dense_rank() 按照考试的结果排序，相同的分数按照相同的结果排序，不同的分数依次排序，如1，2，3，4，4，5，5，6 /* select uid, count(score) as exam_complete_cnt from( select ui...

1. 分享思路：首先是lead() over() 窗口函数的使用得到每一次日期的下一行日期值，其次利用uid分组得到max(start_time),min(start_time) ，max(datediff(next_time,start_time)) 得到连续两次作答最大的时间窗口，统计年度作答次数总数的时候，需要统计的是count(start_time) 而不是count(distinct start_time) ; /* # 3.表间自关联 select A1.uid , timestampdiff(day,A1.submit_time,B1.submit_time) days_diff...

解题思路分析： 1. 分别由窗口函数得到 第二快和第二慢的 试卷ID和试卷提交用时 2. 由两张临时表得到第二快和第二慢的试卷提交时长的时差并join 表 examination_info 判断为时长的一半 3. 最后得到试卷ID，试卷时长，试卷发布时间，并根据试卷ID降序排列。 select EI.exam_id, EI.duration, EI.release_time from (select exam_id, time_Diff from ( #1 找出每张试卷 第二快 和第二慢的 时间纪录以及时间差 select exam_id , timestampdiff(second,star...

1. 抄别人的代码实现了我自己的东西。 select tag, uid, ranking from ( select EI.tag, ER.uid, max(ER.score) max_score, min(ER.score) min_score, row_number() over(partition by EI.tag order by max(ER.score) desc,min(ER.score) desc,ER.uid desc) ranking from exam_record ER left join examination_info EI on ER.exam_id = EI....

1. 一开始想到的 功能查询，用的user_info表左关联查询exam_record 和左关联查询practice_record ，通过 uid的level 6-7级作为限制条件 和uid分组得到 uid 的 总活跃月份数，2021年总活跃天数，2021年试卷作答活跃天数，2021年答题区活跃天数，后面想来，这样写法没有考虑到2021年 活跃日期的去重问题。遂采用第二种方法。第二种方法的功能属于是，先各自统计试卷活跃月份天数，和答题区活跃月份天数，然后使用union all 得到全部的纪录并使用exam和question标志位来标识是试卷区还是答题区。最后通过Uid和if函数判断得到所有的结...