题解 | #每月及截止当月的答题情况#
每月及截止当月的答题情况
https://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e
#首先计算每个月份的活跃用户数和新增用户数,然后将两个表进行连接,同时将新增用户表中null使用case转化为0;接着对新增用户数使用max窗口函数计算单月最大新增用户数,对新增用户数使用count窗口函数计算累积用户数,容易错误的点累积用户数是每个月新增用户数相加,不是活跃用户数相加。 SELECT *,max(month_add_uv) over (order by start_month) as max_month_add_uv,sum(month_add_uv) over (order by start_month) as cum_sum_uv FROM (SELECT b.*,case when month_add_uv is null then 0 else month_add_uv end as month_add_uv FROM (SELECT DATE_FORMAT(start_time,'%Y%m') as start_month,count(DISTINCT uid) as mau FROM exam_record GROUP BY DATE_FORMAT(start_time,'%Y%m')) b left join ( SELECT DATE_FORMAT(start_time,'%Y%m') as start_month,count(uid) as month_add_uv FROM (SELECT *,row_number() over (partition by uid order by start_time) as xin FROM exam_record)a where xin=1 GROUP BY DATE_FORMAT(start_time,'%Y%m') ) c on b.start_month=c.start_month) d