题解 | #每月及截止当月的答题情况#

/*活跃dau：按月count(distinct uid)   新增：按uid选min(start_time)*/
with t AS(
    select uid
        ,min(date_format(start_time,'%Y%m')) AS first_time
    from exam_record
    group by uid
)
select start_month
        ,mau
        ,count(first_time) AS month_add_uv /*当月新增用户数，左连接不为null的数量*/
        ,max(count(first_time)) over(order by start_month asc) AS max_month_add_uv
        ,sum(count(first_time)) over(order by start_month asc) AS cum_sum_uv
from(
    select date_format(start_time,'%Y%m') AS start_month
        ,count(distinct uid) AS mau /*活跃用户数，只能用count(distinct uid)*/
        from exam_record
        group by date_format(start_time,'%Y%m')
)t1
left join t on t1.start_month = t.first_time
group by start_month
order by start_month asc

1、月活dua表t1左连接用户首次活跃时间表t，左表中start_month连接右表时，如果没有和first_time连接上，说明当月没有新增活跃用户，这一项为null，这样可以统计每个月的新增用户

2、窗口函数统计截至当前月，最大的用户新增数量以及累计用户数

3、t1左连接t，形成的表类似于 start_month , dau, uid, first_time

group by start_month，count(first_time)就是start_month这个月的新增用户数