oj-sql

题目

SQL126 平均活跃天数和月活人数

思路

1. 提取年月：
   1. EXTRACT(YEAR_MONTH From submit_time)
   2. date_format(submit_time, ‘%Y%m’)
2. 提取年月日：
   1. date_format(submit_time,’%Y%m%d’)
3. 计数去重：
   1. count(distinct col1, col2)

solution

extract year_month date_format count distinct

select
    EXTRACT(YEAR_MONTH From submit_time) month,
    round(count(distinct uid,date_format(submit_time,'%Y%m%d'))/count(distinct uid),2) avg_active_days,
    count(distinct uid) mau
from exam_record
where submit_time is not null and year(submit_time)=2021
group by EXTRACT(YEAR_MONTH From submit_time)
;