【题目】
有两张表：
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 t1group 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 t3left join(select year(max(登录日期)) as 活跃最大年份,week(max(登录日期),1) as 活跃最大周数from 用户登录信息表) as t4 on 1 = 1where 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 t1group by t1.年份,t1.周数) as t2left join(select count(distinct t3.用户id) as 当前注册用户数from(select 用户id,注册日期,year(注册日期) as 年份,week(注册日期,1) as 周数from 已注册用户信息表) as t3left join(select year(max(登录日期)) as 活跃最大年份,week(max(登录日期),1) as 活跃最大周数from 用户登录信息表) as t4 on 1 = 1where t3.年份 < t4.活跃最大年份 or (t3.年份 = t4.活跃最大年份 and t3.周数 <= t4.活跃最大周数)) as t5 on 1 = 1;
返回结果为：
【本题考点】
1）考查对用户活跃指标“周活跃用户率”的了解；
2）考查对日期的处理能力，比如week函数的使用；
3）考查对多表联结的了解。
⬇️点击「阅读原文」
免费报名 数据分析训练营
原文作者：猴子数据分析；如有侵权，请联系管理员删除。
