Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table_name (Sales_Date, Sales_Value) AS
SELECT DATE '2019-04-01',  20 FROM DUAL UNION ALL
SELECT DATE '2019-05-01',  50 FROM DUAL UNION ALL
SELECT DATE '2019-06-01',  60 FROM DUAL UNION ALL
SELECT DATE '2019-07-01',  10 FROM DUAL UNION ALL
SELECT DATE '2019-08-01',  20 FROM DUAL UNION ALL
SELECT DATE '2019-09-01',  10 FROM DUAL UNION ALL
SELECT DATE '2020-04-01',  70 FROM DUAL UNION ALL
SELECT DATE '2020-05-01', 100 FROM DUAL UNION ALL
SELECT DATE '2020-06-01',  10 FROM DUAL UNION ALL
SELECT DATE '2020-07-01',  10 FROM DUAL UNION ALL
SELECT DATE '2020-08-01',  20 FROM DUAL UNION ALL
SELECT DATE '2020-09-01',  20 FROM DUAL;
12 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT sales_date,
       sales_value,
       COALESCE(
         MAX(sales_value) OVER (
           ORDER BY sales_date
           RANGE BETWEEN INTERVAL '1' MONTH PRECEDING
                     AND INTERVAL '1' MONTH PRECEDING
         ),
         0
       ) AS last_month_sales,
       COALESCE(
         MAX(sales_value) OVER (
           ORDER BY sales_date
           RANGE BETWEEN INTERVAL '12' MONTH PRECEDING
                     AND INTERVAL '12' MONTH PRECEDING
         ),
         0
       ) AS last_year_sales
FROM   table_name


SALES_DATE
SALES_VALUE
LAST_MONTH_SALES
LAST_YEAR_SALES


2019-04-01 00:00:00
20
0
0

2019-05-01 00:00:00
50
20
0

2019-06-01 00:00:00
60
50
0

2019-07-01 00:00:00
10
60
0

2019-08-01 00:00:00
20
10
0

2019-09-01 00:00:00
10
20
0

2020-04-01 00:00:00
70
0
20

2020-05-01 00:00:00
100
70
50

2020-06-01 00:00:00
10
100
60

2020-07-01 00:00:00
10
10
10

2020-08-01 00:00:00
20
10
20

2020-09-01 00:00:00
20
20
10

SALES_DATE	SALES_VALUE	LAST_MONTH_SALES	LAST_YEAR_SALES
2019-04-01 00:00:00	20	0	0
2019-05-01 00:00:00	50	20	0
2019-06-01 00:00:00	60	50	0
2019-07-01 00:00:00	10	60	0
2019-08-01 00:00:00	20	10	0
2019-09-01 00:00:00	10	20	0
2020-04-01 00:00:00	70	0	20
2020-05-01 00:00:00	100	70	50
2020-06-01 00:00:00	10	100	60
2020-07-01 00:00:00	10	10	10
2020-08-01 00:00:00	20	10	20
2020-09-01 00:00:00	20	20	10