新用户的次日留存
分类:SQL
假设我们有一个名为user_info的用户信息表,其中包含用户ID、注册日期和最近一次活跃日期等字段。下面是计算每天新用户的次日留存的SQL语句:
SELECT
reg_date AS date,
COUNT(DISTINCT CASE WHEN DATEDIFF(act_date, reg_date) = 1 THEN user_id ELSE NULL END) AS retained_users,
COUNT(DISTINCT user_id) AS new_users,
ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(act_date, reg_date) = 1 THEN user_id ELSE NULL END) / COUNT(DISTINCT user_id), 4) AS retention_rate
FROM
user_info
GROUP BY
reg_date
解析:
SELECT reg_date AS date
:选择注册日期作为结果集中的日期列。COUNT(DISTINCT CASE WHEN DATEDIFF(act_date, reg_date) = 1 THEN user_id ELSE NULL END)
: 使用CASE语句过滤出次日留存的用户ID,并进行去重计数。COUNT(DISTINCT user_id)
: 按照注册日期分组后,计算每天的新用户数。ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(act_date, reg_date) = 1 THEN user_id ELSE NULL END) / COUNT(DISTINCT user_id), 4)
:计算次日留存率,即留存用户数/新用户数。GROUP BY reg_date
:按照注册日期进行分组。
以上SQL查询语句将会输出每天的留存率及相关指标,方便对于新用户留存的监控和优化。