LAG() Function Error Within BigQuery SQL; Unable to return lagged hits.page.pagePath

Question

I am currently trying to create a column within a BigQuery table that simply includes the lagged values of the hits.page.pagePath. For this example, imagine that I have hits.time values of 0, 513, 518, 762, 991. These values correspond to a unique_visit_id which I created. There are thousands of unique visits per day, each with different hits.time values and row numbers. To help with a variety of calculations, I want to add a column to the data frame that simply includes the lagged value for all rows in the hits.page.pagePath column (with there being no lag associated when hits.time > 0). Note that the hits.time is a repeated group that refers to a unique visit id which is calculated using `CONCAT(fullVisitorId, STRING(visitId)) AS unique_visit_id`. Thus, I suspect that a command like OVER would be involved. My initial query used the following approach: ``` SELECT date, LAG(hits.page.pagePath,1), CONCAT(fullVisitorId, STRING(visitId)) AS unique_visit_id, visitId, visitNumber, fullVisi…

Accepted Answer

Analytic functions have an odd syntax... you're almost there, but you just need to move the `ORDER BY` clause into the `OVER` statement used by `LAG()`:

```
SELECT date, LAG(hits.page.pagePath,1) 
  OVER(PARTITION BY unique_visit_id ORDER BY hits.time ASC) as lagged, 
  CONCAT(fullVisitorId, STRING(visitId)) AS unique_visit_id, visitId, 
  ...
FROM (FLATTEN([XXXXXXXX.ga_sessions_20140711], hits.time))
WHERE hits.customVariables.index = 4
LIMIT 1000;
```

LAG() Function Error Within BigQuery SQL; Unable to return lagged hits.page.pagePath

Solution

Problem