Calculate in a custom column by using subdate to get the previous day

Question

I am trying to use the subdate method in mysql but I can't get what I want...

Consider I have the below table :

Table:

id    created_at    value
1     2019-03-19    50
2     2016-03-20    100
3     2016-03-21    87

I would like to create a view which will display the 3 columns and add one custom which is basically the difference between the current value and the one at the previous day.

In the example, my view output should be:

id    created_at    value    diff
1     2019-03-19    50       0
2     2016-03-20    100      50
3     2016-03-21    87       -13

Thank you for your help

Answer 1

In MySQL 8.0, you can use lag():

select 
    t.*,
    coalesce(value - lag(value) over(order by created at), 0) diff
from mytable t

In earlier versions, you can self-join the table and use a not exists condition with a correlated subquery to get the previous record:

select
    t.*,
    coalesce(t.value - t1.value, 0) diff
from mytable t
left join mytable t1 
    on t1.created_at < t.created_at
    and not exists(
        select 1
        from mytable t2
        where t2.created_at < t.created_at and t2.created_at > t1.created_at
    )