(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

score 0 · Accepted Answer · answered Jul 18 '21 at 11:42

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.

score 0 · Answer 2 · answered Jul 18 '21 at 13:58

In BigQuery, you can use a RANGE window specification. This only trick is that you need a number rather than a date:

select t.*,
       max(frequency) over (order by date_diff(date, date '2000-01-01', month)
                            range between 1 preceding and 1 preceding
                           ) as prev_frequence
from t;

The '2000-01-01' is an arbitrary date. This turns the date column into the number of months since that date. The actual date is not important.

(SQL BigQuery) Using Lag but data contains missing months

2 Answers2