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

横扫SQL面试

📌 用户留存率问题

📝 题目题干

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

📊 数据表结构

login_detail 用户登录表

1. 标准留存率🌟：用户在首次登录后第N天（精确日期）再次登录的比例
2. 时间段活跃率🌟：用户在首次登录后的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天推送优惠券）。

• 用户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_date
    from login_detail
    group by user_id
)

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

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

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

retention_check as (
    select 
        -- 从 first_login 中选取用户 ID
        fl.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_7

    from first_login fl
)    

处理过程 ✅

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

中间表 retention_check ✅

• 用户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;

处理过程 ✅

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

最终结果表 ✅

-- 核心逻辑：精确匹配首次登录+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天 短期行为分析、运营活动效果验证🌟🌟

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

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

with first_login as (
    select 
        user_id,
        min(date(event_time)) as first_login_date
    from login_detail
    group 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

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

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

select
    round(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;

最终结果表

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

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

复购用户人数🚀🚀🚀

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

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

复购用户人数SQL题解

📝 题目题干

题目要求
统计在 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_day
    FROM `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 的用户。

中间表示例 ✅

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

Reorder AS (
    SELECT 
        o.user_id,
        STR_TO_DATE(o.pay_day, '%Y%m%d') AS reorder_day
    FROM `order` o
    JOIN FirstOrder fo 
        ON o.user_id = fo.user_id
    WHERE 
        -- 订单日期在首次支付后的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 排除首次支付当天的订单，确保复购是 再次支付。

中间表示例 ✅

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

SELECT COUNT(DISTINCT user_id) AS repurchase_count
FROM Reorder;

最终结果 ✅

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_day
    FROM `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_id
    FROM `order` o
    JOIN FirstOrder fo 
        ON o.user_id = fo.user_id
    WHERE 
        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]  │
└───────────────────┬──────────┘
                    ▼
          排除首日当天的订单
          (只保留右侧箭头部分)

整理不易 后续还会继续更新 希望列位多多支持~🚀🚀🚀