CODEWITHSUNDEEP
mysqlpostgresqldate
Quinn Olive
Quinn Olive

Reputation: 57

Interval addition onto date

I'm going to attempt to explain the logic and hopefully someone can understand and help me.

Effectively I'm looking for people within my database that have stopped transacting within the first 120 days of first creating their account, but have been inactive for 120 days since their last transaction.

Basically if someone transacts for 120 days and then stops and 3 years later they transact again, I need them to fall into this list. So using max(transaction.created_at) isn't going to work.

Hopefully I've explained myself correctly.

Upvotes: 1

Views: 104

Answers (1)

AdrianBR
AdrianBR

Reputation: 2588

I assume you have a log of the type

table transaction
    user; Timestamp

first step is sorting the correct sequence

select t.*, 
@curRow := @curRow + 1 AS row_number 
from transaction t 
JOIN  (SELECT @curRow := 0) r 
order by user, timestamp

result

user, timestamp, row_id
1       t1           1
1       t1+x         2
...

next step is to join consecutive actions by the same user

select * from
    (select t.*, 
    @curRow := @curRow + 1 AS row_number 
    from transaction t 
    JOIN  (SELECT @curRow := 0) r 
    order by user, timestamp) a
inner join
    (select t.*, 
    @curRow := @curRow + 1 AS row_number 
    from transaction t 
    JOIN  (SELECT @curRow := 0) r 
    order by user, timestamp)b
on a.user=b.user and a.row_id=b.row_id-1

Result:

user timestamp row user timestamp row
 1      t1      1    1     t1+x    2
 2      t1+x    2    1    t1+x+x2  3
...

now you just need to filter by time span between events

 select * from
        (select t.*, 
        @curRow := @curRow + 1 AS row_number 
        from transaction t 
        JOIN  (SELECT @curRow := 0) r 
        order by user, timestamp) a
    inner join
        (select t.*, 
        @curRow := @curRow + 1 AS row_number 
        from transaction t 
        JOIN  (SELECT @curRow := 0) r 
        order by user, timestamp)b
    on a.user=b.user and a.row_id=b.row_id+1
WHERE datediff(b.timestamp, a.timestamp)>120

now you have a lsit of users that had a break longer than 120 days between transactions if you need this to happen within first few days from creating an acc, just add where user in(select user from .... where datediff(min(timestamp, creation_Date)<120) or do an inner join on user_id to filter by that subquery

Upvotes: 1

Related Questions