Amazon BIE面试SQL问题:查询2019、2020年各超3单客户的代码为何不通过测试
代码无法通过测试用例的核心问题
- 输出字段名不符合要求:题目明确规定输出字段为
customer_name,当前代码最终输出的字段别名是Name1,测试用例做字段名校验时会直接判定不通过。 - 关联匹配维度错误:代码将
name作为两年交易集合的关联匹配条件,但name是可重复的非唯一字段——现实场景中大量同名用户完全可能被错误匹配,把分属不同用户的两年交易误判为同一用户的连续交易,导致统计结果完全失真,正确的匹配维度应该是用户唯一主键user_id。 - 表连接逻辑无效且有漏洞:代码对两表做LEFT JOIN,但目标是识别有效客户的交易,关联后没有过滤
u.id为空的脏交易(即不存在对应用户的无效流水),这类记录本来就不该纳入客户交易统计;LEFT JOIN在这里既无法起到保留交易的作用,还会引入统计噪声。 - 语法兼容性问题:代码混用双引号、单引号给列起别名,比如
u.name AS 'Name1',在亚马逊面试常用的数仓引擎(如Redshift、BigQuery)中,单引号仅用于标识字符串字面量,不能作为列别名的语法标识,部分场景下会直接触发语法报错。 - 交易笔数统计逻辑不严谨:代码用
count(user_id)统计交易数,当存在user_id为空的流水时,该写法会自动排除这类记录,但统计交易数最严谨的口径是统计交易表的非空主键transactions.id,避免因为字段空值导致的计数偏差。
可通过测试的参考写法
WITH eligible_yearly_trade AS ( SELECT u.id AS user_id, u.name AS customer_name, EXTRACT(YEAR FROM t.created_at) AS trade_year, COUNT(t.id) AS trade_total FROM transactions t INNER JOIN users u ON t.user_id = u.id WHERE EXTRACT(YEAR FROM t.created_at) IN (2019, 2020) GROUP BY u.id, u.name, EXTRACT(YEAR FROM t.created_at) HAVING COUNT(t.id) > 3 ) SELECT customer_name FROM eligible_yearly_trade GROUP BY user_id, customer_name HAVING COUNT(DISTINCT trade_year) = 2;
说明:上面的写法先筛出每一年交易超过3笔的用户明细,再筛选同时覆盖2019、2020两个年份的用户,既避免了重复关联全表的性能问题,也不会因为同名用户导致匹配错误,输出字段也完全符合题目要求。
内容的提问来源于stack exchange,提问作者Ariel Zhao
相关产品推荐
相关产品推荐

