MySQL get previous day data

I have the following table

Date       | amount 1    | 
-----------|-------------|
2020-01-01 | 100         | 
2020-01-02 | 120         | 
2020-01-03 | 150         | 

What I try to get is writing the day before data on the following day

Date       | amount 1    | amount 2 |
-----------|-------------|----------|
2020-01-01 | 100         | 0        |
2020-01-02 | 120         | 100      |
2020-01-03 | 150         | 120      |

I can get yesterday but don’t know how to do it for all rows.

Thanks,

Answer

You can use next approach.

select 
    test.date1, 
    test.amount1,  
    ifnull(yestarday_test.amount1, 0) as amount2
from test
left join test yestarday_test on 
    date_sub(yestarday_test.date1, interval -1 day ) = test.date1
order by test.date1 asc
;

In this query we use join same table to itself by date with 1 day shift.

DB Fiddle