题解 | #每个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

全部评论

相关推荐

不愿透露姓名的神秘牛友
07-07 13:46
点赞 评论 收藏
分享
nus2201602...:兄弟,你这个简历撕了丢了吧,就是一坨,去找几个项目,理解项目流程,看几遍就是你的了,看看八股就去干了,多看看牛客里别人发出来的简历,对着写,你这写的啥啊,纯一坨
点赞 评论 收藏
分享
07-07 12:47
门头沟学院 Java
码农索隆:竟然还真有卡体检报告的
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务