题解 | #平均活跃天数和月活人数#

建一个新表temp存每个人每个月活跃了多少天。具体就是用date_format()截取出submit_time的年月部分然后根据uid和submit_time的年月部分分组，然后count每个月有多少distinct的submit_time作为每个人的月活天数。

with temp as
(select date_format(submit_time, "%Y%m") as mon, uid, count(distinct day(submit_time))as cnt from
exam_record
where year(submit_time) = "2021"
group by date_format(submit_time, "%Y%m"), uid)

从exam_record中选出2021年所有月份并和temp表连接，这样就可以补齐temp表中月份为null的情况。然后根据月份分组并求出所有人月活天数平均值和月活人数,这里我用if function来把null的情况填补为0.

with temp as
(select date_format(submit_time, "%Y%m") as mon, uid, count(distinct day(submit_time))as cnt from
exam_record
where year(submit_time) = "2021"
group by date_format(submit_time, "%Y%m"), uid)

select temp2.month, 
       if(null, 0, round(avg(temp.cnt),2)) as avg_active_days,
       if(null, 0, count(distinct temp.uid)) as mau
from temp right join 
(select date_format(submit_time, "%Y%m") as month from exam_record
 where year(submit_time) = "2021") as temp2
on temp.mon = temp2.month
group by temp2.month