题解 | #每天的日活数及新用户占比#

每天的日活数及新用户占比

https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb

WITH
    daily_active AS (
        SELECT
            uid,
            DATE (in_time) act_dt
        FROM
            tb_user_log
        UNION
        SELECT
            uid,
            DATE (out_time) act_dt
        FROM
            tb_user_log
    ),
    new_users AS (
        SELECT
            uid,
            DATE (MIN(in_time)) first_dt
        FROM
            tb_user_log
        GROUP BY
            uid
    ),
    daily_act_cnt AS (
        SELECT
            act_dt,
            COUNT(*) dau
        FROM
            daily_active
        GROUP BY
            act_dt
    )

#需要考虑dau表与new表连接--》有几个连接就有几个新增
#但需要注意这里left join 会出现null的情况
SELECT 
    act_dt dt,
    dau,
    ROUND(
        COUNT(n.uid)/dau,
        2
    )
FROM daily_act_cnt d
    LEFT JOIN new_users n ON n.first_dt = d.act_dt
GROUP BY act_dt
ORDER BY dt

步骤 1:计算日活跃用户

首先,计算每天的活跃用户数,考虑 in_timeout_time 两个时间点。

  • daily_active 子查询:选择每个用户的 in_time 和 out_time,并将它们转换为日期格式,得到用户的活跃日期 act_dt。使用 UNION 合并 in_time 和 out_time 的结果,去除重复的日期。

步骤 2:计算新增用户

接下来,计算每个用户的首次活跃日期,即新增用户的日期。

  • new_users 子查询:选择每个用户的 in_time 最小值作为用户的首次活跃日期 first_dt。使用 DISTINCT 和 GROUP BY 去除重复的用户,确保每个用户只有一个首次活跃日期。

步骤 3:计算每天的日活跃用户数

然后,计算每天的活跃用户数(DAU)。

  • daily_act_cnt 子查询:对 daily_active 的结果进行分组,按活跃日期 act_dt 统计每天的活跃用户数 dau。

步骤 4:计算新增用户在 DAU 中的比例

最后,计算每天新增用户在日活跃用户数(DAU)中的比例。

  • 主查询:将 daily_act_cnt 和 new_users 进行左连接,连接条件是 new_users 的首次活跃日期 first_dt 等于 daily_act_cnt 的活跃日期 act_dt。由于使用左连接,有可能出现 new_users 表中没有匹配记录的情况,导致 new_users 表中的 uid 为 NULL。选择每天的活跃日期 act_dt 和活跃用户数 dau。计算每天新增用户数 COUNT(n.uid) 并除以 dau 得到比例,使用 ROUND 函数保留两位小数。按 act_dt 进行分组,并按日期排序。

关键点

  • 处理 NULL 值:在进行左连接时,可能会出现 new_users 表中没有匹配记录的情况,导致 n.uidNULL。这种情况下,COUNT(n.uid) 只会计算非 NULL 值,因此能够正确统计新增用户数。
  • 比例计算:计算比例时,先计算每天的新增用户数 COUNT(n.uid),再除以每天的活跃用户数 dau,得到新增用户在 DAU 中的比例。
全部评论

相关推荐

点赞 收藏 评论
分享
牛客网
牛客企业服务