CODEWITHSUNDEEP
mysqlsqldatetimedatediff
gresell
gresell

Reputation: 37

DateDiff of dates in the same column from same group SQL

I need to get the differences in days between an order and the previous from same user

ORDERS TABLE:

customer_id    id       Activation_time
-----------------------------------------------------
1               1       2016-02-10 13:41:50.000
1               2       2018-03-10 18:42:45.000
1.              3       2019-05-18 15:55:04.000
.
.
.

I have no idea so any help will be really appreciate !!

Thanks in advance :)

Upvotes: 0

Views: 905

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use window functions. It looks like this:

select o.*,
       datediff(activation_time, lag(activation_time) over (partition by  customer_id order by activation_time) as Days_since_last_order
from orders o;

Note this will return NULL for the earliest activation. I'm not sure why your data has "2".

I prefer NULL for the earliest date, but if you want 0, you can use:

select o.*,
       datediff(activation_time,
                lag(activation_time, 1, activation_time) over (partition by  customer_id order by activation_time
               ) as Days_since_last_order
from orders o;

That is, add a default argument to LAG().

Upvotes: 2

Related Questions