题目

建表语句

drop table if exists exam_record;
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 exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-07-02 09:01:01', '2021-07-02 09:21:01', 80),
(1002, 9001, '2021-09-05 19:01:01', '2021-09-05 19:40:01', 81),
(1002, 9002, '2021-09-02 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', null, null),
(1002, 9001, '2021-07-02 19:01:01', '2021-07-02 19:30:01', 82),
(1002, 9002, '2021-07-05 18:01:01', '2021-07-05 18:59:02', 90),
(1003, 9002, '2021-07-06 12:01:01', null, null),
(1003, 9003, '2021-09-07 10:01:01', '2021-09-07 10:31:01', 86),
(1004, 9003, '2021-09-06 12:01:01', null, null),
(1002, 9003, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1005, 9001, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 88),
(1006, 9002, '2021-09-02 12:11:01', '2021-09-02 12:31:01', 89);

解题思路

题目信息简化

计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau
结果保留两位小数

问题拆分

根据提交时间submit_time不为空筛选活跃的的人。知识点：select…from…where…
筛选每个月的平均活跃天数和总活跃人数：
- 根据月份来选择时间。知识点：date_format() 通过这个函数匹配’%Y%m’年份和月份；
- 计算用户平均活跃天数：
  - 根据不同的日期且不同的用户ID统计每个月用户的总活跃天数。知识点：distinct、count()、date_format()
  - 统计每个月用的总人数。知识点：distinct、count()
  - 总天数/总人数得到每个月的用户平均活跃天数；
- 计算每月总活跃人数，直接统计每月不同的用户id数。知识点：count()、distinct
按照月份分组group by date_format(submit_time, ‘%Y%m’) 知识点：group by …
保留两位小数。 知识点：round(x,2)

SQL代码

select date_format(submit_time, '%Y%m') as month,
       round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_active_days,
       count(distinct uid) as mau
from exam_record
where submit_time is not null
and year(submit_time) = 2021
group by date_format(submit_time, '%Y%m')

LeetCode

平均活跃天数和月活人数

题目

解题思路

SQL代码