题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
select
ui.uid
, act_month.act_month_total
, act_day.act_day_num as act_days_2021
, count(distinct case when year(er.start_time) = 2021 then date(er.start_time) else null end) as act_days_2021_exam
, count(distinct case when year(pr.submit_time) = 2021 then date(pr.submit_time) else null end) as act_days_2021_question
from user_info as ui
left join exam_record as er on ui.uid = er.uid
left join practice_record as pr on pr.uid = er.uid
left join (
select
t1.uid
, count(distinct date_format(t1.act_date, "%Y%m")) as act_month_total
from (
select
ui.uid
, er.start_time as act_date
from user_info as ui
left join exam_record as er on ui.uid = er.uid
union all
select
ui.uid
, pr.submit_time as act_date
from user_info as ui
left join practice_record as pr on ui.uid = pr.uid
) as t1
group by t1.uid
) as act_month on act_month.uid = ui.uid
left join (
select
t2.uid
, count(distinct date(act_day)) as act_day_num
from (
select
ui.uid
, er.start_time as act_day
from user_info as ui
left join exam_record as er on ui.uid = er.uid and year(er.start_time) = 2021
union all
select
ui.uid
, pr.submit_time as act_day
from user_info as ui
left join practice_record as pr on pr.uid = ui.uid and year(pr.submit_time) = 2021
) as t2
group by t2.uid
) as act_day on act_day.uid = ui.uid
where ui.level in (6, 7)
group by ui.uid
order by 2 desc, 3 desc
