小宇宙电台的同期群分析

2024-11 小宇宙 困难

小宇宙电台是一款受欢迎的在线音频平台,拥有大量活跃用户。为了更好地了解用户的行为模式和优化用户体验,平台需要对用户的登录行为进行深入分析。具体来说,平台希望通过分析用户的登录记录,将用户分为以下三类:

  1. 新增用户:当天第一次访问平台的用户。

  2. 留存用户:在前一天、前两天或前三天有登录记录的用户。

  3. 回流用户:至少在四天前有过登录记录的用户。

通过对这些用户群体的占比进行统计,平台可以更好地理解用户的活跃度和忠诚度,从而制定更有效的用户增长和 retention 策略。

 

同期群(Cohort)分析

同期群分析是一种数据分析方法,它将用户按照某个共同特征(通常是注册或首次行为的时间段)分组,然后跟踪这些组在不同时间段的行为变化。这种分析方法可以帮助企业了解用户在不同阶段的留存率、活跃度等关键指标。

主要用途

  1. 用户留存分析:了解用户在注册后一段时间内的留存情况。

  2. 用户活跃度分析:观察用户在特定时间段内的活跃程度。

  3. 用户行为模式:发现不同时间段用户的使用习惯和行为模式。

结合用户来源与同期群分析

为了更全面地了解小宇宙电台用户的登录行为,我们将结合用户来源分析和同期群分析的方法。具体步骤如下:

  1. 确定同期群:将用户按首次访问日期分组。

  2. 跟踪行为变化:记录每个同期群在后续几天内的登录行为。

  3. 计算指标:统计每个同期群在不同日期的新增用户、留存用户和回流用户的占比。

 

为了进行上述分析,我们有一个名为 user_login_log 的表,记录了用户的每一次登录时间。

编写一个 SQL 查询,统计2024年,每一天的新增用户占比、留存用户占比和回流用户占比。

字符串处理 时间日期 聚合函数 窗口函数 多表连接 百分比计算 CTE或SubQuery group by left join inner join date min case when lag datediff concat
排名 用户名 得分 耗时(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 计划通 1.81 871.24
10 润发搞定了90570 0.0 320.04
11 祁厅长是个好人 0.0 332.45
12 蜘蛛侠在谈薪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日开始;注意用英文逗号+空格连接,保留两位小数。

ID25 分类(1)姿势太多很过分,分类要用CaseWhen
入门 group by date min case when count 聚合函数 入门-闺蜜捉奸记 思扣SQLintern
ID59 抖音面试真题(1)T+1日留存率
简单 group by left join date case when count date_add / concat cast date_sub 时间日期 聚合函数 留存率计算 CTE或SubQuery 2025春招面试真题 字节跳动
ID78 滴滴面试真题(4)未完成订单在第二天继续呼叫的比例  
中等 left join date distinct count date_add / concat 口径探查 百分比计算 CTE或SubQuery 打车出行漏斗分析 2025春招面试真题 滴滴出行
ID152 每天新增用户的会员转化比例  
中等 left join min 时间日期 Bilibili
ID262 拼接歌曲名和专辑名  
简单 group by inner join concat 多表连接 音乐偏好分析 QQ音乐

MySQL 8.0

00:00