题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
- 先合并两个日期记录表,注意标识来源 `src`
- 后左连接条件去重统计,合理使用时间函数
- 最后分组排序
-
select uid, count(distinct date_format(submit_time,'%Y%m')) act_month_total, count(distinct if(year(submit_time) = 2021, date(submit_time),null)) act_days_2021, count(distinct if(src = 'exam' and year(submit_time)=2021, date(submit_time), null)) act_days_2021_exam, count(distinct if(src = 'practice' and year(submit_time)=2021, date(submit_time), null)) act_days_2021_question from user_info left join ( select uid,start_time submit_time, 'exam' src from exam_record union all select uid, submit_time, 'practice' src from practice_record ) un using(uid) where level in (6,7) group by uid order by act_month_total desc, act_days_2021 desc
