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

select date_format(temp.day, '%Y%m')  as month,
       round(count(*) / count(distinct uid),2)as avg_active_days,
       count(distinct uid)               mau

from (select er.uid, date_format(start_time, '%Y-%m-%d') as day
      from exam_record er
      where date_format(er.start_time, '%Y%m') in (select date_format(start_time, '%Y%m') as month
                                                   from exam_record er
                                                   where er.score is not null
                                                     and year(start_time) = '2021'
                                                   group by month)
        and er.score is not null
      group by er.uid, day) as temp
group by month;