一、SQL核心考点与高频题型 🗄️
📝 基础高频原题回顾
-
连续登陆至少两天的用户
-
最大连续登陆天数
-
共同好友≥3 的用户对
🔍 补充字节高频SQL题
-
统计每个用户近 7 天累计登陆时长（滑动窗口应用）
-
计算用户留存率（次日 /7 日留存，业务核心指标）
-
找出每个类别下金额 Top3 的订单（经典 TopN 问题）
-
行列转换（用户多维度行为统计常用场景）
✍️ 详细解题解析（MySQL语法）
题1：连续登陆至少两天的用户
📋 表结构（user_login）：
| user_id | login_date |
|---------|------------|
| 1 | 2025-01-01 |
| 1 | 2025-01-02 |
| 2 | 2025-01-01 |
| 3 | 2025-01-01 |
| 3 | 2025-01-02 |
| 3 | 2025-01-03 |💡 解题思路：
-
用
ROW_NUMBER()窗口函数按用户分组，对登陆日期升序排序； -
计算
login_date - 排序值，连续的登陆日期计算后结果会保持一致（核心逻辑）； -
按用户 + 上述计算差值分组，统计分组内记录数（即连续天数），筛选出连续天数≥2 的用户。
💻 SQL 实现：
WITH login_rn AS (
SELECT
user_id,
login_date,
-- 关键：连续日期的差值固定，形成分组标识
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY) AS diff
FROM user_login
GROUP BY user_id, login_date -- 去重：同一用户同一天多次登陆算一次有效登陆
),
continuous_login AS (
SELECT
user_id,
COUNT(*) AS continuous_days
FROM login_rn
GROUP BY user_id, diff
HAVING COUNT(*) >= 2 -- 筛选连续至少2天的记录
)
SELECT DISTINCT user_id -- 去重得到目标用户
FROM continuous_login;📌 考点：窗口函数（ROW_NUMBER）的应用、日期运算逻辑、连续值分组判断。
题2：最大连续登陆天数
💡 解题思路：
基于题 1 的中间结果（login_rn），新增一层分组，按用户聚合，取连续天数的最大值即可。
💻 SQL 实现：
WITH login_rn AS (
SELECT
user_id,
login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) DAY) AS diff
FROM user_login
GROUP BY user_id, login_date
),
continuous_stats AS (
SELECT
user_id,
diff,
COUNT(*) AS continuous_days
FROM login_rn
GROUP BY user_id, diff
)
SELECT
user_id,
MAX(continuous_days) AS max_continuous_days -- 取每个用户的最大连续天数
FROM continuous_stats
GROUP BY user_id;🌟 拓展：若需统计全平台用户的最大连续登陆天数，最后一层改为SELECT MAX(continuous_days) FROM continuous_stats;
题3：共同好友≥3的用户对
📋 表结构（friend_relation）：
| user_id | friend_id |
|---------|-----------|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |💡 解题思路：
-
自连接好友关系表，匹配两个用户共同的好友记录；
-
通过
a.user_id < b.user_id限定条件，避免用户对重复（如 (1,2) 和(2,1)视为同一组）； -
按用户对分组，统计共同好友数量，筛选出数量≥3 的组合。
💻 SQL 实现：
WITH friend_pair AS (
-- 自连接找共同好友，限定user1 < user2去重
SELECT
a.user_id AS user1,
b.user_id AS user2,
a.friend_id AS common_friend
FROM friend_relation a
JOIN friend_relation b
ON a.friend_id = b.friend_id -- 好友ID匹配，即共同好友
AND a.user_id < b.user_id -- 核心去重逻辑
)
SELECT
user1,
user2,
COUNT(common_friend) AS common_friend_count
FROM friend_pair
GROUP BY user1, user2
HAVING COUNT(common_friend) >= 3;📌 考点：自连接的应用、分组去重逻辑、聚合统计。
题4：统计每个用户近7天累计登陆时长
📋 表结构（user_login_duration）：
| user_id | login_time | duration_min |
|---------|---------------------|--------------|
| 1 | 2025-01-01 08:00:00 | 30 |
| 1 | 2025-01-05 10:00:00 | 60 |
| 2 | 2025-01-06 14:00:00 | 45 |💡 解题思路：
使用SUM() OVER()滑动窗口函数，按用户分组、登陆时间排序，窗口范围限定为“当前行及之前 7 天”，累计计算登陆时长。
💻 SQL 实现：
SELECT
user_id,
login_time,
SUM(duration_min) OVER(
PARTITION BY user_id
ORDER BY login_time
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW -- 近7天滑动窗口
) AS last7d_total_duration
FROM user_login_duration;📌 考点：滑动窗口函数的范围限定（时间维度）、累计聚合统计。
题5：计算次日留存率
📖 定义：次日留存率 = 首日登陆且次日也登陆的用户数 / 首日登陆的总用户数（业务核心指标，衡量用户粘性）
📋 表结构：同题 1 的 user_login（以 2025-01-01 为首日统计）
💡 解题思路：
-
筛选首日（2025-01-01）登陆的用户集合；
-
筛选首日登陆且次日（2025-01-02）也登陆的用户集合；
-
用两个集合的用户数做除法，得到次日留存率。
💻 SQL 实现：
WITH first_login AS (
-- 首日（2025-01-01）登陆的用户
SELECT DISTINCT user_id
FROM user_login
WHERE login_date = '2025-01-01'
),
second_login AS (
-- 首日登陆且次日也登陆的用户
SELECT DISTINCT fl.user_id
FROM first_login fl
JOIN user_login ul
ON fl.user_id = ul.user_id
AND ul.login_date = '2025-01-02'
)
SELECT
-- 左连接确保首日所有用户都被统计，避免分母为0
COUNT(DISTINCT sl.user_id) / COUNT(DISTINCT fl.user_id) AS next_day_retention
FROM first_login fl
LEFT JOIN second_login sl ON fl.user_id = sl.user_id;🌟 拓展：7 日留存率计算逻辑类似，只需将次日日期改为首日 +7 天（如 2025-01-08）。
题6：每个类别下金额Top3的订单
📋 表结构（order_info）：
| order_id | category | amount |
|----------|----------|--------|
| 1 | A | 100 |
| 2 | A | 200 |
| 3 | A | 150 |
| 4 | A | 180 |
| 5 | B | 300 |💡 解题思路：
用DENSE_RANK()（或ROW_NUMBER()）窗口函数按类别分组、金额降序排序，筛选排名≤3 的订单（注意：DENSE_RANK 支持并列排名，ROW_NUMBER 会打破并列）。
💻 SQL 实现：
WITH order_rank AS (
SELECT
*,
-- DENSE_RANK()：并列排名；ROW_NUMBER()：唯一排名，根据需求选择
DENSE_RANK() OVER(PARTITION BY category ORDER BY amount DESC) AS rk
FROM order_info
)
SELECT *
FROM order_rank
WHERE rk <= 3;📌 考点：窗口排序函数的应用、TopN 问题的通用解法。
✍️ SQL核心考点梳理
-
窗口函数：ROW_NUMBER/RANK/DENSE_RANK/SUM OVER 等的灵活应用；
-
连续值判断：日期运算 + 分组聚合的核心逻辑（面试高频场景）；
-
多表关联：自连接、左连接的应用（如共同好友、留存率计算）；
-
滑动窗口统计：时间维度的累计 / 均值统计（近 N 天、近 N 小时）；
-
经典业务场景：TopN、行列转换、用户行为统计（留存、活跃、转化）。
🌟 SQL备考建议
重点练习业务场景题，理解“窗口函数 + 分组聚合”的核心逻辑，而非死记代码；每道题先梳理思路，再写代码，关注边界情况（如去重、分母为 0 等）。