次周留存率写个sql
时间: 2024-08-16 07:00:17 浏览: 146
七周数据分析.docx
次周留存率是指用户在首次活跃后的第二周还继续使用的比率,通常用于衡量产品用户的粘性和活跃度。在SQL中计算次周留存率,我们需要访问包含用户激活日期以及下次活跃日期的数据表。这里假设我们有一个`user_activity`表,有`user_id`、`activity_date`等字段。下面是一个简单的示例,不过实际SQL会依赖具体的数据库结构:
```sql
WITH first_week AS (
SELECT user_id, MIN(activity_date) AS first_activity_date
FROM user_activity
GROUP BY user_id
HAVING activity_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY) -- 一周前的日期
),
second_week AS (
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_date BETWEEN DATE_ADD(first_activity_date, INTERVAL 1 WEEK) AND DATE_ADD(first_activity_date, INTERVAL 2 WEEK)
)
SELECT
(COUNT(DISTINCT CASE WHEN second_week.user_id IS NOT NULL THEN user_id END) / COUNT(DISTINCT first_week.user_id)) * 100 AS retention_rate
FROM first_week
LEFT JOIN second_week ON first_week.user_id = second_week.user_id;
```
这个查询首先找出一周前首次活跃的用户(`first_week`),然后找出他们在第一周之后第二周内活跃过的用户(`second_week`),最后计算次周留存率。
阅读全文