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

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

http://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1

问题梳理

主问题：每个6/7级用户活跃情况

1. 筛选条件：6/7级用户，即：user_info.level >= 6

2. 指标：

（1）总活跃月份数：做过试卷或做过题目的月份数（无年份限定）

（2）2021年活跃天数：2021年（限定年份），做过试卷或做过题目的天数

（3）2021年试卷作答活跃天数：2021年（限定年份），做过试卷的天数

（4）2021年答题活跃天数：2021年（限定年份），做过题目的天数

3. 返回：uid, act_month_total, act_days_2021, act_days_2021_exam, act_days_2021_question

思路

1. 合并试卷记录的活跃日期与答题记录的日期，先各自去重（group by）再合并（union all）

因为要计算总活跃月份数和某年活跃天数，即同时考虑exam和question，注意需要额外标注exam和question

2. 筛选出目标用户(user_info.level >= 6)，连接上述的合并表，创建出临时表(with xxx as ...)。

3. 根据临时表，先计算总活跃月份数，得到表t1 (uid, act_month_total)

这里总活跃月份数要考虑非2021年的记录

4. 根据临时表，再计算其余三个指标，得到表t2 (uid, act_days_2021, act_days_2021_exam, act_days_2021_question)

5. 以表t1为主表，通过uid左连接表t2，注意连接后t2的记录空值转0

表t1的记录了所有用户，t2做了年份条件筛选会导致用户记录不完整，因此需要以t1为主表进行左连接

代码

with tmp as (
    select t1.uid,
           date(t2.start_time) as dt,
           1                   as tp
    from user_info as t1
             left join exam_record as t2 on t1.uid = t2.uid
    where level >= 6
    group by t1.uid, date(t2.start_time)
    union all
    select t1.uid,
           date(t2.submit_time) as dt,
           2                    as tp
    from user_info as t1
             left join practice_record as t2 on t1.uid = t2.uid
    where level >= 6
    group by t1.uid, date(t2.submit_time)
)

select t1.uid,
       t1.act_month_total,
       ifnull(t2.act_days_2021, 0)          as act_days_2021,
       ifnull(t2.act_days_2021_exam, 0)     as act_days_2021_exam,
       ifnull(t2.act_days_2021_question, 0) as act_days_2021_question
from (
         select uid,
                count(distinct date_format(dt, '%Y%m')) as act_month_total
         from tmp
         group by uid
     ) as t1
         left join (
    select uid,
           count(distinct dt)    as act_days_2021,
           sum(if(tp = 1, 1, 0)) as act_days_2021_exam,
           sum(if(tp = 2, 1, 0)) as act_days_2021_question
    from tmp
    where year(dt) = 2021
    group by uid
) as t2 on t1.uid = t2.uid
order by t1.act_month_total desc, t2.act_days_2021 desc;