每个6/7级用户活跃情况

现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

id	uid	nick_name	achievement	level	job	register_time
1	1001	牛客1号	3100	7	算法	2020-01-01 10:00:00
2	1002	牛客2号	2300	7	算法	2020-01-01 10:00:00
3	1003	牛客3号	2500	7	算法	2020-01-01 10:00:00
4	1004	牛客4号	1200	5	算法	2020-01-01 10:00:00
5	1005	牛客5号	1600	6	C++	2020-01-01 10:00:00
6	1006	牛客6号	2600	7	C++	2020-01-01 10:00:00

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2021-09-01 06:00:00
2	9002	C++	easy	60	2021-09-01 06:00:00
3	9003	算法	medium	80	2021-09-01 10:00:00

试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：

uid	exam_id	start_time	submit_time	score
1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	78
1001	9001	2021-09-01 09:01:01	2021-09-01 09:31:00	81
1005	9001	2021-09-01 19:01:01	2021-09-01 19:30:01	85
1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:02	85
1006	9003	2021-09-07 10:01:01	2021-09-07 10:21:59	84
1006	9001	2021-09-07 10:01:01	2021-09-07 10:21:01	81
1002	9001	2020-09-01 13:01:01	2020-09-01 13:41:01	81
1005	9001	2021-09-01 14:01:01	(NULL)	(NULL)

题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

uid	question_id	submit_time	score
1001	8001	2021-08-02 11:41:01	60
1004	8001	2021-08-02 19:38:01	70
1004	8002	2021-08-02 19:48:01	90
1001	8002	2021-08-02 19:38:01	70
1004	8002	2021-08-02 19:48:01	90
1006	8002	2021-08-04 19:58:01	94
1006	8003	2021-08-03 19:38:01	70
1006	8003	2021-08-02 19:48:01	90
1006	8003	2020-08-01 19:38:01	80

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。由示例数据结果输出如下：

uid	act_month_total	act_days_2021	act_days_2021_exam	act_days_2021_question
1006	3	4	1	3
1001	2	2	1	1
1005	1	1	1	0
1002	1	0	0	0
1003	0	0	0	0

解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

示例1

输入:

drop table if exists examination_info,user_info,exam_record,practice_record;
CREATE TABLE examination_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
    tag varchar(32) COMMENT '类别标签',
    difficulty varchar(8) COMMENT '难度',
    duration int NOT NULL COMMENT '时长',
    release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE user_info (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int UNIQUE NOT NULL COMMENT '用户ID',
    `nick_name` varchar(64) COMMENT '昵称',
    achievement int COMMENT '成就值',
    level int COMMENT '用户等级',
    job varchar(32) COMMENT '职业方向',
    register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE practice_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    question_id int NOT NULL COMMENT '题目ID',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE exam_record (
    id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid int NOT NULL COMMENT '用户ID',
    exam_id int NOT NULL COMMENT '试卷ID',
    start_time datetime NOT NULL COMMENT '开始时间',
    submit_time datetime COMMENT '提交时间',
    score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;

INSERT INTO user_info(uid,`nick_name`,achievement,level,job,register_time) VALUES
  (1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
  (1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
  (1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
  (1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
  (1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
  (1006, '牛客6号', 2600, 7, 'C++', '2020-01-01 10:00:00');

INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
  (9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
  (9002, 'C++', 'easy', 60, '2021-09-01 06:00:00'),
  (9003, '算法', 'medium', 80, '2021-09-01 10:00:00');

INSERT INTO practice_record(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1006, 8002, '2021-08-04 19:58:01', 94),
(1006, 8003, '2021-08-03 19:38:01', 70),
(1006, 8003, '2021-08-02 19:48:01', 90),
(1006, 8003, '2020-08-01 19:38:01', 80);

INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 78),
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1005, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:59', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 81),
(1002, 9001, '2020-09-01 13:01:01', '2020-09-01 13:41:01', 81),
(1005, 9001, '2021-09-01 14:01:01', null, null);

输出:

1006|3|4|1|3
1001|2|2|1|1
1005|1|1|1|0
1002|1|0|0|0
1003|0|0|0|0

原站题解

上次编辑到这里，代码来自缓存点击恢复默认模板

Mysql 解法, 执行用时: 36ms, 内存消耗: 6384KB, 提交时间: 2021-12-31


# select uid ,month(start_time) act_month
# from exam_record
# union all
# select uid,month(submit_time) act_month
# from practice_record

select t1.uid, count(distinct act_month) act_month_total,
count(distinct case when year(act_day)=2021 then act_day end) act_days_2021,
count(distinct case when left(tag,1)=9 and year(act_day)=2021 then act_day end) act_days_2021_exam,
count(distinct case when left(tag,1)=8 and year(act_day)=2021 then act_day end) act_days_2021_question
from user_info t1
left join (select uid ,
      date_format(start_time,'%Y%m') act_month,
      date_format(start_time,'%Y%m%d') act_day,
      exam_id tag
from exam_record
union all
select uid,
      date_format(submit_time,'%Y%m') act_month,
      date_format(submit_time,'%Y%m%d') act_day,
      question_id tag
from practice_record) t2
on t1.uid=t2.uid
where t1.uid in (select uid 
from user_info
where level in (6,7))
group by uid
order by act_month_total desc,act_days_2021 desc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2021-12-14

select
    a.uid,
    case
    when act_month_total is null then 0
    else act_month_total
    end as 'act_month_total',
    case
    when act_days_2021 is null then 0
    else act_days_2021
    end as 'act_days_2021',
    case
    when act_days_2021_exam is null then 0
    else act_days_2021_exam
    end as 'act_days_2021_exam',
    case 
    when act_days_2021_question is null then 0
    else act_days_2021_question
    end as 'act_days_2021_question'
from (
        select uid 
        from user_info
        where level >= 6
    ) as a
    left join (
        select
            a.uid,
            count(distinct date_format(act_time, '%Y%m')) as 'act_month_total',
            count(distinct case
                  when year(act_time)=2021 then date_format(act_time, '%Y%m%d') 
                  end) as 'act_days_2021',
            count(distinct case
                  when year(act_time)=2021 and label='exam' then date_format(act_time, '%Y%m%d')
                  end) as 'act_days_2021_exam',
            count(distinct case when year(act_time)=2021 and label='question' then date_format(act_time, '%Y%m%d')
                  end) as 'act_days_2021_question' 
        from (
            select uid, exam_id as id, start_time as 'act_time', 'exam' as 'label'
            from exam_record
            where uid in (
                select uid
                from user_info
                where `level` >= 6
            )
            union 
            select uid, question_id as id, submit_time as 'act_time', 'question' as 'label'
            from practice_record
            where uid in (
                select uid
                from user_info
                where `level` >= 6
            )
        ) as a
        group by a.uid
    ) as b on a.uid = b.uid
order by act_month_total desc, act_days_2021 desc;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2021-12-12

select tmp1.uid as 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=6 or level=7
)  as tmp1
	left join 
(
	select uid ,count(distinct(date_format(submit_time,'%Y%m'))) as act_month_total
	from 
	(
		select uid , submit_time
		from exam_record 
		
		union all
		
		select uid,submit_time
		from practice_record 
	) as tmp
	group by tmp.uid
) as tmp2 on (tmp1.uid = tmp2.uid)

left join 

(
select uid ,count(distinct(dayofyear(submit_time))) as act_days_2021
from 
	(
		select uid , submit_time
		from exam_record 
		
		union all
		
		select uid,submit_time
		from practice_record 
	) as tmp
where year(submit_time)='2021'
group by tmp.uid
) as tmp3 on(tmp2.uid = tmp3.uid)

left join 
(
select user_info.uid ,count(distinct(dayofyear(submit_time))) as act_days_2021_exam
from user_info,exam_record
where user_info.uid=exam_record.uid
	and year(exam_record.submit_time)='2021'
group by user_info.uid
) as tmp4 on( tmp3.uid = tmp4.uid)

left join 

(
select user_info.uid ,count(distinct(dayofyear(submit_time))) as act_days_2021_question
from user_info,practice_record
where user_info.uid = practice_record.uid
	and year(practice_record.submit_time)='2021'
group by user_info.uid
) as tmp5 on (tmp4.uid = tmp5.uid)
order by act_month_total desc , act_days_2021 desc
;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2022-02-09

select user_info.uid,
        count(distinct act_month) as act_month_total,
        count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
        count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
        count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question
from (
     SELECT uid,exam_id as ans_id,start_time as act_time,date_format(start_time,'%Y%m') as act_month, 
      date_format(start_time,'%Y%m%d') as act_day,'exam' as tag from exam_record
     UNION ALL
     select uid,question_id as ans_id,submit_time as act_time,date_format(submit_time,'%Y%m') as act_month,
      date_format(submit_time,'%Y%m%d') as act_day,'question' as tag from practice_record 
     ) as total 
right join user_info on total.uid=user_info.uid
where user_info.level in (6,7)
group by user_info.uid
order by act_month_total desc,act_days_2021 desc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2022-01-27

select
    u_i.uid,
    count(distinct  date_format(start_time, '%Y%m')) act_month_total,
    count(distinct if(YEAR(start_time)=2021,date_format(start_time, '%Y%m%d'),null)) act_days_2021,
    count(distinct if(YEAR(start_time)=2021 and tag = '2',date(start_time),null)) act_days_2021_exam,
    count(distinct if(YEAR(start_time)=2021 and tag = '1',date(start_time),null)) act_days_2021_queston
from (select
          uid,
          submit_time start_time,
          '1' tag
      from practice_record
      union
      select
          uid,
          start_time,
          '2' tag
      from exam_record) mon
right join user_info u_i
on u_i.uid = mon.uid
where u_i.level >5
group by
    uid
order by
    act_month_total desc ,act_days_2021 desc;

上一题

下一题

详情