当前位置：首页 > news >正文

牛客网SQL进阶144：每月及截止当月的答题情况

news 2025/11/26 22:07:17

每月及截止当月的答题情况_牛客题霸_牛客网

0 问题描述

基于试卷作答记录表exam_record 计算月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。

1 数据准备

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, '2020-01-01 09:01:01', '2020-01-01 09:21:59', 90),
(1002, 9001, '2020-01-20 10:01:01', '2020-01-20 10:10:01', 89),
(1002, 9001, '2020-02-01 12:11:01', '2020-02-01 12:31:01', 83),
(1003, 9001, '2020-03-01 19:01:01', '2020-03-01 19:30:01', 75),
(1004, 9001, '2020-03-01 12:01:01', '2020-03-01 12:11:01', 60),
(1003, 9001, '2020-03-01 12:01:01', '2020-03-01 12:41:01', 90),
(1002, 9001, '2020-05-02 19:01:01', '2020-05-02 19:32:00', 90),
(1001, 9002, '2020-01-02 19:01:01', '2020-01-02 19:59:01', 69),
(1004, 9002, '2020-02-02 12:01:01', '2020-02-02 12:20:01', 99),
(1003, 9002, '2020-02-02 12:01:01', '2020-02-02 12:31:01', 68),
(1001, 9002, '2020-02-02 12:01:01', '2020-02-02 12:43:01', 81),
(1001, 9002, '2020-03-02 12:11:01', null, null);

2 数据分析

select start_month ,--月活用户数  count(distinct uid) mau,  -- 新增用户sum(new_user) month_uv, -- 截止当月的最大新增用户数max(sum(new_user))over(order by start_month) max_month_uv, sum(sum(new_user))over(order by start_month) cum_sum_uv
from (select *,date_format(start_time,'%Y%m') start_month,-- 若用户的登录时间等于首次登录时间则定义为新用户，新用户被标记为1，其他标记为0。if(start_time = min(start_time)over(partition by uid),1,0) new_userfrom exam_record)t1
group by start_month;

思路分析：

step1: 定义新用户

IF(start_time = new_user_day,1,0) AS new_day # 若用户的登录时间等于首次登录时间则定义为新用户，新用户被标记为1，其他标记为0。
IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) AS new_day #完整的表达