题解 | #平均活跃天数和月活人数#

平均活跃天数和月活人数

https://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9

SELECT date_format(temp.day, '%Y%m') AS month,
       round(count(*) / count(DISTINCT temp.uid), 2) AS avg_active_days,
       COUNT(DISTINCT temp.uid) AS mau
FROM (
    SELECT er.uid,
           DATE_FORMAT(er.start_time, '%Y-%m-%d') AS day
    FROM exam_record er
             JOIN (
        SELECT DISTINCT DATE_FORMAT(start_time, '%Y%m') AS month
        FROM exam_record
        WHERE score IS NOT NULL AND YEAR(start_time) = '2021'
    ) temp ON DATE_FORMAT(er.start_time, '%Y%m') = temp.month
    WHERE er.score IS NOT NULL
    GROUP BY er.uid, day
) temp
GROUP BY month;

全部评论
点赞 收藏 评论
分享

全站热榜

正在热议