what is wrong with this MYSQL query? Amazon Interview question-CodePudding

Prompt Question
Write a query that'll identify returning active users. A returning active user is a user that has made a second purchase within 7 days of any other of their purchases. Output a list of user_ids of these returning active users.

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

My solution is as follows. I'm getting a partially right answer. There are many user ids that should not be there. Can someone tell me what's wrong with my solution?

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
    );

CodePudding user response：

Here is a way to solve the problem

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
                 )

CodePudding user response：

Use

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
    );