calculating time between many dated in the same column with group by using MYSQL

Question

i have a data set which has the number id of customers and the dates of thier orders what i need to do is to calculate the number of days bewtween every order for each customer for example :

so i need to know the number of days between every order made for every customer . thanks for the help :)

Answer 1

here's how you will do this, you can use dense_rank()

MySQL 8.0

select t1.id, t1.order_date
            , ifnull(
                datediff(cast(t1.order_date as datetime), cast(t2.order_date as datetime))
                ,0) as intrval
    from (
            select dense_rank() over (partition by id order by cast(order_date as datetime) asc) as rnk
                , order_date, id 
            from table1) as t1
    left join 
     (
            select dense_rank() over (partition by id order by cast(order_date as datetime) asc) as rnk
                , order_date, id 
            from table1) as t2
    on t1.id = t2.id and t2.rnk+1 = t1.rnk

Output: