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

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

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_user
    from 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 #完整的表达

• step2：统计月活用户： 月活用户需要去重 count(distict uid) mau
• step3：统计月新增用户​​​​​​​：对每天的新增用户进行求和sum(new_day) month_add_uv

3 小结

(1)新增用户的定义——首次登录的用户，也就是把用户最早登录的那天定义为首次登录日期。

(2)截止当月的单月最大新增用户数：按照月份依次对比每个月的新增用户数的大小取大值用max()over(order by start_month)

(3)截止当月的累积用户数：按照月份依次累加新增用户数用sum()over(order by start_month)