横扫SQL面试——用户留存率问题

横扫SQL面试

📌 用户留存率问题

在这里插入图片描述

📝 题目题干

某APP的用户登录明细表 login_detail 记录了用户的登录行为，需要计算以下两类指标：

📊 数据表结构

login_detail 用户登录表

字段名	类型	说明
user_id	BIGINT	用户ID（唯一标识）
event_time	TIMESTAMP	登录时间（精确到秒）

标准留存率🌟：用户在首次登录后第N天（精确日期）再次登录的比例
时间段活跃率🌟：用户在首次登录后的N天内（任意一天）活跃的比例

编写SQL查询，返回以下指标（保留2位小数）：💻🔍💻🔍

次日留存率（1_day_retention_rate）
3日留存率（3_day_retention_rate）
7日留存率（7_day_retention_rate）
3天窗口期活跃率（3_day_activity_rate）
7天窗口期活跃率（7_day_activity_rate）

精准定位问题时间点（例如用户在第3天流失率高），指导针对性改进（如第2天推送优惠券）。

user_id	event_time
1001	2023-08-01 09:30:15
1001	2023-08-02 10:15:00
1002	2023-08-01 14:20:30
1003	2023-08-01 18:45:00
1002	2023-08-03 11:00:00
1003	2023-08-05 19:30:00

用户1001：
首次登录日期=2023-08-01
次日留存检查：2023-08-02 ✅（存在记录）
3日窗口活跃检查：2023-08-02~2023-08-04 ✅（有8月2日记录）
用户1003：
首次登录日期=2023-08-01
7日留存检查：2023-08-08 ❌（无记录）
7天窗口活跃检查：2023-08-02~2023-08-08 ✅（有8月5日记录）

🎯 核心思路

一：标准留存率：首次登录后第N天（精确日期）

标准留存率 精准日期检查 DATE(event_time) = 首次登录 +N天 用户粘性分析、行业报告🌟🌟

📌 步骤1：计算用户首次登录日期

with first_login as (select user_id, min(date(event_time)) as first_login_datefrom login_detailgroup by user_id
)

从login_detail表中提取每个用户的最早登录日期 🍕. 🔍. 🔍
使用MIN(date(event_time))获取首次登录的日期（忽略时间部分）
按user_id分组确保每个用户仅一条记录

得到的中间表 first_login~🚀🚀🚀 ✅

user_id	first_login_date
1001	2023-08-01
1002	2023-08-01
1003	2023-08-01

📌 步骤2：验证用户留存状态


retention_check as (select -- 从 first_login 中选取用户 IDfl.user_id,-- 检查次日留存情况-- 使用 CASE WHEN EXISTS 语句来判断是否存在满足条件的记录-- 如果用户在首次登录日期的次日有登录记录，则标记为 1，表示留存-- 否则标记为 0，表示未留存case when exists (-- 子查询，用于检查是否存在满足条件的记录select 1 -- 从用户登录明细表 login_detail 中查询数据from login_detail ld -- 确保子查询中的用户 ID 与 first_login 中的用户 ID 一致where ld.user_id = fl.user_id -- 确保登录日期为首次登录日期的次日and date(ld.event_time) = fl.first_login_date + interval 1 day) then 1 else 0 end as retained_1,... -- 类似逻辑计算retained_3/retained_7from first_login fl
)

处理过程 ✅

遍历first_login中每个用户
对每个用户执行三次子查询：
- 次日留存：检查是否存在first_login_date + 1天的登录 ✅
- 3日留存：检查是否存在first_login_date + 3天的登录 ✅
- 7日留存：检查是否存在first_login_date + 7天的登录 ✅
存在则标记1，否则标记0

在这里插入图片描述

中间表 retention_check ✅

user_id	retained_1	retained_3	retained_7
1001	1	0	0
1002	0	1	0
1003	0	0	1

用户1001：次日在2023-08-02登录 ✅
用户1002：第3天在2023-08-04登录 ✅
用户1003：第7天在2023-08-08登录 ✅

📌 步骤3：计算留存率

用户旅程示意图：
首次登录日       次日         第3天         第7天
│             │            │            │
├─────────────┼────────────┼────────────┤
2023-08-01    2023-08-02   2023-08-04   2023-08-08
(用户1001)     ✅           ❌           ❌
(用户1002)     ❌           ✅           ❌
(用户1003)     ❌           ❌           ✅

select round(avg(retained_1)*100, 2) as retention_1_day_rate,round(avg(retained_3)*100, 2) as retention_3_day_rate,round(avg(retained_7)*100, 2) as retention_7_day_rate
from retention_check;

处理过程 ✅

对retention_check表的标记列取平均值
- avg(retained_1) = (1+0+0)/3 = 0.3333
- 转化为百分比：0.3333 × 100 = 33.33%
同理计算其他留存率

最终结果表 ✅

retention_1_day_rate	retention_3_day_rate	retention_7_day_rate
33.33	33.33	33.33

-- 核心逻辑：精确匹配首次登录+N天的日期
WHERE date(ld.event_time) = fl.first_login_date + interval N day

Tips:Avg函数求比率🤣 🤣 🤣 🤣📚 给新手的AVG函数计算比率小课堂

💡 核心原理✅

AVG( [1,0,1,1,0] ) = (1+0+1+1+0)/5 = 3/5 = 0.6 → 60%

🌰 举个栗子
假设有3个用户：

| user_id | retained_1 |
|---------|------------|
| A       | 1          |
| B       | 0          |
| C       | 1          |

计算过程：

(1+0+1)/3 = 0.6666...
0.6666 × 100 = 66.6666...
ROUND后 → 66.67%

二：时间段活跃率：首次登录后的N天内（任意一天）

时间段活跃率 时间段检查（如3天内） BETWEEN 首次登录+1天 AND +N天 短期行为分析、运营活动效果验证🌟🌟

场景类型	典型案例
运营活动效果验证🧩	双11促销期的3天转化跟踪
新手引导期监测 🧩	注册后7天功能使用率统计
短期行为模式分析🧩	用户领券后3天核销率追踪

📚 时间段活跃率 = 在首次登录后N天内至少活跃一次的用户比例
（如7天窗口期活跃率 = 首次后7天内任意一天登录过的用户数 / 总用户数）

📌步骤1：获取首次登录日期（同方案一）

with first_login as (select user_id,min(date(event_time)) as first_login_datefrom login_detailgroup by user_id
)

在这里插入图片描述

📌步骤2：窗口期活跃验证

activity_check as (select fl.user_id,-- 次日单日活跃检查case when exists (select 1 from login_detail ld where ld.user_id = fl.user_id and date(ld.event_time) = fl.first_login_date + 1) then 1 else 0 end as active_1,-- 3天窗口期活跃检查case when exists (select 1 from login_detail ld where ld.user_id = fl.user_id and date(ld.event_time) between fl.first_login_date + 1 and fl.first_login_date + 3) then 1 else 0 end as active_3_window,-- 同理：-- 7天窗口期活跃检查from first_login fl
)

中间表 activity_check

user_id	active_1	active_3_window	active_7_window
1001	1	1	0
1002	0	1	1
1003	0	0	1

用户1001：
✅ 次日活跃（8月2日）
✅ 3天窗口期（8月2-4日）有登录
❌ 7天窗口期（8月2-8日）无后续登录

📌步骤3：计算窗口期活跃率

selectround(avg(active_1)*100, 2) as active_1_day_rate,round(avg(active_3_window)*100, 2) as active_3_day_window_rate,round(avg(active_7_window)*100, 2) as active_7_day_window_rate 
from activity_check;

最终结果表

active_1_day_rate	active_3_day_window_rate	active_7_day_window_rate
33.33	66.67	66.67

Tips：🎈🎈🎈

-- 包含首日后第1天到第N天（闭区间）
BETWEEN first_day+1 AND first_day+N-- 等效写法（跨数据库兼容）
date(ld.event_time) >= first_day+1 
AND date(ld.event_time) <= first_day+N

🚨 常见误区

误区： 将窗口期误算为N个自然日
正解： 实际计算的是首日后的连续N天

-- 错误示范（可能跨月错误）
between first_day and first_day + interval '3 days'-- 正确写法（首日+1到首日+N）
between first_day + 1 and first_day + N

场景	适用逻辑	核心SQL片段	适用场景
标准留存率	精准日期检查	`DATE(event_time) = 首次登录 +N天`	用户粘性分析、行业报告
时间段活跃率	时间段检查（如3天内）	`BETWEEN 首次登录+1天 AND +N天`	短期行为分析、运营活动效果验证

给大家留一个作业~🤣 🤣 🤣 🤣

复购用户人数🚀🚀🚀

已知 order 表，表中记录了订单的相关信息，order_id 是唯一的。请通过 SQL 语句统计在 2024 年 1 月 1 日到 2024 年 3 月 10 日期间，有 30 日复购行为的人数。

表名	字段名	数据类型	说明
order	order_id	未知（唯一标识）	订单的唯一编号
order	user_id	未知	用户的编号
order	pay_day	字符串（格式为 YYYYMMDD）	订单的支付日期

其中，30 日复购的定义为：用户在 2024 年 1 月 1 日到 2024 年 3 月 10 日这个范围内首次支付后的 30 天内又再次进行了支付。

order_id	user_id	pay_day
10001	user_01	20240101
10002	user_02	20240105
10003	user_03	20240110
10004	user_01	20240115
10005	user_04	20240120
10006	user_02	20240201
10007	user_05	20240208
10008	user_03	20240215
10009	user_06	20240220
10010	user_01	20240301
10011	user_07	20240305
10012	user_04	20240310

复购用户人数SQL题解

📝 题目题干

表名	字段名	数据类型	说明
`order`	order_id	唯一标识	订单的唯一编号
`order`	user_id	字符串或数值	用户的编号
`order`	pay_day	字符串（格式为 `YYYYMMDD`）	订单的支付日期

题目要求
统计在 2024年1月1日到2024年3月10日 期间，有 30日复购行为 的用户人数。
复购定义：用户在首次支付后的30天内（含当天）再次支付。

📌步骤1：获取首次支付日期✅

WITH FirstOrder AS (SELECT user_id,MIN(STR_TO_DATE(pay_day, '%Y%m%d')) AS first_pay_dayFROM `order`WHERE STR_TO_DATE(pay_day, '%Y%m%d') BETWEEN '2024-01-01' AND '2024-03-10'GROUP BY user_id
)

使用 MIN(STR_TO_DATE(pay_day, '%Y%m%d')) 获取用户在时间段内的 首次支付日期。
筛选条件：仅统计首次支付在 2024-01-01 至 2024-03-10 的用户。

中间表示例 ✅

user_id	first_pay_day
user_01	2024-01-01
user_02	2024-01-05

📌步骤2：筛选复购订单✅

Reorder AS (SELECT o.user_id,STR_TO_DATE(o.pay_day, '%Y%m%d') AS reorder_dayFROM `order` oJOIN FirstOrder fo ON o.user_id = fo.user_idWHERE -- 订单日期在首次支付后的30天内（含当天）STR_TO_DATE(o.pay_day, '%Y%m%d') BETWEEN fo.first_pay_day AND DATE_ADD(fo.first_pay_day, INTERVAL 30 DAY)-- 排除首次支付当天的订单（仅保留复购订单）AND STR_TO_DATE(o.pay_day, '%Y%m%d') > fo.first_pay_day
)

通过 BETWEEN 筛选首次支付后 30天内 的订单。
STR_TO_DATE(o.pay_day, '%Y%m%d') > fo.first_pay_day 排除首次支付当天的订单，确保复购是 再次支付。

中间表示例 ✅

user_id	reorder_day
user_01	2024-01-15
user_02	2024-02-01

📌步骤3：统计复购用户数✅

SELECT COUNT(DISTINCT user_id) AS repurchase_count
FROM Reorder;

最终结果 ✅

repurchase_count
3

pay_day 为字符串时，需用 STR_TO_DATE(pay_day, '%Y%m%d') 显式转换。

📌 最终答案

WITH FirstOrder AS (SELECT user_id,MIN(STR_TO_DATE(pay_day, '%Y%m%d')) AS first_pay_dayFROM `order`WHERE STR_TO_DATE(pay_day, '%Y%m%d') BETWEEN '2024-01-01' AND '2024-03-10'GROUP BY user_id
),
Reorder AS (SELECT o.user_idFROM `order` oJOIN FirstOrder fo ON o.user_id = fo.user_idWHERE STR_TO_DATE(o.pay_day, '%Y%m%d') BETWEEN fo.first_pay_day AND DATE_ADD(fo.first_pay_day, INTERVAL 30 DAY)AND STR_TO_DATE(o.pay_day, '%Y%m%d') > fo.first_pay_day
)
SELECT COUNT(DISTINCT user_id) AS repurchase_count
FROM Reorder;

Tips：如果题目允许首次当天后续订单算复购

-- 修改条件为 >= 
AND STR_TO_DATE(o.pay_day, '%Y%m%d') >= fo.first_pay_day-- 但需要去重首次订单本身：
AND o.order_id != (SELECT MIN(order_id) FROM `order` WHERE user_id = o.user_id AND STR_TO_DATE(pay_day, '%Y%m%d') = fo.first_pay_day
)

过滤条件双重保险：
┌──────────────────────────────┐
│         30天时间窗口          │
│  [first_day ～ first_day+30]  │
└───────────────────┬──────────┘▼排除首日当天的订单(只保留右侧箭头部分)

横扫SQL面试——用户留存率问题