基于本文回答

播面

0
评论

对于判断为已流失（过去 90 天无任何登录行为）的用户，统计他们流失前最后一次在平台的行为类型（如浏览、加购、搜索、退单等）的分布情况

面试真题：流失用户最后一次行为分布统计

题目描述：
在电商或内容平台中，流失用户的流失前行为对于流失原因分析至关重要。请编写一个 SQL 查询，找出已流失用户（定义为：过去 90 天无任何登录行为的用户），并统计他们在流失前最后一次在平台的行为类型（如浏览、加购、搜索、退单等）的人数及占比分布情况。

注：假设当前分析的基准日期为 2023-10-01，即过去 90 天未登录指自 2023-07-03（含）以来无任何 login 行为。

数据准备

用户行为日志表：user_behavior_log

user_id behavior_type behavior_time
101 login 2023-09-25 10:00:00
101 browse 2023-09-25 10:05:00
102 login 2023-05-10 12:00:00
102 search 2023-05-10 12:05:00
102 browse 2023-05-10 12:10:00
103 login 2023-06-01 09:00:00
103 add_to_cart 2023-06-01 09:15:00
103 refund 2023-06-01 09:30:00
104 login 2023-04-20 14:00:00
104 search 2023-04-20 14:10:00
105 login 2023-06-15 15:00:00
105 add_to_cart 2023-06-15 15:20:00
106 login 2023-08-15 16:00:00

SQL 解决方案（MySQL 8.0+ / Hive SQL）

sql 
WITH user_login_status AS (
    -- Step 1: 计算每个用户的最后一次登录时间
    SELECT 
        user_id,
        MAX(CASE WHEN behavior_type = 'login' THEN behavior_time END) AS last_login_time
    FROM user_behavior_log
    GROUP BY user_id
),
churned_users AS (
    -- Step 2: 筛选出过去 90 天（基准日 2023-10-01）未登录的流失用户
    SELECT user_id
    FROM user_login_status
    WHERE last_login_time < DATE_SUB('2023-10-01', INTERVAL 90 DAY)
       OR last_login_time IS NULL
),
last_behavior_ranked AS (
    -- Step 3: 锁定流失用户的所有行为，并通过窗口函数按时间倒序排序
    SELECT 
        b.user_id,
        b.behavior_type,
        ROW_NUMBER() OVER (PARTITION BY b.user_id ORDER BY b.behavior_time DESC) AS rn
    FROM user_behavior_log b
    INNER JOIN churned_users cu ON b.user_id = cu.user_id
),
user_last_behavior AS (
    -- Step 4: 提取每个流失用户流失前的最后一次行为
    SELECT user_id, behavior_type
    FROM last_behavior_ranked
    WHERE rn = 1
)
-- Step 5: 统计最后一次行为的分布及占比
SELECT 
    behavior_type,
    COUNT(DISTINCT user_id) AS user_count,
    ROUND(COUNT(DISTINCT user_id) * 100.0 / SUM(COUNT(DISTINCT user_id)) OVER(), 2) AS percentage
FROM user_last_behavior
GROUP BY behavior_type;

核心考点与解题思路分析

本题是业务分析场景中非常经典的“流失用户画像/行为溯源”问题，主要考察面试者将复杂业务逻辑转化为高效 SQL 的能力。

1. 如何准确定义“流失用户”？

  • 业务逻辑：过去 90 天无任何登录行为。
  • SQL 实现：先通过 GROUP BY user_id 结合条件聚合 MAX(CASE WHEN behavior_type = 'login' THEN behavior_time END) 找出每个用户的最后登录时间。
  • 边界考虑：使用 DATE_SUB 动态计算 90 天前的边界。在面试中，主动提出“考虑从未有过登录记录的边缘用户（last_login_time IS NULL）”会是加分项。

2. 如何获取“最后一次行为”？

这是典型的 分组 Top N (N=1) 问题。

  • 常用工具：窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY behavior_time DESC)。
  • 排序依据：按行为时间 behavior_time 倒序，取 rn = 1 即为该用户流失前的最后一次操作。
  • 优化思维：在子查询中先通过 INNER JOIN 过滤出流失用户，再执行 ROW_NUMBER()。这样可以避免对全量活跃用户进行大表的窗口函数计算，大大提升查询性能。

3. 如何计算占比（Percentage）？

  • 在最终的分组统计中，我们需要知道每个行为的人数占总流失人数的比例。
  • 高阶写法：利用窗口函数 SUM(COUNT(...)) OVER() 一步到位。
    • COUNT(DISTINCT user_id) 计算当前行为类型的用户数。
    • SUM(COUNT(DISTINCT user_id)) OVER() 则是计算所有行为类型的总流失人数。
    • 两者相除并乘以 100，再用 ROUND(..., 2) 保留两位小数，免去了再次写子查询关联的繁琐。
右滑查看面试常问