Add column with yesterday sale information on a daily sales database

I’m working with a database that contains daily sales information of different products and stores.

StoreSku  Date         UnitsSale
 A-2134   20/04/2019      2
 A-2135   20/04/2019      1
 A-2134   19/04/2019      5
 A-2134   18/04/2019      3
 A-2135   19/04/2019      6

i want to add a column with one day ago unit sales information

StoreSku  Date         UnitsSale    OneDaySales
 A-2134   20/04/2019      2             5
 A-2135   20/04/2019      1             6
 A-2134   19/04/2019      5             3
 A-2134   18/04/2019      3            NULL
 A-2135   19/04/2019      6            NULL

i’ve got to be honest, i don’t know if sql is able to achieve this. If not, could you please recommend a way? considering there are over thousands of records.

Answer

You are probably looking for lag():

select t.*,
       lag(UnitsSale) over (partition by StoreSku order by Date) as prev_unitsSale      
from t;

Alternatively, you can use a left join with date arithmetic. That varies by database, but the standard would be:

select t.*, tprev.UnitsSale as prev_unitssale
from t left join
     t tprev
     on tprev.StoreSku = t.StoreSku and
        tprev.Date = t.date - interval '1 day';

These are not exactly equivalent. The first will get the previous day in the data. The second will get the previous day — with NULL for the day after missing days.