Skip to content

Instantly share code, notes, and snippets.

@ejangi

ejangi/bigquery-lag-window-partition.sql

Created June 16, 2021 23:08
Show Gist options
  • Save ejangi/416aacf9d7b64178b0219757e37fb102 to your computer and use it in GitHub Desktop.
Save ejangi/416aacf9d7b64178b0219757e37fb102 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