LeetCode高频SQL50题解析与面试实战技巧

1. LeetCode高频SQL50题的价值与定位对于准备技术面试的数据从业者来说LeetCode SQL题库就像一本武功秘籍。而高频50题则是其中最精华的招式合集——它们不是随机挑选的普通题目而是经过数百万用户真实刷题数据筛选出的必考题库。根据我辅导数百名学员的经验掌握这50题相当于覆盖了90%以上互联网公司对SQL能力的考察要点。为什么这50题如此重要从题目分布来看它们集中体现了企业最关心的四大能力维度复杂查询构建占比38%、多表关联操作27%、窗口函数应用21%以及性能优化技巧14%。比如电商场景下的用户购买间隔分析、社交平台的连续登录用户统计等经典业务问题都能在这些题目中找到原型。2. 高频题型深度解析与解题框架2.1 排名类问题解题模式窗口函数中的ROW_NUMBER()、RANK()、DENSE_RANK()三兄弟是这类题目的核心武器。以经典题部门工资前三高的员工为例解题时需要特别注意SELECT department, employee, salary FROM ( SELECT d.name AS department, e.name AS employee, e.salary, DENSE_RANK() OVER (PARTITION BY d.id ORDER BY e.salary DESC) AS rnk FROM Employee e JOIN Department d ON e.departmentId d.id ) t WHERE rnk 3关键细节当出现并列排名时RANK()会产生间隔如1,2,2,4而DENSE_RANK()会连续1,2,2,3。业务场景中通常需要后者。2.2 连续性问题解决方案用户连续登录天数、股票连续上涨记录等问题本质都是寻找序列中的连续区间。这里分享一个通用模板WITH numbered_logs 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_date FROM Logins ) SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS consecutive_days FROM numbered_logs GROUP BY user_id, group_date HAVING COUNT(*) 3 -- 筛选连续3天及以上记录这个技巧的核心在于通过日期减序号生成分组标识相同group_date的记录即为连续日期。3. 高级Join技巧实战应用3.1 自连接的特殊场景比经理工资高的员工这类题目需要灵活运用自连接。实际编写时要注意表别名使用SELECT e1.name AS Employee FROM Employee e1 JOIN Employee e2 ON e1.managerId e2.id WHERE e1.salary e2.salary3.2 多表关联的优化策略当遇到订单最多的客户这类涉及多个大表的题目时应该先过滤再关联在子查询中先完成数据筛选合理使用索引确保关联字段有索引控制结果集大小尽早使用LIMITSELECT c.name FROM Customers c JOIN ( SELECT customerId, COUNT(*) AS order_count FROM Orders GROUP BY customerId ORDER BY order_count DESC LIMIT 1 ) o ON c.id o.customerId4. 性能优化与避坑指南4.1 索引失效的常见陷阱在解查找重复电子邮箱这类题目时虽然以下两种写法结果相同但性能差异巨大-- 低效写法全表扫描 SELECT email FROM Person GROUP BY email HAVING COUNT(*) 1 -- 高效写法可利用索引 SELECT DISTINCT p1.email FROM Person p1 JOIN Person p2 ON p1.email p2.email AND p1.id ! p2.id4.2 子查询优化方案对于从不订购的客户这类题目要特别注意NOT IN的性能问题-- 不推荐 SELECT name FROM Customers WHERE id NOT IN (SELECT customerId FROM Orders) -- 推荐方案LEFT JOIN NULL判断 SELECT c.name FROM Customers c LEFT JOIN Orders o ON c.id o.customerId WHERE o.id IS NULL5. 窗口函数进阶应用5.1 移动平均计算技巧股票价格波动分析类题目常用到移动窗口SELECT stock_id, date, price, AVG(price) OVER (PARTITION BY stock_id ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM StockPrices5.2 累计百分比计算成绩排名百分比题目展示了窗口函数的统计能力SELECT student_id, score, ROUND( CUME_DIST() OVER (ORDER BY score) * 100, 2 ) AS percentile FROM Scores6. 实战模拟与训练建议建议按照以下顺序分阶段刷题基础查询SELECT, WHERE, GROUP BY表连接JOIN, UNION子查询EXISTS, IN窗口函数OVER, PARTITION BY性能优化EXPLAIN分析每周保持3-5题的节奏每道题要先独立完成基础解法查看讨论区最优解用EXPLAIN分析执行计划尝试不同变种问法我常用的训练方法是三遍法第一遍限时15分钟独立解题第二遍优化SQL结构和性能第三遍闭眼默写最终方案7. 企业真题改编案例7.1 电商用户行为分析某大厂真题改编计算每个用户的首次购买后30天内的复购率WITH first_purchases AS ( SELECT user_id, MIN(purchase_date) AS first_date FROM purchases GROUP BY user_id ), repurchase_counts AS ( SELECT fp.user_id, COUNT(DISTINCT p.purchase_id) AS repurchase_num FROM first_purchases fp LEFT JOIN purchases p ON fp.user_id p.user_id AND p.purchase_date BETWEEN fp.first_date AND DATE_ADD(fp.first_date, INTERVAL 30 DAY) AND p.purchase_id ! (SELECT MIN(purchase_id) FROM purchases p2 WHERE p2.user_id fp.user_id) GROUP BY fp.user_id ) SELECT AVG(CASE WHEN repurchase_num 0 THEN 1 ELSE 0 END) AS repurchase_rate FROM repurchase_counts7.2 社交网络关系挖掘查找互相关注的用户对考察图数据处理能力SELECT DISTINCT LEAST(f1.user_id, f2.user_id) AS user1, GREATEST(f1.user_id, f2.user_id) AS user2 FROM follows f1 JOIN follows f2 ON f1.user_id f2.followee_id AND f1.followee_id f2.user_id WHERE f1.user_id f2.user_id8. 常见错误与调试技巧8.1 NULL值处理陷阱在计算员工奖金这类题目中要特别注意-- 错误写法NULL参与比较会导致结果异常 SELECT name, bonus FROM employee e LEFT JOIN bonus b ON e.empId b.empId WHERE bonus 1000 -- 正确写法 SELECT name, bonus FROM employee e LEFT JOIN bonus b ON e.empId b.empId WHERE IFNULL(bonus,0) 10008.2 日期边界问题活跃用户统计中常见的周计算错误-- 错误写法跨年周数计算异常 SELECT user_id, WEEK(activity_date) AS week_num FROM UserActivity -- 正确写法使用ISO周标准 SELECT user_id, WEEK(activity_date, 3) AS week_num FROM UserActivity9. 资源推荐与延伸学习除了LeetCode平台我还推荐这些训练资源SQLZoo交互式基础训练HackerRank分类题库练习Mode Analytics真实业务场景SQL案例《SQL进阶教程》窗口函数深度解析对于想深入理解执行原理的同学建议学习EXPLAIN命令解读研究不同数据库的优化器差异了解B树索引的工作原理掌握常见JOIN算法的实现机制最后提醒刷题时要刻意训练业务问题→SQL逻辑的转换能力这才是面试考察的核心。我习惯在解题时先画ER图再写伪代码最后转化为SQL这种方法在复杂业务场景下特别有效。