nPath LAG and LEAD Expressions Example | Teradata Vantage - LAG and LEAD Expressions Example: Alias for Input Query - Analytics Database

Input

SQL Call

SELECT * FROM nPath (
  ON (SELECT customer_id, session_id, datestamp, page FROM bank_web_clicks) AS dt1
    PARTITION BY customer_id, session_id
    ORDER BY datestamp
  USING
  Mode (NONOVERLAPPING)
  Pattern ('(DUP|A)*')
  Symbols (
    TRUE AS A,
    page = LAG (page,1) AS DUP
  )
  Result (
    FIRST (customer_id OF any (A)) AS customer_id,
    FIRST (session_id OF A) AS session_id,
    FIRST (datestamp OF A) AS first_date,
    LAST (datestamp OF ANY(A,DUP)) AS last_date,
    ACCUMULATE (page OF A) AS page_path,
    ACCUMULATE (page of DUP) AS dup_path
  )
) AS dt2;

Output