题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
# select uid from user_info where level>=6，并且left join保留6/7所有人
# 活跃月份数，天数，答卷天数，答题天数...看上好复杂
# 其实就是把exam表和practice表连起来，加个标签区分一下是谁，然后挨个求。
# 注意left join可能有null，所以要ifnull一下改成0
select uid,
ifnull(count(distinct date_format(start_time,'%Y%m')),0) act_month_total,
# 活跃月数
ifnull(count(distinct if(year(start_time)=2021,date(start_time),null)),0) act_days_2021,
# 活跃天数，要过滤2021年
ifnull(count(distinct if(year(start_time)=2021 and whichone='er',date(start_time),null)),0) act_days_2021_exam,
# 活跃答卷天数，过滤2021年
ifnull(count(distinct if(year(start_time)=2021 and whichone='pr',date(start_time),null)),0) act_days_2021_question
# 活跃答题天数，过滤2021年
from (select uid from user_info where level>=6) level_6_up
# 找出6/7级大佬
left join
# 保留全部6/7级大佬，出null改0
(
select uid,start_time,'er' whichone
from exam_record
union
select uid,submit_time start_time,'pr' whichone
from practice_record
# 把exam和practice表连起来，加个标签区分是谁
) temp using(uid)
group by uid
order by act_month_total desc,act_days_2021 desc
