Get Data for two dates in one row

Question

Please consider the following data-set.

Account No	Month End Date	Balance
123960	31-Dec-2021	1000
123960	31-Jan-2022	1500
123960	28-Feb-2022	1700
123960	31-Mar-2022	2100
123960	30-Apr-2022	1900

Can you please advise me the best possible way to get the following output to compare one month with the previous one.

Account No	Month End Date	Balance	Previous Month Balance
123960	31-Dec-2021	1000	-
123960	31-Jan-2022	1500	1000
123960	28-Feb-2022	1700	1500
123960	31-Mar-2022	2100	1700
123960	30-Apr-2022	1900	2100

Thank you so much for your help.

Answer 1

you can use the LAG function to do that, example:

create table acounts (
account_no number,
month_end_date DATE, balance number);


insert into acounts values(123960,  '31-Dec-2021',  1000)
insert into acounts values(123960,  '31-Jan-2022',  1500);
insert into acounts values(123960,  '28-Feb-2022',  1700);



sELECT 
    account_no,
    month_end_date, 
    balance,
    LAG(balance) OVER (
        ORDER BY month_end_date
    ) py_sales
FROM 
    acounts
    where account_no = 123960