select uid,exam_id, round(avg(case when count_score >1 then ((score-min_score)/(max_score-min_score))*100 else score end )) as avg_new_score from ( select uid,exam_id,score, max(score) over w as max_score, min(score) over w as min_score, count(score) over w as count_score -- 需要增加一个判断数量的列 from exa...

select tag ,sum(case when start_year =2020 then exam_cnt else 0 end) as exam_cnt_20 ,sum(case when start_year =2021 then exam_cnt else 0 end) as exam_cnt_21 ,concat(round((sum(case when start_year =2021 then exam_cnt else 0 end)-sum(case when start_year =2020 then exam_cnt else 0 end)) /sum(case whe...

select t1.uid,t1.month_d,count(*)as total_cnt,count(t1.submit_time) as complete_cnt from -- 先求出未完成率的排名 (select uid,count(submit_time is null or null)/count(start_time) as num, PERCENT_RANK() over(order by count(submit_time is null or null)/count(start_time) ) as ranking from exam_record left join ex...

select uid,sum(num1) as exam_complete_cnt from ( select uid,date_format(start_time,'%Y%m') as month_d , count(start_time) as num1 ,count(submit_time) as num2 ,row_number() over( partition by uid order by date_format(start_time,'%Y%m') desc ) as ranking from exam_record group by uid,d...

with t as ( SELECT uid,date(start_time) as start_day,exam_id, row_number() over(partition by uid order by start_time) as ranking from exam_record ) SELECT t.uid,max(datediff(t1.start_day,t.start_day)+1) as days_window, count( t.exam_id), round((count( t.exam_id)/(datediff(max(t.start_day),min(t.star...

with t as ( SELECT uid,date(start_time) as start_day,exam_id, row_number() over(partition by uid order by start_time) as ranking from exam_record ) SELECT t.uid,max(datediff(t1.start_day,t.start_day)+1) as days_window, count( t.exam_id), round((count( t.exam_id)/(datediff(max(t.start_day),min(t.star...

select exam_id,duration,release_time from ( select exam_id,sum(case when rank_asc = 2 then time_1 else 0 end) as asc_d ,sum(case when rank_desc = 2 then time_1 else 0 end) as desc_d from ( select exam_id,timestampdiff(second,start_time,submit_time) as time_1, row_number() over(partition by exam_id o...

SELECT * FROM ( SELECT tag AS tid, uid, ROW_NUMBER() OVER (PARTITION BY tag ORDER BY MAX(score) desc ,MIN(score) DESC,uid DESC) AS ranking FROM examination_info t JOIN exam_record t1 USING (exam_id) GROUP BY tag, uid ) t WHERE ranking <= 3  知识点：1、窗口函数的执行顺序是在group by之后的，所以他是对最后的结果进行再计算2、虽有partitio...

思路：使用一维表的思维，将试卷和答题结合起来，且区分来源，这样既可以方便计算总量，也方便根据类别计算。 select uid, count(distinct act_month) as act_month_total, count(distinct case when year(act_days) = '2021' then act_days end) as act_days_2021, count(distinct case when year(act_days) = '2021' and tag = 'exam' then act_days ...

要求 1、高难度 SQL 试卷的得分平均值 大于80 且 用户等级为7difficulty = "hard" and t.level = 7 and tag = 'SQL'group by uid having avg(score)>80 2、年份要求2021，只保留有试卷完成记录的用户如果有题目练习，没有试卷完成则不保留 思路，先找出符合条件的用户uid,再根据UID计算在2021年的答题次数 select uid , count(DISTINCT t1.id,t1.submit_time) as exam_cnt , count(distinct ...

select uid,'activity1' as activity from exam_record where year(start_time)=2021 group by uid having avg(score) >=85 union all select uid,'activity2' as activity from exam_record t join examination_info t1 using(exam_id) where difficulty = 'hard' and TIMESTAMPDIFF(second,st...

select t.tid , count(distinct uid) as uv, count(*) from ( select uid,exam_id as tid from exam_record union select uid,question_id from practice_record)t group by t.tid  由于这道题要求对 试卷和练习的结果分别排序，所以上方的写法就没有办法实现了。 with t1 as (select exam_id as tid , count(distinct uid) as uv , count(*) as pv from exam_rec...

select t2.level ,count( t.uid) as level_cnt from exam_record t inner join examination_info t1 on t.exam_id =t1.exam_id inner join user_info t2 using(uid) where t.score>80 and t1.tag = "SQL" group by t2.level order by level_cnt desc,level desc 为了保险起见，可以加入 level desc,不然可能会有别的报错 这个题比上一道更简单...

类别 SQL发布当天用户为五级以上  select t.exam_id ,count(distinct t.uid) as uv ,round(avg(t.score),1) as avg_score from exam_record t inner join examination_info t1 on t.exam_id =t1.exam_id and date(t.submit_time) = date(t1.release_time) inner join user_info t2 using(uid) where t2.level>5 and t1.tag = "SQ...