select t1.tag,2020_cnt,2021_cnt, # 同比增长率 （21年-20年）/20年 concat(round((2021_cnt-2020_cnt)/2020_cnt*100,1),'%') growth_rate,2020_rk,2021_rk, # 排名变化：21年排名-20年排名，要注意rank值相减时的问题 if(2021_rk>=2020_rk,2021_rk-2020_rk,-(2020_rk-2021_rk))rank_delta from # 2021年各类试卷完成数以及排名 (select tag, count(submit_time) 202...

select ui.uid, ifnull(act_month_total,0) act_month_total, ifnull(act_days_2021,0) act_days_2021, ifnull(act_days_2021_exam,0)act_days_2021_exam, ifnull(act_days_2021_question,0)act_days_2021_question from user_info ui left join # 2021年试卷作答活跃天数 (select uid,count(distinct date(start_time)) act_days_20...

select uid,exam_cnt, #左连接输出有完成书卷记录的用户 if(question_cnt>=0,question_cnt,0) question_cnt from # 2021年用户完成试卷的次数 (select uid,count(submit_time) exam_cnt from exam_record where year(submit_time)=2021 and length(submit_time )>0 and uid in # 计算高难度SQL试卷得分平均值大于80并且是7级的用户 (select uid from exam_record joi...

select uid, # 取用户连续2次作答试卷的最大值 max(days_window) days_window, # 用户累计作答的试卷数/用户作答试卷的最大时间窗=用户平均每天作答的试卷数 # 用户平均每天作答的试卷数*用户连续2次作答试卷的最大值=avg_exam_cnt round(max(exam_cnt)/max(max_days_window)*max(days_window),2) avg_exam_cnt from (select uid,date(start_time) s_day, # 通过偏移窗口函数计算连续2次作答试卷的时间窗 #第二次作答试卷的时间 lead(d...

select author_id,author_level, count(distinct answer_date) days_cnt from (select author_id,answer_date,author_level, # 连续问题出现时，选择用日期减排序，如果是连续，相减结果是相同的 answer_date-row_number()over(partition by author_id order by answer_date) new_rk from author_tb join answer_tb using(author_id))t group by author_id,...

select in_time, count(uid) as dau, round(count(if(new_record=1,uid,null))/count(uid),2) as uv_new_ratio from (select uid,in_time, if(min(in_time)over(partition by uid)=in_time,1,0) as new_record from (select uid,date(in_time) in_time from tb_user_log union select uid,date(out_time) in_time from tb_u...

# 按用户新增日期分组，当天没有新增时，则不输出 select new_day, # new_day+1天即为新增用户次日的活跃情况 round(count(if(in_time=new_day+1,uid,null))/count(if(in_time=new_day,uid,null)),2) new_u from # 在用户活跃表中求用户新增时间new_day (select distinct uid, in_time, min(in_time)over(partition by uid order by in_time) as new_day from # 选择并集，创建用户活跃表t ...

with a as # 先计算所有带大小写字母的tag的作答数，存临时表a (select tag,count(start_time) answer_cnt from exam_record er join examination_info ei on ei.exam_id=er.exam_id where tag REGEXP '[a-zA-Z]' group by tag), # 计算所有带大小写字母的tag的作答数小于3的数，存临时表b b as( select tag,count(start_time) answer_cnt from exam_record er join exami...

select exam_id, dt, cnt_m, sum(cnt_m) over ( partition by exam_id order by dt) as cum_cnt # 按exam_id分组，按时间排序后计算累计值 from (select exam_id, dt,cnt_m from (select exam_id,date_format (start_time, '%Y%m') dt, count(start_time) as cnt_m from exam_record group by exam_id, date_format (start_time, '%Y%m') )...

select i.uid uid,if(m.cnt_m is null,0,m.cnt_m) as act_month_total , if(d.cnt_d is null,0,d.cnt_d) as act_days_2021, if(e.exam_cnt is null,0,e.exam_cnt) as act_days_2021_exam, if(q.question_cnt is null,0,q.question_cnt) as act_days_2021_question from user_info i left join (select uid,count(distinct d...