Translating query using LAG function from SQL Server to BigQuery

I had a working query on SQL Server which resulted in a table with columns having CPI values lagged and also one column having updated values every 6 months with a 4 month lag.

SELECT   *
        ,LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
        ,LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
        ,LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
        ,LAG (CPI, ((MONTH+8) % 6) + 4) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4

FROM SYSTEM_CPI

System_CPI table is a table having cpi values for each year, month [YEAR | MONTH | CPI]

When trying to do the same query in BigQuery, the LAG function will not accept the MONTH column as parameter. So the query gives an error. Is there an easy workaround for this?

DESIRED OUTPUT:

You may have to break-up your query into several unioned sub-queries to get around the restrictions of the offset which must be a a non-negative integer literal or parameter. refer

The formula ((MONTH+8) % 6) + 4 where month must be in range 1 to 12 gives a fixed result range of 4 to 9 like so:

select
  month, ((MONTH+8) % 6) + 4
from (
    select 1  as month union all select 2  as month union all select 3  as month union all select 4  as month union all
    select 5  as month union all select 6  as month union all select 7  as month union all select 8  as month union all
    select 9  as month union all select 10 as month union all select 11 as month union all select 12 as month
     ) x
order by 2, 1

fiddle

So arrange the sub-queries so that the offset can be an integer literal by using where clauses that choose the months to suit the needed offset.

SELECT
      *
    , LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
    , LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
    , LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
    , LAG (CPI, 4) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (4,10)
UNION ALL
SELECT
      *
    , LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
    , LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
    , LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
    , LAG (CPI, 5) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (5,11)
UNION ALL
SELECT
      *
    , LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
    , LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
    , LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
    , LAG (CPI, 6) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (6,12)
UNION ALL
SELECT
      *
    , LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
    , LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
    , LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
    , LAG (CPI, 7) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (1,7)
UNION ALL
SELECT
      *
    , LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
    , LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
    , LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
    , LAG (CPI, 8) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (2,8)
UNION ALL
SELECT
      *
    , LAG (CPI, 1) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_1
    , LAG (CPI, 2) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_2
    , LAG (CPI, 3) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_3
    , LAG (CPI, 9) OVER (ORDER BY YEAR, MONTH ASC) AS CPI_6M_LAG4
FROM SYSTEM_CPI
WHERE MONTH IN (3,9)
ORDER BY Year, Month