LAG

Syntax

LAG(expression [, offset [, default]]) OVER (PARTITION BY column ORDER BY column)

AMC specifics

• The offset defaults to 1 and the value outside the partition to NULL. An OVER clause with ORDER BY is required.
• LEAD works the same but looks at the following row.

Example

Change in impressions versus the previous day per campaign.

WITH daily AS (
  SELECT
    campaign,
    DATE_TRUNC('DAY', event_dt_utc) AS day,
    SUM(impressions) AS impressions
  FROM
    sponsored_ads_traffic
  GROUP BY
    1,
    2
)
SELECT
  campaign,
  day,
  impressions,
  impressions - LAG(impressions, 1, 0) OVER (PARTITION BY campaign ORDER BY day) AS change_vs_previous_day
FROM
  daily

Checked against the AMC schema, not executed on an AMC instance.