SQL26 每个6/7级用户活跃情况

题目主要信息：

• 统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序
• 用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）
• 试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）
• 题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）

问题拆分：

• 试卷以开始答题时间作为活跃时间，只要用户答题了就代表活跃，不管有没有完成。先筛选出用户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

代码：

select u_i.uid as uid,
       count(distinct act_month) as act_month_total,
       count(distinct case 
             when year(act_time) = 2021 
             then act_day 
             end) as act_days_2021,
       count(distinct case 
             when year(act_time) = 2021 
             and tag = 'exam' 
             then act_day 
             end) as act_days_2021_exam,
        count(distinct case
             when year(act_time) = 2021
             and tag = 'question'
             then act_day
             end) as act_days_2021_question
from user_info u_i
left join (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
      from exam_record
      union all 
      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
      from  practice_record
      ) exam_and_practice
on exam_and_practice.uid = u_i.uid
where u_i.level >= 6
group by uid
order by act_month_total desc, act_days_2021 desc