(SQL BigQuery) Using Lag but data contains missing months

Question

I have the following table with monthly data. But we do not have the third month.

DATE	FREQUENCY
2021-01-01	6000
2021-02-01	4533
2021-04-01	7742
2021-05-01	1547
2021-06-01	9857

I want to get the frequency of the previous month into the following table.

DATE	FREQUENCY	PREVIOUS_MONTH_FREQ
2021-01-01	6000	NULL
2021-02-01	4533	6000
2021-04-01	7742	NULL
2021-05-01	1547	7742
2021-06-01	9857	1547

I want the 2021-04-01 record to have NULL for the PREVIOUS_MONTH_FREQ since there is no data for the previous month.

I got so far as...

SELECT DATE, 
       FREQUENCY, 
       LAG(FREQUENCY) OVER(ORDER BY DATE) AS PREVIOUS_MONTH_FREQ
FROM Table1

Answer 1

Use a CASE expression to check if the previous row contains data of the previous month:

SELECT DATE, 
       FREQUENCY, 
       CASE WHEN DATE_SUB(DATE, INTERVAL 1 MONTH) = LAG(DATE) OVER(ORDER BY DATE)
            THEN LAG(FREQUENCY) OVER(ORDER BY DATE)
       END AS PREVIOUS_MONTH_FREQ
FROM Table1 

See the demo.