场景 1：连续登录至少两天的用户
业务需求
找出按自然天连续登录至少两天的用户（只要某一天有登录行为即算当天登录）。
表结构
user_login (user_id, login_date)
核心思路
-
对用户登录日期去重，避免同一天多次登录干扰；
-
用「登录日期 - 排序序号」生成连续登录分组标识（相同标识为连续天）；
-
按用户 + 分组标识统计天数，筛选≥2 天的分组，最终去重得到用户 ID。
完整 SQL
sql
WITH t1 AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS group_rk
FROM user_login
GROUP BY user_id, login_date
),
t2 AS (
SELECT
user_id,
group_rk,
COUNT(*)
FROM t1
GROUP BY user_id, group_rk
HAVING COUNT(*) >= 2
)
SELECT DISTINCT user_id FROM t2;
关键要点
-
DATE_SUB(login_date, INTERVAL 排序序号 DAY)是生成连续分组的核心； -
先按
user_id+login_date去重，避免同一天多次登录误判为连续。
场景 2：用户最大连续登录天数（含起止时间）
业务需求
统计用户最大连续登录天数，并输出对应连续时间段的最早登录时间、最晚登出时间。
表结构
user_login (user_id, login_time, logout_time)
核心思路
-
按用户 + 登录日期去重，保留每天最早登录、最晚登出时间；
-
生成连续登录分组标识；
-
统计每个分组的连续天数，找到用户最大天数；
-
关联回分组信息，输出最大天数及对应起止时间。
完整 SQL
sql
WITH daily_login AS (
SELECT
user_id,
DATE(login_time) AS login_date,
MIN(login_time) AS daily_first_login,
MAX(logout_time) AS daily_last_logout
FROM user_login
WHERE logout_time >= login_time
GROUP BY user_id, login_date
),
continuous_group AS (
SELECT
user_id,
login_date,
daily_first_login,
daily_last_logout,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS group_id
FROM daily_login
),
group_stats AS (
SELECT
user_id,
group_id,
COUNT(*) AS continuous_days,
MIN(daily_first_login) AS start_login,
MAX(daily_last_logout) AS end_logout
FROM continuous_group
GROUP BY user_id, group_id
),
user_max_con AS (
SELECT
user_id,
MAX(continuous_days) AS max_days
FROM group_stats
GROUP BY user_id
)
SELECT
a.user_id,
a.max_days,
b.start_login,
b.end_logout
FROM user_max_con a
INNER JOIN group_stats b
ON a.user_id = b.user_id
AND a.max_days = b.continuous_days;
关键要点
-
过滤
logout_time < login_time的无效数据； -
连续段起止时间取分组内最早登录、最晚登出时间。
场景 3：共同好友≥3 的用户对
业务需求
找出共同好友数量≥3 的用户对（避免 1-2 和 2-1 重复的用户对）。
表结构
friend_relation (user_id, friend_id)
核心思路
-
匹配两个用户的共同好友，生成
user1 < user2的有序用户对； -
统计每个用户对的共同好友数量；
-
筛选数量≥3 的用户对。
完整 SQL
sql
WITH t1 AS (
SELECT
a.user_id AS user1,
b.user_id AS user2,
a.friend_id AS friend
FROM friend_relation a
JOIN friend_relation b ON a.friend_id = b.friend_id
WHERE a.user_id < b.user_id
),
t2 AS (
SELECT
user1,
user2,
COUNT(*) AS cnt
FROM t1
GROUP BY user1, user2
HAVING cnt >= 3
)
SELECT user1, user2 FROM t2;
关键要点
-
a.user_id < b.user_id确保用户对唯一，避免重复； -
可先对
friend_relation去重，避免重复记录导致统计错误。
场景 4：刷题用户次日留存率
业务需求
计算用户刷题的次日留存率（当天刷题的用户中，次日仍刷题的比例）。
表结构
question_practice_detail (id, device_id, question_id, result, date)
核心思路
-
按用户 + 刷题日期去重，避免同一天多次刷题重复统计；
-
左连接匹配用户次日刷题记录，标记是否留存；
-
按日期统计总用户数、留存用户数，计算留存率（保留 2 位小数）。
完整 SQL
sql
WITH t1 AS (
SELECT DISTINCT
device_id,
date AS practice_date
FROM question_practice_detail
),
t2 AS (
SELECT
a.practice_date,
a.device_id,
CASE WHEN b.device_id IS NOT NULL THEN 1 ELSE 0 END AS is_retention
FROM t1 a
LEFT JOIN t1 b
ON a.device_id = b.device_id
AND b.practice_date = DATE_ADD(a.practice_date, INTERVAL 1 DAY)
)
SELECT
practice_date,
COUNT(DISTINCT device_id) AS total_users,
SUM(is_retention) AS retention_users,
ROUND(
IF(COUNT(DISTINCT device_id)=0,0,SUM(is_retention)/COUNT(DISTINCT device_id)),
2
) AS retention_rate
FROM t2
GROUP BY practice_date
ORDER BY practice_date;
关键要点
-
DATE_ADD(practice_date, INTERVAL 1 DAY)匹配次日日期； -
IF(总用户数=0,0,留存数/总用户数)避免除以 0 错误； -
留存率用
ROUND(...,2)格式化，便于运营查看。
通用核心技巧总结
-
连续日期类问题：核心是「日期 - 排序序号」生成连续分组标识；
-
用户对类问题：用
user1 < user2确保用户对唯一，避免重复； -
留存率类问题：先按用户 + 日期去重，再左连接匹配次日记录；
-
数据鲁棒性：过滤无效数据（如登出时间 < 登录时间）、避免除以 0 错误。