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

with table1 as(
	select uid , 
	date_format(start_time,'%Y-%m') as act_month,
	date_format(start_time,'%Y-%m-%d') as act_days, 'exam' as type
	from exam_record
	union all
	select uid, 
	date_format(submit_time,'%Y-%m') as act_month,
	date_format(submit_time,'%Y-%m-%d') as act_days, 'question' as type
	from practice_record
) 
select ui.uid, 
	count(distinct act_month) as act_month_total,
	count(distinct
    	case 
    	when year(act_days) = '2021' then act_days else null end)
    	as act_days_2021, 
	count(distinct
    	case when year(act_days) = '2021' and type = 'exam' 
    	then act_days else null end) as act_days_2021_exam, 
	count(distinct
    	case when year(act_days) = '2021' and type = 'question' 
    	then act_days else null end) as act_days_2021_question
from table1
right join user_info as ui using(uid)
where ui.level in ('6','7')
group by uid
order by act_month_total desc, act_days_2021 desc 

1、考虑先把需要用的两个record表中的数据编成一个表table1，主键为uid，过滤出活跃月和活跃日，并用type标签区别一下。 2、从新表table1中筛选数据，利用tag区别试卷和作答的活跃天数。 3、在筛选6/7级用户时使用右链接，把user_info看做主表。