LAG in Bigquery
Not what you think it is.
Let's create a simple table with order dates.
with prep as (
select 'Xbox' as product, '20201010' as orderdate
union all
select 'PS5', '20201011'
union all
select 'PS5', '20201012'
union all
select 'PS5', '20201013'
union all
select 'PC (master race)', '20201012'
union all
select 'PC (master race)', '20201013'
union all
select 'PC (master race)', '20201014'
union all
select 'PC (master race)', '20201015'
)
select * from prep
So the result is..
So to apply the Lag:
select *
,LAG(orderdate)
OVER (PARTITION BY product ORDER BY orderdate ASC) AS last_order_date
from prep
order by product asc
lagged as (
select *
,LAG(orderdate)
OVER (PARTITION BY product ORDER BY orderdate ASC) AS prev_order_date
from prep
order by product asc
),
minmaxed as (
select *
,min(orderdate) over (partition by product) as min_order
,max(orderdate) over (partition by product) as max_order
from lagged
)
Source:
https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#lag
