题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
select uid,ifnull(act_month_total,0) as act_month_total, ifnull(act_days_2021,0) as act_days_2021 , ifnull(act_days_2021_exam,0)as act_days_2021_exam, ifnull(act_days_2021_question,0)as act_days_2021_question from( select uid from user_info where level in(6,7))as id left join ( select uid,ifnull(count(distinct date_format(submit_time,'%Y%m')),0) as act_month_total from (select uid,submit_time from exam_record union all select uid,submit_time from practice_record) as act where uid in(select uid from user_info where level in(6,7) ) group by uid) as actmonth using(uid) left join( select uid,ifnull(count(distinct date_format(submit_time,'%Y%m%d')),0) as act_days_2021 from (select uid,submit_time from exam_record union all select uid,submit_time from practice_record) as act where uid in(select uid from user_info where level in(6,7)) and year(submit_time)='2021' group by uid) as actday using(uid) left join (select uid,count(distinct date_format(submit_time,'%y%m%d'))as act_days_2021_exam from exam_record where year(submit_time)='2021' group by uid)as examcnt using(uid) left join (select uid,count(distinct date_format(submit_time,'%y%m%d'))as act_days_2021_question from practice_record where year(submit_time)='2021' group by uid)as quescnt using(uid) order by act_month_total desc ,act_days_2021 desc