题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
https://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e
select a.start_month,count(distinct uid) as mau,
count(distinct if(first_month=start_month,uid,null)) as month_add_uv,
max(count(distinct if(first_month=start_month,uid,null))) over(order by start_month) max_month_add_uv,
sum(count(distinct if(first_month=start_month,uid,null))) over(order by start_month) cum_sum_uv
from(
SELECT uid,
min(date_format(start_time,"%Y%m")) over(partition by uid order by date_format(start_time,"%Y%m")) as first_month,
date_format(start_time,'%Y%m') as start_month
from exam_record
)a
group by a.start_month
查看12道真题和解析