本文已参与「新人创作礼」活动，一起开启掘金创作之路

First-thinking

试卷以开始答题时间作为活跃时间，只要用户答题了就代表活跃，不管有没有完成。先筛选出用户ID，开始答题时间作为活跃时间，开始答题的月份作为活跃月份，开始答题的日期作为活跃天，标记tag为'exam'。select uid, start_time as act_time, date_format(start_time, '%Y%m') as act_month, date_format(start_time, '%Y%m%d') as act_day, 'exam' as tag
题目以提交时间作为活跃时间，因为表格中没有开始时间。先筛选出用户ID，提交时间作为活跃时间，提交的月份作为活跃月份，提交的日期作为活跃天，标记tag为'question'。select uid, submit_time as act_time, date_format(submit_time, '%Y%m') as act_month, date_format(submit_time, '%Y%m%d') as act_day, 'question' as tag
将上述两个筛选结果合并作为新表exam_and_practice。知识点：union all
活跃信息在新表exam_and_practice中，用户等级信息在user_info中，因此在表user_info右边连接新表，以uid为准。知识点：left join...on... 使用left join是因为要使没有活跃的用户在连接后的表中显示活跃信息为空而不是消失。
从连接后的表中筛选出等级大于等于6的用户，然后统计每个用户的活跃信息：
- 每个用户都要统计，因此要对uid分组。知识点：group by
- 统计每个uid下在连接后的表中的不同的活跃月份数。count(distinct act_month) as act_month_total 知识点：distinct、count()
- 统计每个uid下在连接后的表中不同的活跃天数，前提是活跃时间的年份等于2021. count(distinct case when year(act_time) = 2021 then act_day end) as act_days_2021 知识点：case when...then...end
- 统计每个id在连接后表中活跃时间的年份等于2021且标签为'exam'的不同活跃天数。count(distinct case when year(act_time) = 2021 and tag = 'exam' then act_day end) as act_days_2021_exam 知识点：case when...and...then...end
- 统计每个id在连接后表中活跃时间的年份等于2021且标签为'question'的不同活跃天数。count(distinct case when year(act_time) = 2021 and tag = 'question' then act_day end) as act_days_2021_question 知识点：case when...and...then...end
对选择的信息按照总活跃月份数、2021年活跃天数降序排序输出。order by act_month_total desc, act_days_2021 desc 知识点：order by

Seconed-solve

with exam_and_practice_record as(
SELECT
	uid,
	start_time, -- 活跃时间
	DATE_FORMAT( start_time, '%Y%m' ) act_month, -- 活跃月份
	DATE_FORMAT( start_time, '%Y%m%d' ) act_days, -- 活跃天数
	'exam' tag 
FROM
	exam_record
union all
SELECT
	uid,
	submit_time,
	DATE_FORMAT( submit_time, '%Y%m' ) act_month,
	DATE_FORMAT( submit_time, '%Y%m%d' ) act_days,
	'practice' tag 
FROM
	practice_record 

)
select
b.uid,
count(distinct a.act_month) act_month_total,
count(distinct case when year(a.start_time) = 2021  then act_days end) act_days_2021,
count(distinct case when year(a.start_time) = 2021  and tag = 'exam' then act_days end) act_days_2021_exam,
count(distinct case when year(a.start_time) = 2021   and tag = 'practice'then act_days end) act_days_2021_question

from

exam_and_practice_record  a right join user_info b on a.uid = b.uid
where b.level >= 6

group by b.uid
order by act_month_total desc,act_days_2021 desc

每个6/7级用户活跃情况(牛客网)

First-thinking

Seconed-solve

Third-url