题解 | #每个6/7级用户活跃情况#

--  计算试卷作答活跃天数
-- select ui.uid,left(start_time,10) 天,
-- 	left(start_time,7) 月,
-- 	left(start_time,4) 年,
-- 	"试卷作答"
-- from user_info ui left join exam_record er
-- 	on ui.uid=er.uid 
-- where `level` in (6,7);
-- 
--  计算答题活跃天数
-- select ui.uid,left(submit_time,10) 天,
-- 	left(submit_time,7) 月,
-- 	left(submit_time,4) 年,
-- 	'题目练习'
-- from user_info ui left join practice_record pr 
-- 	on ui.uid = pr.uid 
-- where `level` in (6,7);

# 合并表格
select uid ,
	count(distinct 月) act_month_total, # 总活跃月份数
	count(distinct if(年=2021,天,null)) act_days_2021, # 2021年活跃天数
	count(distinct if(`试卷作答`='试卷作答' and 年=2021,天,null))	act_days_2021_exam, #2021年试卷作答活跃天数
	count(distinct if(`试卷作答`='题目练习' and 年=2021,天,null)) act_days_2021_question #2021年答题活跃天数
from 
(
(select ui.uid,left(start_time,10) 天,
	left(start_time,7) 月,
	left(start_time,4) 年,
	"试卷作答"
from user_info ui left join exam_record er
	on ui.uid=er.uid 
where `level` in (6,7)) 
union
(select ui.uid,left(submit_time,10) 天,
	left(submit_time,7) 月,
	left(submit_time,4) 年,
	'题目练习'
from user_info ui left join practice_record pr 
	on ui.uid = pr.uid 
where `level` in (6,7))
) as 合并
group by uid 
order by act_month_total desc,act_days_2021 desc;