如何分析周活跃率？

【题目】

有两张表：

1）“用户登录信息表”，包含2个字段：用户id、登录日期。

2）“已注册用户信息表”，包含2个字段：用户id、注册日期。

【问题】计算周活跃用户率。

【解题思路】

1. 活跃用户率

“活跃用户率”是观察用户活跃度的常见指标，一般指所有注册用户中活跃用户的比例。当活跃周期设定为“周”时，计算的就是“周活跃用户率”。

周活跃用户率 = 周活跃用户数 / 当前注册用户数

值得注意的是，“当前注册用户数”指截止本周最后一天之前注册的用户数量。因为，在这之后注册的用户必然不会在本周活跃。

2. 日期处理

为了获取“用户登录信息表”中“登录日期”对应周的信息，我们需要对该字段进行处理。

函数year的作用是日期的年份，使用方法是：

year(日期字段名)

函数week的作用是返回一个int型的数值，代表指定日期在所在年的周数信息，使用方法是：

week(日期字段名,参数)

其中，“参数”不填写的情况下，默认值为0，代表周日为本周第一天；“参数”为1时，代表周一为本周第一天；“参数”为2时，代表周二为本周第一天；以此类推。

select 用户id,
       登录日期,
       year(登录日期) as 年份,
       week(登录日期,1) as 周数
from 用户登录信息表;

返回结果如下：

该结果记为表t1，在此基础上计算每周活跃用户数。

select t1.年份,
       t1.周数,
       count(distinct 用户id) as 活跃用户数
from
(
select 用户id,
       登录日期,
       year(登录日期) as 年份,
       week(登录日期,1) as 周数
from 用户登录信息表
) as t1
group by t1.年份,t1.周数;

返回结果如下：

该结果记为表t2，记录的是每周的活跃用户数。

对“已注册用户信息表”的“注册日期”字段进行处理：

select 用户id,
       注册日期,
       year(注册日期) as 年份,
       week(注册日期,1) as 周数
from 已注册用户信息表;

返回结果如下：

该结果记为表t3。

为了后面计算“当前注册用户数”，需要获取“用户登录信息表”中的“活跃最大年份”，以及对应的“活跃最大周数”。

select year(max(登录日期)) as 活跃最大年份,
       week(max(登录日期),1) as 活跃最大周数
from 用户登录信息表;

返回结果如下：

该结果记为表t4。

3. 多表联结

为了计算每个活跃周的“当前注册用户数”，将表t3和表t4进行联结。

select count(distinct t3.用户id) as 当前注册用户数
from
(
select 用户id,
       注册日期,
       year(注册日期) as 年份,
       week(注册日期,1) as 周数
from 已注册用户信息表
) as t3
left join
(
select year(max(登录日期)) as 活跃最大年份,
       week(max(登录日期),1) as 活跃最大周数
from 用户登录信息表
) as t4 on 1 = 1
where t3.年份 < t4.活跃最大年份 or (t3.年份 = t4.活跃最大年份 and t3.周数 <= t4.活跃最大周数);

返回结果如下：

该结果记为表t5。

最后，将表t2和表t5联结，计算最终结果：

select t2.年份,
       t2.周数,
       t2.活跃用户数,
       t5.当前注册用户数,
       t2.活跃用户数/t5.当前注册用户数 as 周活跃用户率
from
(
select t1.年份,
       t1.周数,
       count(distinct 用户id) as 活跃用户数
from
(
select 用户id,
       登录日期,
       year(登录日期) as 年份,
       week(登录日期,1) as 周数
from 用户登录信息表
) as t1
group by t1.年份,t1.周数
) as t2
left join
(
select count(distinct t3.用户id) as 当前注册用户数
from
(
select 用户id,
       注册日期,
       year(注册日期) as 年份,
       week(注册日期,1) as 周数
from 已注册用户信息表
) as t3
left join
(
select year(max(登录日期)) as 活跃最大年份,
       week(max(登录日期),1) as 活跃最大周数
from 用户登录信息表
) as t4 on 1 = 1
where t3.年份 < t4.活跃最大年份 or (t3.年份 = t4.活跃最大年份 and t3.周数 <= t4.活跃最大周数)
) as t5 on 1 = 1;

返回结果为：

【本题考点】

1）考查对用户活跃指标“周活跃用户率”的了解；

2）考查对日期的处理能力，比如week函数的使用；

3）考查对多表联结的了解。

 ⬇️点击「阅读原文」

 免费报名 数据分析训练营