SQL35 每月及截止当月的答题情况

题目主要信息：

  • 输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数
  • 结果按月份升序输出

问题拆分：

  • 根据uid从exam_record表中筛选出每个用户最早答题的月份：
    • 根据uid分组统计每组最早月份。知识点：group by
    • 计算最小的月份。min(date_format(start_time, '%Y%m')) as early_month 知识点：min()、date_format()
    • 筛选出的结果记为early_month_table
  • 从early_month_table中筛选出每个月份新增的用户，即表early_month_table中是每个用户最早出现的月份，我们现在要从中筛选出每个月份有多少个最早出现的用户，即新增用户：
    • 根据最早的月份进行分组，统计每个月份的情况。知识点：group by
    • 统计每个月份不同的uid。 知识点：count()、distinct
    • 筛选出的结果记录为month_add_table
  • 从exam_record中筛选出每个月份的活跃用户：
    • 根据不同的月份分组，统计每个月的情况，月份即作为开始月份start_month。group by date_format(start_time, '%Y%m') 知识点：group by
    • 统计每组不同的用户ID即为每个月活跃用户数。知识点：count()、distinct
    • 筛选出的结果记为month_mau_table
  • 新增信息和活跃信息分布在两个表中，我们连接表month_mau_table和表month_add_table，采用left join操作，将每个用户最早出现月份早于开始月份的，都连在开始月份后面，这样每个开始月份后面就会有包括这个月在内的前面所有月的新增信息。on month_mau_table.start_month >= month_add_table.early_month 知识点：left join
  • 从连接后的表中筛选出每个月份start_month、每个月的活跃人数、每个月新增，截至当月累计新增、截至当月最大新增：
    • 以每个start_month分组，讨论每种情况。知识点：group by
    • 利用if语句判断每组的start_month是否等于early_month，因为它连接了这个月及之前的全部early_month，所有只有等于时才是当月新增，我们选择新增数量，不等时为0，对这个组的所有判断求和就是当月新增。sum(if(start_month = early_month, month_add_uv, 0)) as month_add_uv 知识点：if、sum
    • 直接用取这个分组的最大新增，就是截至当月最大新增人数，因为它连接了这个月及之前的全部early_month，即这个组有这些全部early_month。 知识点：max
    • 直接对这个分组的全部新增求和，就是截至当月累计用户数，因为它连接了这个月及之前的全部early_month。 知识点：sum
  • 按照月份升序排序。知识点：order by asc

代码：

select start_month, mau,
       sum(if(start_month = early_month, month_add_uv, 0)) as month_add_uv,
       max(month_add_uv) as max_month_add_uv,
       sum(month_add_uv) as cum_sum_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')
) month_mau_table
left join(
    select early_month, count(distinct uid) as month_add_uv
    from(
        select uid, min(date_format(start_time, '%Y%m')) as early_month
        from exam_record
        group by uid
    ) early_month_table
    group by early_month
) month_add_table 
on month_mau_table.start_month >= month_add_table.early_month
group by start_month
order by start_month asc