这个MYSQL查询出了什么问题?亚马逊访谈问题

Question

提示问题
编写一个查询来识别返回的活动用户。返回的活动用户是指在任何其他购买后7天内进行第二次购买的用户。输出这些返回的活动用户的user_id列表。

CREATE TABLE amazon_transactions(
    id         int,
    user_id    int,
    itemvar    char,
    created_at datetime,
    revenue    int
)

我的解决方案如下。我得到了一个部分正确的答案。有许多用户ID不应该存在有人能告诉我我的解决方案出了什么问题吗

select
    user_id
from
    amazon_transactions t1
where
    7 < ANY(
        select
            ABS( DATEDIFF( t1.created_at, t2.created_at ) ) 
        from
            amazon_transactions t2 
        where
            t2.user_id = t1.user_id
    );

Answer 1

以下是解决问题的方法

select DISTINCT user_id
  from amazon_transaction a
where exists (select null
                from amazon_transaction b
               where a.user_id=b.user_id
                 and a.id <> b.id 
                 and abs(datediff(b.created_at,a.created_at))<=7
                 )

Answer 2

使用

select
    t1.user_id
from
    amazon_transactions t1
join amazon_transactions t2
on t1.user_id = t2.user_id and ABS(DATEDIFF(t1.created_at, t2.created_at )) <= 7 and t1.id <> t2.id

或

select
    user_id
from
    amazon_transactions t1
where
    7 >= ANY(
        select
            ABS( DATEDIFF( t1.created_at, t2.created_at ) ) 
        from
            amazon_transactions t2 
        where
            t2.user_id = t1.user_id
    );

Answer 3

SELECT DISTINCT user_id
FROM amazon_transaction t1
WHERE EXISTS ( SELECT NULL
               FROM amazon_transaction t2
               WHERE t1.id < t2.id
                 AND t1.user_id = t2.user_id
                 AND t2.created_at < t1.created_at + INTERVAL 7 DAY )

这项任务有点模棱两可。也许您必须调整查询-例如，使用t1.id <> t2.id、t2.created_at <= t1.created_at、INTERVAL 6 DAY。。。