电商数据分析SQL面试真题解析：从连续登录到留存率的解题思路

回顾前几个月集中准备数据分析岗位面试的经历，最有感触的其实是SQL这道坎。投过头部电商平台数据分析岗的都知道，笔试和一面基本就是SQL题专场，甚至到了二面三面，面试官还会临时抛一道现场写SQL的场景题。我前前后后刷了不下两百道题，也帮忙整理过模拟面试题集，过程中踩了不少坑，也总结出了一套应对这类面试题的方法。这篇就把我整理的电商数据分析SQL面试高频真题、解题思路和容易翻车的地方一次性说透。

如果你正在准备数据岗面试，或者想检验自己的SQL水平是否达到电商数据分析的实操要求，这篇文章可以帮你省下不少盲目刷题的时间。整体会围绕“题目到底在考什么”和“怎么写才能拿分”两条线展开，每道题都配了可运行的标准SQL实现和面试官追问的回答思路。

1. 电商数据分析SQL面试到底在筛什么

1.1 表面考语法，实际考业务拆解能力

很多候选人以为SQL面试是考记忆、考函数、考语法，其实面试官真正想看的，是你拿到一个模糊的业务问题时能不能把它拆成清晰的逻辑步骤。就拿“计算复购率”这个需求来说，业务口径就有好几种定义：是按自然月计算，还是按用户首次下单后N天内计算？复购是按订单数算，还是按下单天数算？不同口径得出的数字可能差好几个点，面试官要看到的是你对业务口径的判断和主动确认的动作，而不只是会写一个count。

我见过不少候选人，窗口函数、连表、子查询都用得很熟，但一遇到“哪些用户在连续N天内有下单”这种实际问题就开始手忙脚乱。根本原因就是平时刷题都在做纯语法练习，没有养成“先拆业务、再写SQL”的习惯。电商数据分析岗位对SQL的要求不只是会取数，还要能通过SQL理解业务变化的原因，这才是面试环节设置SQL题的真实意图。

1.2 从岗位职责反推考点权重

从多家电商公司的数据分析岗JD来看，SQL相关的职责描述通常包括：日常数据提取与分析、业务监控与异常诊断、专题分析与ABTest支撑、数据报表开发。对应到面试题，考点分布大概是这样：

这里有一个容易被忽略的点：面试官不会只关心你写出来的结果对不对，更会关注你写的SQL是不是够简洁、够高效。比如能用一次窗口函数解决的问题，就不要嵌套三层子查询；能用一个group by完成的统计，就不要先查明细再在外面包一层。这些都是面试现场拿印象分的关键。

1.3 常见的SQL面试流程与考察节奏

电商数据分析的面试通常分两轮技术考察。第一轮是笔试或线上测评，一般给4到6道SQL题，限时60到90分钟，覆盖取数、聚合、窗口函数、业务指标计算这几个核心模块。第二轮是业务面，面试官会结合具体业务场景现场提问，比如“给你一张订单表和一张退款表，怎么分析退款率异常的原因”，然后要求你边讲思路边写SQL。

我在模拟面试中发现一个规律：大部分候选人笔试能写完，但一到现场口头讲题就卡壳。这其实是可以刻意练习的。你需要在写每一步时都能说清楚“我为什么要做这一步转换”“这个指标的业务含义是什么”“如果数据量很大这个写法会不会有问题”。而这种表达能力，只能通过大量做真题、复盘思路来训练。

2. 高频真题拆解：连表、聚合与业务口径

2.1 题目1：连续N天下单的用户

这是各大电商平台最爱出的题目之一，考察的是窗口函数加时间函数的组合应用。原题一般是给一张下单记录表，包含用户ID和下单日期，要求找出连续3天及以上有下单记录的用户。

考察点：对窗口函数row_number的理解、日期差的计算、分组聚合的粒度切换。

标准解法是这样的：

select user_id from ( select user_id, order_date, date_sub(order_date, row_number() over (partition by user_id order by order_date)) as date_group from ( select distinct user_id, order_date from order_table ) t1 ) t2 group by user_id, date_group having count(1) >= 3;

这个解法的核心逻辑是“日期减去序号是否变化”。如果用户1号、2号、3号连续下单，row_number分别生成1、2、3，减去之后得到的都是同一天，说明这是连续区间。如果中间断了一天，减出来的日期就会发生变化。最后的having count来判断连续天数是否达标。

这道题有两点特别容易踩坑。第一，去重过程不能少，因为用户一天可能有多笔订单，如果不先去重，会产生错误的连续区间。第二，date_sub函数在不同数据库里的写法略有差异，比如某些场景下也可以写成 order_date - row_number()，但在Hive里建议使用date_sub明确转换。

面试官追问：如果要求的是“连续下单天数最多的前10名用户”，怎么写？

这实际上是把连续区间的逻辑封装成子查询，再计算每个用户最大的连续天数，然后排序取前10：

select user_id, max(continuous_days) as max_days from ( select user_id, date_group, count(1) as continuous_days from (...) group by user_id, date_group ) a group by user_id order by max_days desc limit 10;

我在讲解时通常会补一句：如果一张表里有多家店铺的数据，记得先过滤店铺维度，否则用户在不同店铺下单也会被算成连续，这个和业务上的“全站连续下单”定义是矛盾的。这种细节多说一句，面试官对你的印象分会明显上涨。

2.2 题目2：每个品类下销售额Top3的商品

这道题考察窗口函数中的排名函数，以及“先聚合、后排名”的逻辑顺序。原题给一张订单明细表、一张商品信息表，要求统计每个品类下销售额排名前3的商品。

考察点：join关联、group by聚合、rank或dense_rank的选用、TopN的取法。

先做聚合再做排名是核心思路：

select category_id, product_id, sales_amount, sales_rank from ( select b.category_id, b.product_id, sum(a.order_amount) as sales_amount, row_number() over (partition by b.category_id order by sum(a.order_amount) desc) as sales_rank from fact_order a left join dim_product b on a.product_id = b.product_id group by b.category_id, b.product_id ) t where sales_rank <= 3;

这里有一个性能上的注意点：如果数据量很大，join之后再分组会比先分组再join更慢。更优的做法是先在订单表里按product_id聚合出销售额，然后再join商品表获取品类信息，最后再开窗排名。我在实际面试中会这样优化：

select b.category_id, a.product_id, a.sales_amount from ( select product_id, sum(order_amount) as sales_amount from fact_order group by product_id ) a left join dim_product b on a.product_id = b.product_id ;

然后在内存表基础上做窗口排名。这个顺序调整，面试官问到你“数据量大了怎么办”时，可以直接抛出来，很容易加分。

排名函数的选择也是高频追问点：row_number、rank、dense_rank分别适用于什么场景。简单说，row_number生成唯一连续序号，rank并列会跳号，dense_rank并列不跳号。业务上如果明确要取Top3且允许并列，可以用dense_rank；如果要求“不管是否并列只取3条”，用row_number或者rank加limit都可以。部分候选人在这道题上会把聚合和窗口函数写在一个层次上，导致语法报错，这块也需要提前练习到肌肉记忆的程度。

2.3 题目3：GMV月度同比环比计算

电商面试题里，指标计算出现的频率极高，其中GMV的同比环比又是必考中的必考。原题一般给订单表，包含订单日期和订单金额，要求按月统计GMV，并计算每个月的环比增长率和同比增长率。

考察点：日期格式化、窗口函数lag的用法、同比环比的业务口径。

select month_id, gmv, lag(gmv, 1) over (order by month_id) as prev_month_gmv, round((gmv - lag(gmv, 1) over (order by month_id)) / lag(gmv, 1) over (order by month_id), 4) as mom_rate from ( select date_format(order_date, '%Y-%m') as month_id, sum(order_amount) as gmv from fact_order where order_status = 'completed' group by date_format(order_date, '%Y-%m') ) t;

同比的逻辑一样，只不过lag的偏移量改成12，同时需要保证数据跨度至少覆盖13个月：

lag(gmv, 12) over (order by month_id) as prev_year_gmv

这道题有多个细节可以聊。第一，如果订单表里还包含退款订单，是否需要剔除？通常GMV定义是拍下订单金额，而GTV或实际成交额需要剔除退款，这就要看业务口径了。面试时主动确认口径比闷头写完整道题要安全得多。第二，lag的默认值问题，如果没有前一期数据，返回NULL，这就需要在计算时用case when处理。第三，环比计算除数为0时的处理，线上数据里偶尔会有某个月份GMV为0的情况，直接用除法会导致返回NULL或报错，稳妥做法是配合nullif处理。

我在模拟面试时一定会追问：“如果我要按周统计，日期函数应该怎么改？”答案是把date_format的粒度从‘%Y-%m’改成‘%Y-%W’或通过date_sub计算到周一，这既能考察候选人对日期函数的熟练度，也能看出他有没有真正理解聚合粒度这个概念。

2.4 题目4：用户累计消费金额与消费等级

这类题考察累计计算，一般给订单表，要求计算每个用户截至每个订单日期的累计消费金额，并据此划分消费等级。

考察点：窗口函数sum与order by的组合、累计计算与窗口边界控制。

select user_id, order_date, order_amount, sum(order_amount) over (partition by user_id order by order_date) as cumulative_amount from fact_order ;

接下来基于累计金额划分等级，比如0到1000为普通用户，1000到5000为银牌用户，5000以上为金牌用户：

select user_id, cumulative_amount, case when cumulative_amount < 1000 then '普通用户' when cumulative_amount < 5000 then '银牌用户' else '金牌用户' end as user_level from ( select user_id, max(cumulative_amount) as cumulative_amount from ( select user_id, sum(order_amount) over (partition by user_id order by order_date) as cumulative_amount from fact_order ) a group by user_id ) t;

这里有几个值得展开讲的重难点。

窗口函数sum() over(partition by ... order by ...) 默认的窗口范围是从分区的第一行到当前行，这正是累计计算的原理。如果你在order by后面写了rows between unbounded preceding and current row，含义更明确，也是等价写法。

第二个重点是“按用户取截至当前最大累计金额”。这里很多人会直接用“最新一笔订单的累计金额“来代表用户当前累计消费，这在订单表存在跨天更新时会有问题。稳妥做法是先算累计序列，再按用户取max，或者取最后一条订单的累计值。但在面试中，取max更稳妥，因为不受明细表当天更新状态的影响。

还有一点关于case when的顺序，条件的先后会直接影响结果，比如划分区间时要把大区间写在前面，否则前面的条件会把后面区间的数据吞掉。这种低级错误其实很容易出现在面试现场，因为紧张而忘记检查。我建议所有候选人最后都要自查一遍边界值，比如1000元整到底属于哪个等级。

3. 进阶业务场景：留存、漏斗与归因分析

3.1 题目5：用户留存率计算

留存率是电商和互联网数据分析面试中我认为最值得深究的题目之一。它考察的不只是SQL能力，还有对留存R1、R3、R7等业务口径的理解。原题给用户注册表和登录（或下单）记录表，要求计算某个月新增用户在注册后第1天、第3天、第7天的留存率。

考察点：日期差计算、条件聚合、多表关联的粒度控制。

一种通用写法是先算每个用户的注册日期与活跃日期之差，再判断是否等于1、3、7：

select b.reg_month, count(distinct a.user_id) as new_users, count(distinct if(datediff(a.active_date, b.reg_date) = 1, a.user_id, null)) as retain_day1, count(distinct if(datediff(a.active_date, b.reg_date) = 3, a.user_id, null)) as retain_day3, count(distinct if(datediff(a.active_date, b.reg_date) = 7, a.user_id, null)) as retain_day7 from active_table a join ( select user_id, reg_date, date_format(reg_date, '%Y-%m') as reg_month from user_reg_table where date_format(reg_date, '%Y-%m') = '2025-03' ) b on a.user_id = b.user_id group by b.reg_month;

这道题我特别喜欢用来考察候选人的细节敏感度。很多人在计算留存时直接用“注册日期+1=活跃日期”来判断，这在小数据量时没问题，但如果活跃日期有跨天偏移、或者活跃表记录的是精确到秒的时间戳，就必须先做date的截断处理。另一个常见错误是用了inner join而不是left join，导致没有后续活跃行为的注册用户直接被过滤掉，留存率虚高。

面试官追问通常会往两个方向走：一是定义“活跃”的口径，比如是有登录行为还是要有下单行为；二是留存表的输出格式，是要宽表（每列是一个留存天数）还是窄表（每天一行留存记录）。作为候选人，你可以主动给出宽窄表两种写法，并说明各自适合什么下游场景。这个主动性会拉开档次。

3.2 题目6：用户下单漏斗分析

漏斗分析是电商业务的数据分析岗面试题里比较进阶的题目。原题一般给一张用户行为日志表，包含用户ID、行为时间、行为类型（曝光、点击、加购、下单、支付），要求计算各个环节之间的转化率。

考察点：条件聚合、子查询、转化率口径定义。

我常用的写法是先用条件聚合，把每个用户的每个行为是否有转化统计出来，再做漏斗：

select sum(if(is_expose = 1, 1, 0)) as step1, sum(if(is_click = 1, 1, 0)) as step2, sum(if(is_addcart = 1, 1, 0)) as step3, sum(if(is_order = 1, 1, 0)) as step4, sum(if(is_pay = 1, 1, 0)) as step5 from ( select user_id, max(if(action = 'expose', 1, 0)) as is_expose, max(if(action = 'click', 1, 0)) as is_click, max(if(action = 'addcart', 1, 0)) as is_addcart, max(if(action = 'order', 1, 0)) as is_order, max(if(action = 'pay', 1, 0)) as is_pay from user_behavior_log where dt = '2025-03-15' group by user_id ) a;

然后计算每两层之间的转化率。这里的核心注意点是：漏斗分析里每一层的用户集合应该是上一层的子集。如果直接用多个count分别统计，得到的其实是“总用户中有多少人发生了某行为”，而不是真正的漏斗。更严谨的做法是把每一步的约束条件都限定在前置行为发生的用户之内。这个点很多候选人都会忽略，一旦你主动指出来，面试官通常都会认可。

另外，漏斗分析还有“时间窗口”这个维度，比如用户从曝光到下单要在多久之内才算有效转化。如果要看一天内完成的转化，需要给行为时间加时间差限制。这种扩展问题的价值在于展示你对业务分析场景的理解深度。

3.3 题目7：订单退款率异常归因分析

这类题目更像业务面试里的综合题。面试官会给一张订单表和一张退款表，并告诉你“最近一周退款率明显上升，请分析可能的原因”，要求用SQL配合分析思路作答。

考察点：从数据角度拆解业务问题的框架能力。

需要写SQL的部分通常是计算不同维度下的退款率变化，比如按商品品类、按支付渠道、按用户等级、按城市：

select b.category_name, count(distinct a.order_id) as order_cnt, count(distinct c.refund_order_id) as refund_cnt, count(distinct c.refund_order_id) / count(distinct a.order_id) as refund_rate from fact_order a left join ( select order_id from fact_refund where refund_status = 'success' and refund_date between '2025-03-08' and '2025-03-15' ) c on a.order_id = c.order_id left join dim_product b on a.product_id = b.product_id where a.order_date between '2025-03-08' and '2025-03-15' group by b.category_name order by refund_rate desc;

分析思路上，我会建议候选人按“内部因素”和“外部因素”两层框架来展开：内部因素是商品或运营的变化，比如某些品类的商品质量出问题、大促后的退款高峰期、某渠道的支付成功率下降；外部因素包括竞品促销带来的用户比价行为、天气等环境因素的影响。如果你能结合SQL查询结果说“先按品类看哪些品类异常，再看退款原因分布”，面试官会认为你有完整的诊断思路，而不仅仅是会写两条SQL。

4. 面试现场容易翻车的五个SQL细节

4.1 聚合粒度搞混

这是排名第一的翻车原因。同一个指标，由于关联表不同，行数会翻倍。比如订单明细表join商品维表一般是一对一，没有风险；但订单表join退款表可能是一对多，因为一个订单可能有多次部分退款记录，导致金额sum重复计算。解决思路是先在子查询里把退款表按订单ID聚合好，再与订单表关联，这是我在写所有多表关联前都会先想一遍的关键逻辑。

4.2 窗口函数使用边界

很多候选人会把窗口函数直接写在where条件里，或者在一个查询里同时使用聚合函数和普通字段，而没有用group by。这些都属于SQL基础不扎实的表现。窗口函数的执行顺序要记住：在group by和having之后，在select阶段执行，因此不能在where里过滤窗口函数的结果，只能在外面包一层子查询。这条规则几乎每场面试都会碰到。

4.3 NULL值处理路径不清

计算转化率、占比时，NULL值经常被忽略。比如left join过程中右表没有匹配记录，相关字段就是NULL。如果直接拿NULL做分母或判断条件，结果可能完全错误。建议养成三个习惯：聚合前用coalesce或ifnull补默认值；判断时用is null而不是= null；除法运算中分母使用nullif避免返回null或报错。

4.4 日期字段类型不统一

订单日期有的是datetime，有的是string，有的是date，不同表之间join时经常出现类型不一致问题。面试时不一定会跑真实数据，但候选人最好主动说出“这里需要先把日期格式统一”这类话。常见的处理方式是date_format或cast，也要注意Hive和MySQL在日期函数上的差异。

4.5 只写答案不讲思路

这是面试中最致命的一个问题。面试官让你现场写题，不只是想看最终结果，更想看你如何思考。候选人应该先用自己的话复述一遍题目理解，再明确业务口径，然后给出整体思路，最后动手写SQL。写完后主动检查边界情况，比如“如果这个用户没有下单记录怎么办”“如果同一商品有多条记录会不会重复计算”。这一套流程走下来，比单纯写对一道题要加分得多。

5. 备考路线与刷题建议

5.1 三阶段备考规划

第一阶段是基础语法巩固，重点刷单表查询、聚合、排序、连接查询，确保没有语法盲区。这一阶段可以用面试高频题直接练，不需要重复做简单的练习题，而是直接挑战真题难度。

第二阶段是业务场景专项，集中练窗口函数和复杂业务指标。连续登录、留存率、复购率、TopN、同比环比、漏斗转化，每类至少做五道以上。做完一道题后马上复盘：还有没有更优的写法？换个业务口径能不能适用？如果数据量翻100倍，这个写法还能跑吗？

第三阶段是模拟面试与表达训练。建议找完全陌生的题目，限时20分钟写完并口头讲解。讲解时可以按照“确认口径 → 拆解思路 → 写SQL → 检查边界→ 数据验证”这个顺序。反复练十道题以上，能解决大部分面试紧张和表达混乱的问题。

5.2 如何利用模拟数据建数据集

我比较推荐自己造一套模拟电商数据来练，因为这样能完整覆盖所有题型。建三张表就够了：用户表（user_id、reg_date、user_level、city）、订单表（order_id、user_id、product_id、order_date、order_amount、order_status）、商品表（product_id、product_name、category_id、category_name）。再加一张用户行为日志表，用来练漏斗分析和留存。

模拟数据规模建议造10万用户、50万订单、1万商品，既能在本地跑得动，又能覆盖数据倾斜的极端场景。自己造数据的过程本身就是在练SQL，因为你需要用SQL生成随机数据、插入表、做数据校验。

5.3 面试准备中的两个加分习惯

第一个加分习惯是“主动给口径”。任何一道指标计算题，先说出“这里GMV的口径我按支付成功金额来算”或“退款订单我会单独标记”，比闷头写SQL强得多。面试官会觉得你有真实业务经验。

第二个加分习惯是“主动提性能优化”。写完SQL后加一句“如果这个表是每天千万级的增量，我会先把过滤条件条件下推，再做join和聚合”，这个动作在面试里出现一次就足够让面试官记住你。我自己带过的模拟面试候选人中，做到这一条的通过率明显高于平均水平。

6. 个人经验分享与最后提醒

写到这里，再分享几个我在真实面试和带人过程中反复验证过的体会。

SQL题完全没有必要追求“答案唯一”。同一个需求有十种写法，面试官更看重的是思路和取舍。你不一定要把最优解写在第一版，但你要能解释清楚你的第一版哪些地方还可以优化，以及你为什么在当前业务量级下选了这个写法。

另外一个很重要的心态是：遇到完全没见过的题,先别慌。电商数据分析的SQL题再怎么变，底层就是“多表关联、聚合统计、窗口函数、时间处理”这几板斧。把题目翻译成业务逻辑，再翻译成SQL语法，大部分题都能拆出来。我见过不少候选人在面试里写出正确答案后自己都不信：“这个居然这样就解出来了。”实际上SQL面试题并没有想象中那么高不可攀。

最后，如果你现在还在为一道连续登录题卡住，或者拿到留存率题目没有头绪，可以先放下焦虑。把基础题刷熟，每道题都做三遍——第一遍独立写，第二遍对着答案改，第三遍隔两天重新盲写。这个过程虽然笨，但比任何取巧的方法都稳定有效。希望这套真题拆解对你有所帮助，也欢迎把你的面试真题和解题思路拿来交流，互相补全盲区。