题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
思路：先把exam_record与practice_record两个表union起来，对每个用户的信息进行统计，形成一个子表，再用user_info左连接该子表，最后让null值为0就得到了想要的结果。
复盘：
1.最初没有认真审题，未注意到需要对每个6/7级的用户进行统计，而是把exam_record与practice_record两个表union起来，统计了union之后的表中6/7级用户的信息。
2.发现问题之后再想怎么改，最后发现不用大改，之前是统计union之后的表中6/7级用户的信息，现在统计union之后的表中所有用户的信息，再用user_info与其左连接即可
3.做的太匆忙，忘了筛选出user_info表中6/7级用户的信息。
select uid,if(act_month_total is null,0,act_month_total) as act_month_total,if(act_days_2021 is null,0,act_days_2021) as act_days_2021,if(act_days_2021_exam is null,0,act_days_2021_exam) as act_days_2021_exam,if(act_days_2021_question is null,0,act_days_2021_question) as act_days_2021_question from user_info left join (select
uid,
count(distinct date_format(sb_time, '%Y%m')) as act_month_total,
count(
distinct if(
year(sb_time) = 2021,
date_format(sb_time, '%Y%m%d'),null
)
) as act_days_2021,
count(
distinct if(
year(sb_time) = 2021
and ep_id like '9%',
date_format(sb_time, '%Y%m%d'),
null
)
) as act_days_2021_exam,
count(
distinct if(
year(sb_time) = 2021
and ep_id like '8%',
date_format(sb_time, '%Y%m%d'),
null
)
) as act_days_2021_question
from
(
select
uid,
exam_id as ep_id,
start_time as sb_time
from
exam_record
union
select
uid,
question_id as ep_id,
submit_time as sb_time
from
practice_record
) as t0
group by
uid
) as t1 using(uid) where level=6 or level=7 order by act_month_total desc,act_days_2021 desc
字节跳动二面107人在聊
查看6道真题和解析