题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
http://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e
with t AS
(select distinct uid,DATE_FORMAT(start_time,"%Y%m") start_month,
DENSE_RANK() over(partition by uid order by DATE_FORMAT(start_time,"%Y%m")) R1
from exam_record)
select start_month,count(distinct uid) mau,sum(if(R1=1,1,0)) month_add_uv,
max(sum(if(R1=1,1,0))) over(order by start_month) max_month_add_uv,
sum(sum(if(R1=1,1,0))) over(order by start_month) cum_sum_uv
from t
group by start_month
order by start_month
查看17道真题和解析