select id, job, score, r from (select *, row_number() over (partition by job order by score desc) r, count(score) over (partition by job) s from grade) t where abs(r-(s+1)/2) < 1 order by id; 中位数题型总结，有空再开坑。

with t as (select uid, exam_id, date_format(start_time, '%Y-%m-%d') day, dense_rank() over (partition by uid order by date_format(start_time, '%Y-%m-%d')) r from exam_record where year(start_time) = 2021) select a.uid, max(datediff(day2, day1) + 1) days_window, round(count(distinct exam_id, start_ti...

select tag, count(1) tag_cnt from (select uid, count(score) / count(distinct date_format(start_time, '%Y%m')) c from exam_record where score is not null group by uid) t left join exam_record r on t.uid = r.uid left join examination_info i on r.exam_id = i.exam_id where c >= 3 group by tag order b...

select uid, start_month, total_cnt, complete_cnt from (select t.uid uid, date_format(start_time, '%Y%m') start_month, count(t.uid) total_cnt, count(score) complete_cnt, rank() over (partition by t.uid order by date_format(start_time, '%Y%m') desc) r from (select uid, count(uid) - count(score) incomp...

select a.uid uid, count(distinct e.score) exam_cnt, count(distinct p.score) question_cnt from (select u.uid uid, avg(score) avg from user_info u left join exam_record r on u.uid = r.uid left join examination_info i on r.exam_id = i.exam_id where tag = 'SQL' and difficulty = 'hard' and level = 7 grou...

select * from (select exam_id tid, count(distinct uid) uv, count(1) pv from exam_record group by exam_id order by uv desc, pv desc) t1 union select * from (select question_id tid, count(distinct uid) uv, count(1) pv from practice_record group by question_id order by uv desc, pv desc) t2; 两个表排序后联合，需要...

with t1 as (select u.uid uid, sum(case when start_time and submit_time is null then 1 else 0 end) incomplete_cnt, round(sum(case when start_time and submit_time is null then 1 else 0 end)/count(1), 3) incomplete_rate from user_info u left join exam_record e on u.uid = e.uid where level = 0 group by ...

select e.exam_id exam_id, duration, release_time from (select exam_id, sum((case when m = 2 then t when k = 2 then -t else 0 end)) s from (select exam_id, row_number() over (partition by exam_id order by timestampdiff(second, start_time, submit_time)) k, row_number() over (partition by exam_id order...

select (select count(distinct a.device_id, a.date) from question_practice_detail a join question_practice_detail b on a.device_id = b.device_id and datediff(b.date, a.date) = 1) / (select count(distinct device_id, date) from question_practice_detail) avg_ret 分两个表计算用户id、日期去重后的用户数，满足次日留存即日期数相差1，示例结果应为...

select uid, sum(incomplete) incomplete_cnt, sum(complete) complete_cnt, group_concat(distinct detail separator ';') detail from (select uid, if(score is null or submit_time is null, 1, 0) incomplete, if(score is null or submit_time is null, 0, 1) complete, concat_ws(':', date(start_time), tag) detai...

select date_format(submit_time, '%Y%m') submit_month, count(1) month_q_cnt, round(count(1)/max(day(last_day(submit_time))), 3) avg_day_q_cnt from practice_record where year(submit_time) = 2021 group by submit_month union select '2021汇总' submit_month, count(1) month_q_cnt, round(count(1)/31, 3) avg_d...

select date_format(start_time, '%Y%m') month, round(count(distinct uid, date_format(start_time, '%Y%m%d'))/count(distinct uid), 2) avg_active_days, count(distinct uid) mau from exam_record where score is not null group by month having month >= '2021'; 要点在于用户活跃天数的计算。用户1在20210901活跃1次，用户2在20210901活跃...