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

方法一：

-- 分两步 
-- 1. 直接 union试卷作答和练习表，求出总月活跃和2021日活跃
-- 2. 求left join用户表、试卷作答表和练习表，以uid为group by的键来统计出21年试卷活跃次数和问题表活跃次数
with temp1 as 
(
	select 
		ui.uid,
		count(distinct date_format(c_time, '%Y-%m')) act_month_total,
		count(distinct case when year(c_time)='2021' then date_format(c_time, '%Y-%m-%d') else null end ) act_days_2021
	from user_info ui 
	left join 
	(
		select uid, exam_id, start_time c_time from exam_record
		union all
		select uid, question_id exam_id,submit_time c_time from practice_record
	) a 
	on a.uid = ui.uid
	where level in (6,7)
	group by ui.uid
), temp2 as 
(
	select ui.uid,
		count(distinct date_format(er.submit_time, '%Y-%m-%d')) act_days_2021_exam,
		count(distinct date_format(pr.submit_time, '%Y-%m-%d')) act_days_2021_question
	from user_info ui 
	left join exam_record er 
	on ui.uid = er.uid 
		and year(er.submit_time) = '2021'
	left join practice_record pr
	on pr.uid = ui.uid
		and year(pr.submit_time) = '2021'
	where level in (6,7)
	group by ui.uid
)
select t1.*, t2.act_days_2021_exam, t2.act_days_2021_question from temp1 t1, temp2 t2 
where t1.uid=t2.uid
order by t1.act_month_total desc, t1.act_days_2021 desc

方法二：

方法二是做题的时候一开始想到的，但是多表连接下，然后在多种情况分开讨论，加group by，做着做着思路就乱套了，考试的时候不适合这样，主要还是我的功底不佳，还需要多练习。

select a.uid,
	count(distinct date_format(start_time, '%Y-%m')) act_month_total,
	count(distinct case when year(start_time)='2021' then date_format(start_time, '%Y-%m-%d') else null end) act_days_2021,
	count(distinct case when year(start_time)='2021' and qtype='exam' then date_format(start_time, '%Y-%m-%d') else null end) act_days_2021_exam,
	count(distinct case when year(start_time)='2021' and qtype='question' then date_format(start_time, '%Y-%m-%d') else null end) act_days_2021_question
from 
(
	select uid
	from user_info
	where level in (6, 7)
)a left join 
	(select uid, 
		exam_id, 
		start_time, 
		submit_time,
		'exam' qtype
		from exam_record
	 union all
	 select uid,
	 	question_id exam_id,
	 	submit_time start_time,
	 	submit_time,
		'question' qtype
	 	from practice_record) b
on a.uid = b.uid
group by a.uid
order by act_month_total desc,act_days_2021 desc