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

思路:

# 根据uid分组找到最小登录月
select uid,
        date_format(min(start_time), '%Y%m') f_month
from exam_record
group by uid

上述也可用窗口函数构造单独的一列, 当找到最小登录月之后, 再根据月份分组, 就能知道每个月份的新增用户数

select f_month,
    count(1) month_add_uv
from (
    select uid,
        date_format(min(start_time), '%Y%m') f_month
    from exam_record
    group by uid
) t1
group by f_month

最后再查月度活跃用户数

select date_format(start_time, '%Y%m') month,
    count(distinct uid) mau
from exam_record
group by month

最后根据月份进行左连接, 使用窗口函数max() over() 即可的出结果

select t1.month,
    t1.mau,
    ifnull(month_add_uv, 0),
    max(month_add_uv) over(order by t1.month),
    sum(month_add_uv) over(order by t1.month)
from (
    select date_format(start_time, '%Y%m') month,
        count(distinct uid) mau
    from exam_record
    group by month
) t1
left join (
    select f_month,
        count(1) month_add_uv
    from (
        select uid,
            date_format(min(start_time), '%Y%m') f_month
        from exam_record
        group by uid
    ) t1
    group by f_month
) t2 on t1.month=t2.f_month