题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
https://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e
select
t1.*,coalesce(t2.month_add_uv,0) month_add_uv,
max(month_add_uv)over(order by t1.start_month) max_month_add_uv,
sum(month_add_uv)over(order by t1.start_month) cum_sum_uv
from
(
select
date_format(start_time,'%Y%m') start_month,
count(distinct uid) mau
from exam_record
group by start_month
)t1
left join
(
select
start_month,
count(uid) month_add_uv
# count(uid)over(partition by start_month order by start_month)
from
(
select
uid,
date_format(start_time,'%Y%m') start_month,
row_number()over(partition by uid order by date_format(start_time,'%Y%m')) rn
from exam_record
)t
where rn=1
group by start_month
) t2
using(start_month)
