亚马逊SQL求职面试问题:购买2件以上的客户

Question

您有一个只有两个字段的简单表：CustomerID、DateOfPurchase。列出在六个月的任何一段时间内至少进行了两次购买的所有客户。您可以假设该表包含过去10年的数据。此外，没有PK或唯一值。

我的朋友已经得到了这份工作，尽管他不能回答这个问题。我很好奇这类问题是怎么解决的。

感谢

Answer 1

• 从抽象的角度来看，这个问题是关于有效地自联接一个没有PK或唯一标识符的表
• 正如你所看到的，这是非常棘手的，可能会出现这样的情况
  • 一个客户在6个月内恰好进行了2次购买，而且也是在同一日期(这看起来像是重复记录(
  • 客户制作>6个月内在不同日期购买2次(通常情况下(
• 这里需要做的一件事是生成一个列，该列可以像唯一标识符一样操作，这可以在这里使用row_number实现
• 在拥有唯一标识符后，可以很容易地根据您的要求加入和来自第一个别名的唯一标识符！=第二个别名的唯一标识符(意味着连接两个别名的所有行，但同一行除外，同一行！=具有相同数据的不同行与第一种情况相同(
• 将所有这些放在一起，可以使用
  • 公共表表达式，从包含手动添加的唯一标识符的单个数据源开始，然后执行所需的业务
  • row_number，它帮助我们将该唯一标识符分配给在公共表表达式中生成的单个数据源
• 有关技术详细信息，请参阅以下查询

with tempPurchase as (
select *, 
row_number() over (order by CustomerID) as rowNumber -- this is crucial part
from purchase
)
select distinct(tp1.CustomerID) from tempPurchase as tp1 
join tempPurchase as tp2 on tp1.CustomerID = tp2.CustomerID 
and tp1.DateOfPurchase >= tp2.DateOfPurchase
and tp1.DateOfPurchase <= DATEADD(month, 6, tp2.DateOfPurchase)
and tp1.rowNumber != tp2.rowNumber; -- this is crucial part

• 有关完整的工作解决方案，请参阅此处的db fiddle

Answer 2

我们可以尝试在这里使用exists逻辑来检测6个月内发生的同一客户的记录。然后，找到不同的客户，这意味着任何这样匹配的客户至少有两次购买。

SELECT DISTINCT CustomerID
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
WHERE t2.CustomerID = t1.CustomerID AND
t2.DateOfPurchase > t1.DateOfPurchase AND
t2.DateOfPurchase <= DATEADD(month, 6, t1.DateOfPurchase));

请注意，这个答案假设给定客户每天最多只能进行一次不同的购买。更好的方法是：

SELECT DISTINCT CustomerID
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
WHERE t2.CustomerID = t1.CustomerID AND
t2.PK <> t1.PK AND
t2.DateOfPurchase >= t1.DateOfPurchase AND
t2.DateOfPurchase <= DATEADD(month, 6, t1.DateOfPurchase));

上述查询的意思是，为每个客户查找6个月内两条不同购买记录之间的任何关系。这假设该表有一个PK主键列。理想情况下，每个表都应该有某种逻辑主键。

Answer 3

试试这个：

SELECT distinct CustomerID
FROM purchase t1
WHERE 1 < (SELECT count(1) FROM purchase t2
WHERE t2.CustomerID = t1.CustomerID AND
t2.DateOfPurchase >= t1.DateOfPurchase AND
t2.DateOfPurchase <= DATEADD(month, 6, t1.DateOfPurchase))

想法是从外表t1中选择一条记录，并在内表t2中检查是否在6个月内进行了任何购买，包括您从外表中选择的那条。如果来自子查询的计数大于1，则我们有符合条件的客户。