(SQL BigQuery) Using Lag but data contains missing months

user_16438416 0
Viewed 423

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
sql-date-functions google-bigquery lag sql
2 Answers

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.

user_104988280

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.

user_11440350
Related