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 (SELECTuid,DATE(in_time) AS activity_dateFROMtb_user_logUNIONSELECTuid,DATE(out_time) AS activity_dateFROMtb_user_log),-- 计算每个用户的首次活跃日期user_first_activity AS (SELECTuid,MIN(activity_date) AS first_activity_dateFROMuser_activity_recordsGROUP BYuid),-- 合并活跃记录和首次活跃日期user_activity_with_first_date AS (SELECTuar.uid,uar.activity_date,ufa.first_activity_dateFROMuser_activity_records uarJOINuser_first_activity ufa USING (uid))-- 计算每日活跃用户数和新增用户占比
SELECTactivity_date,COUNT(*) AS daily_active_users,ROUND(COUNT(IF(first_activity_date = activity_date, 1, NULL)) / COUNT(*),2) AS new_user_ratio
FROMuser_activity_with_first_date
GROUP BYactivity_date
ORDER BYactivity_date;