小宇宙电台的同期群分析
小宇宙电台是一款受欢迎的在线音频平台，拥有大量活跃用户。为了更好地了解用户的行为模式和优化用户体验，平台需要对用户的登录行为进行深入分析。具体来说，平台希望通过分析用户的登录记录，将用户分为以下三类：
-
新增用户：当天第一次访问平台的用户。
-
留存用户：在前一天、前两天或前三天有登录记录的用户。
-
回流用户：至少在四天前有过登录记录的用户。
通过对这些用户群体的占比进行统计，平台可以更好地理解用户的活跃度和忠诚度，从而制定更有效的用户增长和 retention 策略。
同期群（Cohort）分析
同期群分析是一种数据分析方法，它将用户按照某个共同特征（通常是注册或首次行为的时间段）分组，然后跟踪这些组在不同时间段的行为变化。这种分析方法可以帮助企业了解用户在不同阶段的留存率、活跃度等关键指标。
主要用途
-
用户留存分析：了解用户在注册后一段时间内的留存情况。
-
用户活跃度分析：观察用户在特定时间段内的活跃程度。
-
用户行为模式：发现不同时间段用户的使用习惯和行为模式。
结合用户来源与同期群分析
为了更全面地了解小宇宙电台用户的登录行为，我们将结合用户来源分析和同期群分析的方法。具体步骤如下：
-
确定同期群：将用户按首次访问日期分组。
-
跟踪行为变化：记录每个同期群在后续几天内的登录行为。
-
计算指标：统计每个同期群在不同日期的新增用户、留存用户和回流用户的占比。
为了进行上述分析，我们有一个名为 user_login_log 的表，记录了用户的每一次登录时间。
编写一个 SQL 查询，统计2024年，每一天的新增用户占比、留存用户占比和回流用户占比。
| 排名 | 用户名 | 得分 | 耗时(ms) |
|---|---|---|---|
| 1 | Mikan | 100.0 | 456.47 |
| 2 | 小小明-代码实体 | 100.0 | 497.01 |
| 3 | 美无澜 | 100.0 | 519.25 |
| 4 | 东莞仔 | 100.0 | 627.07 |
| 5 | 菠萝菠萝 | 100.0 | 629.18 |
| 6 | NeverRunAway | 100.0 | 843.97 |
| 7 | 豆豆看星星4747 | 100.0 | 1018.14 |
| 8 | 陈清泉冻鱼拍 | 77.3 | 19413.4 |
| 9 | 你追我如果你追到我 | 66.79 | 595.35 |
| 10 | 计划通 | 1.81 | 871.24 |
| 11 | 润发搞定了90570 | 0.0 | 320.04 |
| 12 | 祁厅长是个好人 | 0.0 | 332.45 |
| 13 | Superstar | 0.0 | 551.84 |
| 14 | 蜘蛛侠在谈薪74642 | 0.0 | 1112.91 |
user_login_log，用户登录日志表
| usr_id | login_time |
|---|---|
| 4001986165359 | 2022-04-22 17:32:52 |
| 3506972046379601 | 2021-09-23 05:52:24 |
| 4628795098338 | 2021-08-31 21:31:29 |
| 3536226009647304 | 2022-06-19 06:14:14 |
| 5132606382970782 | 2022-08-17 15:57:05 |
| login_date | pct |
|---|---|
| 2024-01-01 | 22.22, 33.33, 44.44 |
| 2024-01-02 | 38.46, 7.69, 53.85 |
| 2024-01-03 | 50.00, 11.11, 38.89 |
| 2024-01-04 | 40.00, 10.00, 50.00 |
| 2024-01-05 | 40.00, 20.00, 40.00 |
| 2024-01-06 | 28.57, 21.43, 50.00 |
| 2024-01-07 | 40.00, 10.00, 50.00 |
| 2024-01-08 | 33.33, 16.67, 50.00 |
从2024年1月1日开始；注意用英文逗号+空格连接，保留两位小数。
MySQL 8.0
×