CODEWITHSUNDEEP
mysqlsqldatediff
Praneeth Vasarla
Praneeth Vasarla

Reputation: 123

How to find Rolling Date difference in MySql?

I have a table named "orders"

Orders:

orderid   userid   order_date
10         1001    2020-07-11
11         1001    2020-07-13  
12         1002    2020-07-15
13         1002    2020-07-17
14         1003    2020-07-20
15         1003    2020-07-24

I'm trying to achieve the following:

orderid   userid   order_date     date_differnce_between_orders
10         1001    2020-07-11             null
11         1001    2020-07-13             2
12         1002    2020-07-15             null
13         1002    2020-07-18             3
14         1003    2020-07-20             null
15         1003    2020-07-25             5

So basically I want to calculate date difference for each user separately so that I could further calculate the average number of days a user takes to place an order.

Can somebody help with the "date_differnce_between_orders" column?

Upvotes: 1

Views: 936

Answers (2)

Ravi
Ravi

Reputation: 2281

Try the below query:

SELECT orderid, userid, order_date,
(CASE 
    WHEN @lastuserid = userid 
    THEN DATEDIFF(order_date, @date) 
    ELSE NULL END
) AS date_differnce_between_orders,
@lastuserid:=userid,
@date:= (CASE WHEN @lastuserid = userid THEN order_date ELSE NULL END)
FROM orders, (SELECT @date:=0, @lastuserid:=0) AS x
ORDER BY userid, order_date

Upvotes: 0

forpas
forpas

Reputation: 164064

For MySql 8.0+ you can use window function LAG():

select *,
  datediff(
    order_date,
    lag(order_date) over (partition by userid order by order_date)
  ) date_differnce_between_orders 
from orders
order by orderid;

For previous versions use a correlated subquery that returns the date of the previous order of each user:

select o.*,
  datediff(
    o.order_date,
    (select max(order_date) from orders where userid = o.userid and order_date < o.order_date)
  ) date_differnce_between_orders 
from orders o
order by o.orderid;

See the demo.
Results:

> orderid | userid | order_date | date_differnce_between_orders
> ------: | -----: | :----------| ----------------------------:
>      10 |   1001 | 2020-07-11 |                          null
>      11 |   1001 | 2020-07-13 |                             2
>      12 |   1002 | 2020-07-15 |                          null
>      13 |   1002 | 2020-07-18 |                             3
>      14 |   1003 | 2020-07-20 |                          null
>      15 |   1003 | 2020-07-25 |                             5

Upvotes: 1

Related Questions