编写一个MySQL查询来计算基于注册队列的用户每周留存率[关闭]

已关闭。此问题需要details or clarity。目前不接受回答。
**要改进此问题吗？**通过editing this post添加详细信息并阐明问题。

3天前关闭。
Improve this question的
我需要编写一个MySQL查询来计算这两个表（不确定是否都需要）中用户的每周保留率。
events table的
| 用户标识|事件类型|事件名称|位置|装置|用户类型|发生时间（_A） |
| --|--|--|--|--|--|--|
| 10522 |接合|登录|日本|戴尔Inspiron笔记型电脑| 3 |2014年5月2日11时02分|
| 10522 |接合|首页|日本|戴尔Inspiron笔记型电脑| 3 |2014年5月2日11时02分|
| 10522 |接合|喜欢的消息（_M）|日本|戴尔Inspiron笔记型电脑| 3 |2014年5月2日11时03分|
| 10522 |接合|查看收件箱|日本|戴尔Inspiron笔记型电脑| 3 |2014年5月2日11时04分|
| 10522 |接合|搜索_运行|日本|戴尔Inspiron笔记型电脑| 3 |2014年5月2日11时03分|
| 10612 |接合|发送消息（_M）|荷兰|iPhone 5| 1 |2014年5月1日10时00分|
| 11037 |接合|搜索_自动完成|联合 Realm |iPhone 4S| 1 |2014年5月1日07时25分|
| 11536 |接合|搜索点击结果_10|台湾|MacBook Air| 2 |2014年5月5日11时04分|
| 11768 |注册流程|完成注册|法国|MacBook Pro| 3 |2014年5月1日08时03分|
users table的
| 用户标识|公司标识|语言|状态|创建时间（_A）|激活时间（_A）|
| --|--|--|--|--|--|
| 2242 | 1 |德国|活性|2013年6月20日18时32分|2013年6月20日18时34分|
| 2243 | 17 |法国|活性|2013年6月20日16时32分|2013年6月20日16时33分|
| 2240 | 11565 |英语|活性|2013年6月20日15时41分|2013年6月20日15时43分|
| 2235 | 378 |西班牙|活性|2013年6月19日19时56分|2013年6月19日19时58分|
| 2234 | 1160 |英语|活性|2013年6月19日13时39分|2013年6月19日13时40分|
| 2225 | 2480 |英语|活性|2013年6月19日11时19分|2013年6月19日11时21分|
| 2217 | 3474 |英语|活性|2013年6月19日09时16分00秒|2013年6月19日09时17分|
| 2222 | 9658 |西班牙|活性|2013年6月19日08时52分00秒|2013年6月19日08时54分|
| 2223 | 23 |英语|活性|2013年6月19日02时56分00秒|2013年6月19日02时58分00秒|
我试过了，但没有用：

SELECT
  DATE(u.activated_at) AS cohort_start_date,
  WEEK(e.occurred_at) AS week_number,
  COUNT(DISTINCT e.user_id) AS active_users,
  COUNT(DISTINCT u.user_id) AS total_users,
  COUNT(DISTINCT e.user_id) / COUNT(DISTINCT u.user_id) * 100 AS retention_rate
FROM
  users u
JOIN events e ON u.user_id = e.user_id
WHERE
  e.event_type = 'engagement'
GROUP BY
  cohort_start_date, week_number
ORDER BY
  cohort_start_date, week_number;

字符串

您需要将在给定日期激活的用户计数与给定周内的活动聚合分开。您还需要使用YEARWEEK(date,mode)而不是WEEK。我认为这将产生您正在寻找的统计数据：

WITH cohorts AS (
    SELECT
        DATE(activated_at) AS cohort_start_date,
        COUNT(*) AS total_users
    FROM users
    GROUP BY 1
),
weekly_stats AS (
    SELECT
        DATE(u.activated_at) AS cohort_start_date,
        YEARWEEK(e.occurred_at, 0) AS year_week,
        COUNT(DISTINCT e.user_id) AS active_users
    FROM users u
    JOIN events e ON u.user_id = e.user_id
    WHERE e.event_type = 'engagement'
    GROUP BY cohort_start_date, year_week
)
SELECT
    cohorts.cohort_start_date,
    weekly_stats.year_week,
    weekly_stats.active_users,
    cohorts.total_users AS total_users,
    weekly_stats.active_users / cohorts.total_users * 100 AS retention_rate
FROM cohorts
JOIN weekly_stats
    ON cohorts.cohort_start_date = weekly_stats.cohort_start_date
ORDER BY cohort_start_date, year_week;

字符串
这里有一个db<>fiddle。

编写一个MySQL查询来计算基于注册队列的用户每周留存率[关闭]

1条答案

相关问题

热门标签

最新问答