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

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

题目理解

本SQL查询旨在分析用户活跃数据，计算两个关键指标：

1. 每日活跃用户数(DAU)
2. 每日新增用户占比(新用户占活跃用户的比例)

解题思路

1. 数据准备阶段

首先我们需要获取所有用户的活跃记录，包括：

• 用户进入时间(in_time)
• 用户离开时间(out_time)

由于一个用户在同一天可能有多次活跃记录，我们需要对数据进行去重处理。

---

2. 核心计算逻辑

通过三个CTE(Common Table Expressions)分步处理数据：

1. ​user_activity_records​：合并所有活跃记录

   • 从in_time和out_time中提取日期
   • 使用UNION合并结果并自动去重

2. ​user_first_activity​：计算每个用户的首次活跃日期

   • 按用户分组
   • 使用MIN函数找出每个用户的最早活跃日期

3. ​user_activity_with_first_date​：关联活跃记录与首次活跃日期

   • 将活跃记录与用户首次活跃日期关联
   • 为后续计算准备完整数据集

---

3. 最终指标计算

基于准备好的数据，计算两个核心指标：

1. ​daily_active_users​：每日活跃用户数

   • 按日期分组
   • 使用COUNT(*)计算每日不重复用户数

2. ​new_user_ratio​：新增用户占比

   • 判断当前活跃日期是否为用户的首次活跃日期
   • 计算新增用户数占总活跃用户数的比例
   • 使用ROUND保留两位小数

---

技术亮点

1. ​UNION自动去重​：高效处理用户可能在同一天多次活跃的情况
2. ​CTE分步处理​：使复杂查询逻辑清晰易读
3. ​IF条件计数​：优雅地实现条件计数功能
4. ​JOIN USING语法​：简化相同列名的连接操作

最终代码

WITH
    -- 获取用户活跃日期（合并in_time和out_time）
    user_activity_records AS (
        SELECT
            uid,
            DATE(in_time) AS activity_date
        FROM
            tb_user_log
        UNION
        SELECT
            uid,
            DATE(out_time) AS activity_date
        FROM
            tb_user_log
    ),
    
    -- 计算每个用户的首次活跃日期
    user_first_activity AS (
        SELECT
            uid,
            MIN(activity_date) AS first_activity_date
        FROM
            user_activity_records
        GROUP BY
            uid
    ),
    
    -- 合并活跃记录和首次活跃日期
    user_activity_with_first_date AS (
        SELECT
            uar.uid,
            uar.activity_date,
            ufa.first_activity_date
        FROM
            user_activity_records uar
        JOIN
            user_first_activity ufa USING (uid)
    )

-- 计算每日活跃用户数和新增用户占比
SELECT
    activity_date,
    COUNT(*) AS daily_active_users,
    ROUND(
        COUNT(IF(first_activity_date = activity_date, 1, NULL)) / COUNT(*),
        2
    ) AS new_user_ratio
FROM
    user_activity_with_first_date
GROUP BY
    activity_date
ORDER BY
    activity_date;