Skip to content

Instantly share code, notes, and snippets.

@jamesasu

jamesasu/bigquery-lag-window-partition.sql

Created June 16, 2021 23:05
Show Gist options
  • Save jamesasu/7364549c70d726eb746fdd3f5a0d5416 to your computer and use it in GitHub Desktop.
Save jamesasu/7364549c70d726eb746fdd3f5a0d5416 to your computer and use it in GitHub Desktop.
Download ZIP
Use the LAG() function to include details about the previous record in a series.
Raw
bigquery-lag-window-partition.sql
SELECT
ocr.ContactId,
ocr.OpportunityCloseDate,
ocr.OpportunityAmount,
LAG(ocr.OpportunityCloseDate, 1) OVER (PARTITION BY ocr.ContactId ORDER BY ocr.OpportunityCloseDate ASC) AS `Prev`
FROM
`OpportunityContactRoles` AS ocr
WHERE
ocr.OpportunityAmount > 0
ORDER BY
ocr.OpportunityCloseDate ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment