数据分析面试SQL高频考点：窗口函数、自连接与留存率解题套路

我有一年面试估计被SQL卡了五六次：要么是窗口函数的ORDER BY写错，要么是自连接把自己绕晕，最气的一次是明明思路对了，却因为没处理NULL被面试官追问到哑口无言。后来刷了不少题、也复盘了身边几位转行成数据分析师的朋友的面试经历，发现SQL题其实套路非常固定。这篇文章不整虚的，就是把数据分析面试里反复出现的SQL题目、解题思路、以及那些面试官不会明说但特别爱考的细节，掰开揉碎讲清楚。

适合谁看：准备数据分析师、商业分析岗面试的同学，尤其是笔试和一面二面里会遇到手写SQL的岗位。如果你已经有基础（知道SELECT、JOIN、GROUP BY这三板斧），这篇文章能帮你把面试里的高频题型一次捋明白；如果你是零基础，也可以先把文中每个SQL当作模板抄下来，再慢慢理解。

1. 窗口函数：出镜率第一，翻车率也是第一

1.1 三兄弟的差别，面试官一追就露馅

窗口函数基本是数据分析面试的送分题，也是送命题。送分是因为它太常考了，几乎每家公司的笔试题都有；送命是因为很多人只会写ROW_NUMBER()，一旦面试官追问“RANK()和DENSE_RANK()的区别”，当场就卡壳。

先记结论：

举个例子，一张工资表有三个人的工资都是8000，另一个人是9000。用RANK()排序，9000排第1，三个8000并列排第2，接下来如果有7000的，它就排第5（因为第4名被跳过了）。而DENSE_RANK()算出来的排名是1、2、2、2、3。

面试官考察这个点，本质是想确认你是“背了语法”，还是真正理解排序背后的业务含义。比如“找出每个部门工资第二高的员工”这个需求，用RANK()和用DENSE_RANK()的结果可能完全不同——如果部门里有两个人都拿最高工资，用RANK()找第二高会直接漏人。这种业务口径层面的差异，恰恰是数据分析SQL和开发岗SQL最大的分水岭。

1.2 一道经典真题的完整拆解

某公司面试真题长这样：有一张员工表employee，包含id、name、department、salary四个字段，要求查出每个部门里工资排名前三的员工。

标准答案长这样：

SELECT department, name, salary FROM ( SELECT department, name, salary, DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank_num FROM employee ) t WHERE rank_num <= 3;

这题的关键点是两层查询：内层用窗口函数算排名，外层过滤排名≤3。为什么不能直接WHERE DENSE_RANK() <= 3？因为在SQL标准执行顺序里，WHERE是在SELECT之前执行的，窗口函数在SELECT阶段才计算，所以必须套一层子查询。

这里还有一个加分细节：用DENSE_RANK()还是RANK()？假设某个部门最高工资有三个人同时拿，如果题目说“工资排名前三的员工”，通常DENSE_RANK()更符合直觉——因为并列第一的三个人都应该被算进“排名前三”。如果面试官没有明确说明，你主动问一句“并列怎么算”，特别加分，因为这代表你有业务口径意识。

1.3 窗口大小：最容易被忽略的致命细节

窗口函数不只OVER(PARTITION BY ... ORDER BY ...)这一种形态，OVER()里还可以写窗口范围。比如计算“截至当天，每个人最近7天的累计消费金额”：

SELECT uid, consume_date, SUM(amount) OVER( PARTITION BY uid ORDER BY consume_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) AS recent_7d_sum FROM consume_log;

如果漏掉ROWS BETWEEN这段，整个窗口就会变成“从分区起点到当前行”的累计值，算出来的是全量累计而不是最近7天。这类进阶窗口题，面试官主要在考察你对OVER()语法细节的掌握程度，写出来的东西有没有歧义、边界条件能不能覆盖，比能不能跑通重要得多。

2. 连续登录天数：用一条“日期减去序号”的妙招

连续N天登录是数据分析面试里的常青树，我面过五家公司，两家都考了类似变体。这类题最考查“把业务问题翻译成数据逻辑”的抽象能力。

2.1 题目原型和第一直觉

题目一般这样描述：有一张登录表login_log，字段是uid和login_date（日期格式，同一用户一天可能有多条记录，需要先按天去重），求连续登录超过3天的用户。

很多人第一反应是暴力法——用程序语言遍历每一天，看相邻日期差是不是1。这个思路是对的，但SQL里没有“循环遍历”的天然工具，硬用游标写出来又长又难看，而且面试官最反感这种解法。正确路径是把连续区间转化成组编号问题。

2.2 核心思路：给每一段连续区间盖一个“组号”

具体操作分三步：

第一步，按用户和日期去重，确保一天只保留一条记录；第二步，给每个用户按日期排序，生成序号rn；第三步，用login_date减去rn，得到一个日期。

你可能会问：“日期减去序号”到底图什么？关键逻辑在于：如果是连续登录，日期会一天天增加，序号也会一天天增加，两者相减的结果始终不变；一旦断签，日期和序号的差值就会跳变。这个不变的差值，天然就成了“连续区间的组号”。

上SQL：

SELECT uid, date_sub(login_date, INTERVAL rn DAY) AS group_date FROM ( SELECT uid, login_date, ROW_NUMBER() OVER(PARTITION BY uid ORDER BY login_date) AS rn FROM ( SELECT uid, login_date FROM login_log GROUP BY uid, login_date ) a ) b GROUP BY uid, group_date HAVING COUNT(1) >= 3;

最内层去重，中间层打序号，外层按“用户+组号”分组，再用HAVING筛出超过3天的段。这题我强烈建议你亲手跑一遍，理解date_sub(login_date, rn)如何把动态的连续区间固化成静态的组号，后面几乎所有的“连续类”题目都能复用这一招。

2.3 面试里会遇到的变体

实际面试不考裸题，常见变体有这些：

• 连续登录的最大天数：让每个连续区间的组号跟着用户分组统计次数，再取MAX。
• 连续消费/连续签到：表字段从login_date换成pay_date或者sign_date，逻辑完全一样，唯一的坑是别忘了先对业务主键去重。
• 按月统计连续登录：比如“连续3个月有登录”，那就不能直接用日期减序号了，得用DATE_FORMAT(login_date, '%Y-%m')提取月份后，再对月份做减法并处理跨年问题。

尽量在面试前把“去重 → 排序 → 相减 → 分组计数”这套流程背到肌肉记忆，手写不卡壳，这题基本就稳了。

3. 自连接：面试官最爱埋井盖的一类题

自连接（Self Join）属于看起来简单、做起来翻车率极高的一类。面试官特别喜欢用自连接考察一个坎：你是否清楚表每次被连接时，会产生一个新“副本”的概念。很多人写自连接栽跟头，不是不会语法，而是脑子里没有“同一张表在JOIN两侧各扮演一个角色”的画面。

3.1 最典型的翻车现场：不写别名

考一道基础题：有一张成绩表score，字段为student_id、subject、score，求“每一科成绩高于该科平均分的学生”。

多数人上手就写：

SELECT s.student_id, s.subject, s.score FROM score s JOIN score avg ON s.subject = avg.subject WHERE s.score > avg.score;

这么写直接报错或结果错误——因为AVG(score)必须分组聚合，你不能在JOIN里直接把一张表当成“平均值表”。正确做法是先算每科平均分，再和原表连接：

SELECT s.student_id, s.subject, s.score, a.avg_score FROM score s JOIN ( SELECT subject, AVG(score) AS avg_score FROM score GROUP BY subject ) a ON s.subject = a.subject WHERE s.score > a.avg_score;

这题虽然不算纯自连接，但思路一脉相承：自连接的第一步永远是明确“表的两侧各自扮演什么角色”。比如上面这个写法里，s代表原始明细，子查询a代表聚合之后的平均水平表，两者职责不同，连接条件自然就清楚了。

3.2 “同一产品今天和昨天的销量对比”这类经典自连接

另一道高频题：有一张日销量表sales，字段是product_id、sale_date、amount，要求查出每天比前一天销量高的产品。

标准写法：

SELECT t1.product_id, t1.sale_date FROM sales t1 JOIN sales t2 ON t1.product_id = t2.product_id AND t1.sale_date = DATE_ADD(t2.sale_date, INTERVAL 1 DAY) WHERE t1.amount > t2.amount;

这里t1代表“今天”的角色，t2代表“昨天”的角色，连接条件同时锁定了产品相同、日期相差一天。写自连接时有心算验证的习惯特别重要：随手拿一行今天的数据，心里默念“它应该和昨天的哪一行连接”，一旦连接条件想明白了，结果基本不会错。

3.3 自连接为什么不写连接条件会爆炸

还有些面试题故意不写全连接条件，让你找问题。比如说“查出每个用户最近一次和上一次登录的时间差”，如果连接条件只写了uid相等，忘记写t1.login_date > t2.login_date，结果会是什么？

答案是多对多匹配，产生笛卡尔积。用户有10次登录，就会产生90对组合，数据量直接爆炸。面试官用这个坑问你的目的，其实是想看你能不能自主分辨“这一行该匹配哪些行”。经验口诀：自连接的WHERE和ON里，永远要有一个“双方角色不同”的条件，比如日期不相等、时间有先后、ID大小有区分，否则大概率是笛卡尔积。

4. 留存、漏斗这类指标题：不是写SQL，是定口径

数据分析岗面试的SQL题，越往后越不满足于“查数”，而要求你写出业务指标。留存率和漏斗转化是最高频的两个方向。

4.1 留存率：分母是谁，决定你拿不拿得到Offer

先说一道旧题：有一张用户活跃表active_log，包含uid、active_date，求“2025年1月1日当天新增的用户，在1月2日的次日留存率”。

很多人的第一反应是找“新用户”，但表里只有活跃数据，哪来的“新增”？这里就需要对“新增用户”做定义——一般理解是“该用户在全表里第一次活跃的日期”。这个定义过程，恰恰是数据分析岗面试官考察业务理解的核心环节。

可以这样拆解：

1. 找出每个用户的首次活跃日期（MIN(active_date) GROUP BY uid）；
2. 筛出首次活跃日期为1月1日的用户，作为“当日新增用户”；
3. 再和1月2日的活跃表做LEFT JOIN，看这部分用户第二天有没有回来。

SQL大致长这样：

SELECT COUNT(DISTINCT a.uid) AS new_users, COUNT(DISTINCT b.uid) AS retained_users, COUNT(DISTINCT b.uid) / COUNT(DISTINCT a.uid) AS retention_rate FROM ( SELECT uid, MIN(active_date) AS first_date FROM active_log GROUP BY uid ) a LEFT JOIN active_log b ON a.uid = b.uid AND b.active_date = DATE_ADD(a.first_date, INTERVAL 1 DAY) WHERE a.first_date = '2025-01-01';

注意两个细节：一是要用COUNT(DISTINCT uid)而不是COUNT(uid)，防止同一个人当天多个活跃记录重复计数；二是次日留存率的分母是“1月1日的新增用户”，不是“1月1日的全体活跃用户”。面试官常会故意把这几个口径混在一起，你答题时主动声明“分母用新增用户”，会显得专业得多。

4.2 漏斗转化：每一步筛选都别把用户“弄丢”

漏斗题典型描述：业务表记录了用户行为流水behavior，字段有uid、behavior_type（如'view'、'add_cart'、'pay'）、create_time，求从“浏览”到“加购”再到“支付”的转化率。

思路不复杂，按行为类型分步计数，但有个大坑：漏斗天然要求每一步的行为发生在下一步之前，如果直接WHERE behavior_type IN ('view','add_cart','pay')再分组，会把“先支付后浏览”的异常数据也算进去。稳妥做法是用条件聚合，把三个行为各算各的：

SELECT COUNT(DISTINCT CASE WHEN behavior_type='view' THEN uid END) AS view_users, COUNT(DISTINCT CASE WHEN behavior_type='add_cart' THEN uid END) AS cart_users, COUNT(DISTINCT CASE WHEN behavior_type='pay' THEN uid END) AS pay_users FROM behavior WHERE create_time >= '2025-01-01' AND create_time < '2025-02-01';

如果要严格按事件先后顺序写漏斗，可以用自连接或者窗口函数取时间最早的'view'行为作为起点，再做时间序列上的逐步过滤，复杂度会上去不少。多数面试场景下，面试官更在意你先说出“漏斗各层口径要严格定义好”这句话，再写出上面的条件聚合，分数就不会低。

4.3 指标题的通用答题节奏

我的个人经验是，这种题千万别上来就写SQL。先停顿两秒，口头说三句话：第一，说明口径怎么定义；第二，说明时间范围怎么圈定；第三，说明去重逻辑。然后再动笔。面试官听到这三句话，就算SQL有点小瑕疵，也会认为你具备数据分析的基本素养。

5. Top N、第N高、中位数：老题目新解法，赢在思路稳

“找第二高的工资”“找每个部门工资Top 3”“求薪资中位数”这几道题，属于SQL面试母题，几乎每个准备面试的人都背过。但很多人只会背一种写法，一旦面试官换条件就傻眼。这里把几种主流解法放在一起对比，你就知道为什么窗口函数解法越来越受青睐。

5.1 第二高工资：老古董解法和窗口函数解法

经典题：employee表只有id和salary两个字段，求第二高的工资。常见的传统解法是：

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;

这个写法本身没问题，但有个前提：你得刻意加上DISTINCT，否则两个相同最高工资并列时，LIMIT 1 OFFSET 1取到的还是同一个最高工资，而不是真正的第二高。这个细节十个人里有一半会漏。

用窗口函数的稳定版本：

SELECT DISTINCT salary FROM ( SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) AS rn FROM employee ) t WHERE rn = 2;

窗口函数解法的好处是把“排名规则”显式写出来了：并列怎么算、跳过不跳过，都一目了然。面试官如果追问“如果你要并列也算同一个名次”，你直接说用DENSE_RANK()，这就是可控性和可解释性。

5.2 中位数：两行代码的差异，结果天壤之别

中位数题也是热门：求员工薪资的中位数。多数数据库直接支持PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)，如果你用的数据库支持，这是最优解。但面试手写SQL往往不允许用这种高级函数，得用通用解法。

比较常见的思路是同时取“正数第N个”和“倒数第N个”求平均：

SELECT AVG(salary) AS median FROM ( SELECT salary, ROW_NUMBER() OVER(ORDER BY salary) AS rn, COUNT(*) OVER() AS cnt FROM employee ) t WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2));

这个写法对奇数、偶数个数据都成立：奇数个时FLOOR和CEIL指向同一个数，偶数个时指向中间两个数，再取平均值。理解它的关键在于“中位数就是排序后正中间的那一个或两个值”，看起来用了窗口函数，本质仍然是排序和位置索引的数学题。

6. 去重和NULL：越是基础的地方越拉分

很多面试者把重心放在复杂题型上，结果在简单的去重和NULL处理上栽了跟头。我见过不止一次，候选人窗口函数写得飞起，却在“统计用户数”这种题里用错COUNT(DISTINCT)，当场被问出一身汗。这里把最常见的几个坑单独拎出来说。

6.1 去重别只会DISTINCT

DISTINCT是去重最简单的方式，但不是万能的。比如你要对订单表orders按user_id去重后保留最新一笔订单的完整信息，用SELECT DISTINCT user_id ...只能选出用户，其他字段要么带不齐，要么得写一堆GROUP BY。这时候用窗口函数去重更干净：

SELECT user_id, order_id, amount, order_time FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_time DESC) AS rn FROM orders ) t WHERE rn = 1;

这就是数据分析岗位常用的“按某字段去重并保留最新/最老记录”套路。面试官出这道题，往往是想确认：你只懂得DISTINCT语法，还是知道不同去重场景的取舍。

还有一个容易被问到的细节：COUNT(DISTINCT a, b)可以把两个字段组合起来去重，这在统计“有多少人同一天下了单”这类需求里会很方便。

6.2COUNT和SUM遇到NULL，表现完全不一样

这是一个几乎每周都会在工作里踩到的坑。记住三条：

• COUNT(*)数的是行数，NULL也算；
• COUNT(col)把col为NULL的行自动排除；
• SUM(col)遇到col全是NULL时结果不是0，而是NULL。

举个例子，统计订单表中成交金额总和的题目：

SELECT COUNT(order_id) AS order_cnt, SUM(amount) AS total_amount FROM orders;

如果有一行订单amount字段是NULL（比如退款订单没有填写金额），SUM(amount)会直接忽略它，结果实际没错，但你要是回答“这是0”，面试官就会追问NULL的语义。更坑的是在WHERE条件里比较：WHERE amount = NULL永远查不出任何行，必须用IS NULL。这个错误在笔试里极其常见，写得再复杂也拿不到分。

6.3 面试官追问“如何清理脏数据”时的回答套路

别满足于写出SELECT * FROM table WHERE col IS NOT NULL。我建议你回答时多提一句：“先确认NULL是业务上合法缺失还是录入异常，如果是缺失金额，就按业务规则补0或剔除；如果是编码异常，就要溯源修复。”把“数据清洗”拔高到“业务理解”的层面，面试官对你的评价会明显不同。

7. 一次完整翻车复盘：窗口、连接、去重同时踩坑

前面讲的都是单个考点，真实面试里题目往往是组合拳。分享一道我某次面试遇到的完整题目，把三个坑全都踩了一遍，你应该能从中学到排查思路而不只是答案。

题目：有一张用户行为表user_behavior，字段为uid、event_date、event_type（'注册'、'登录'、'下单'），要求在2025年1月1日至2025年1月15日期间，统计“每个用户从注册到首次下单花了多少天”，并输出那些超过7天才下单的用户。

我当时的思路是先找注册日期，再找首次下单日期，然后求差。结果第一版SQL写出来，面试官看了一眼就指出三个问题：

第一，窗口函数排序方向错了。找首次下单时我写了ORDER BY event_date DESC取了ROW_NUMBER()=1，把最后一次下单当成了第一次，逻辑完全反了。

第二，日期范围过滤位置不对。我用了WHERE event_date BETWEEN '2025-01-01' AND '2025-01-15'，把注册和下单都限制在这个区间，结果是1月14日注册的用户虽然下单在窗口内，但部分用户的注册日期被排除出去，漏人。

第三，没有对去重逻辑做检查。用户同一天可能在注册后又下单，产生多条event_date相同的记录，我没加DISTINCT或PARTITION BY event_date处理，导致COUNT或MIN结果偏差。

正确的思路应该是：先分别取出“注册时间”和“首次下单时间”两套子查询，再对用户做连接，最后算天数差。

SELECT r.uid, DATEDIFF(o.first_order_date, r.reg_date) AS day_diff FROM ( SELECT uid, MIN(event_date) AS reg_date FROM user_behavior WHERE event_type = '注册' GROUP BY uid ) r JOIN ( SELECT uid, MIN(event_date) AS first_order_date FROM user_behavior WHERE event_type = '下单' GROUP BY uid ) o ON r.uid = o.uid WHERE DATEDIFF(o.first_order_date, r.reg_date) > 7;

这题复盘下来，最大的教训是：复杂SQL永远先拆成“几个简单查询”，再用连接组合起来。一旦你试图用一个大查询包揽所有条件，窗口函数、过滤时机、去重这三个坑就会一起出现，很难一次性写对。面试时如果时间紧，先写出分步结构，再说“我再考虑要不要合并”，反而比装模作样写完一大段更能体现工程素养。

8. 准备数据分析SQL面试，我的几条实在建议

如果你正在准备面试，除了刷题，还有几条来自踩坑后的经验值得参考。

一是把每个题目用“业务故事”记住，而不是背SQL。比如连续登录题，本质是“连续出现的日期段怎么分组”；第N高工资题，本质是“排序后怎么按位置取值”；留存率题，本质是“分母和分子分别是谁”。面试现场遇到新题，先把它翻译成这些已知问题类型，思路会顺很多。

二是给自己设计“追问清单”。每道题写完，先问自己三个问题：并列怎么处理？NULL怎么办？数据有重复怎么办？这三个问题几乎对应面试官80%的追问。提前想好答案，就不会在追问环节卡壳。

三是练习写“解释型SQL”。复试环节不只看答案正确与否，面试官会让你一句话说明这段逻辑。平时训练时，每写完一段SQL，尝试用一句话向一个不懂SQL的人讲清楚：这段代码先做了什么，再做了什么，为什么这么做。这个能力一旦养成，面试里“边写边讲”就会非常自然。

四是别忽视手写规范。线上笔试一时看不清缩进和大小写，但现场白板面试或者共享文档编码时，整齐的格式会让面试官更容易跟上你的思路。我习惯把子查询缩进两格，SELECT字段与FROM对齐，JOIN ... ON单独一行——这种细节不影响对错，但影响对方读你代码的心情。

SQL面试说到底，考的不是背诵量，而是能不能把业务问题拆成清晰的数据步骤。你不需要刷几百道题，把窗口函数、自连接、连续区间、指标口径、去重与NULL这五块吃透，覆盖大部分数据分析面试题已经绰绰有余。剩下的，就是多在自己电脑上把每道题亲手跑一遍，错了就多看执行结果，把“看懂”变成“会写”。