LAG and LEAD Expressions Example: First and Most Expensive Purchases - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

Whenever a user visits the home page and then visits checkout pages and buys increasingly expensive products, the nPath query returns the first purchase and the most expensive purchase.

Input

The input table is a collection of clickstream data for different products with price information. Columns userid and sessionid identify the users.

aggregate_clicks
userid sessionid productname pagetype clicktime referrer productprice
1039 1 sneakers home 2009-07-29 20:17:59 Company1 100
1039 2 books home 2009-04-21 13:17:59 Company2 300
1039 3 television home 2009-05-23 13:17:59 Company3 500
1039 4 envelopes home 2009-07-16 11:17:59 Company4 10
1039 4 envelopes home1 2009-07-16 11:18:16 Company4 10
1039 4 envelopes page1 2009-07-16 11:18:18 Company4 10
1039 5 bookcases home 2009-08-19 22:17:59 Company5 150
1039 5 bookcases home1 2009-08-19 22:18:02 Company5 150
1039 5 bookcases page1 2009-08-19 22:18:05 Company5 150
1039 5 bookcases page2 2009-08-22 04:20:05 Company5 150
1039 5 bookcases checkout 2009-08-24 14:30:05 Company5 150
1039 5 bookcases page2 2009-08-27 23:03:05 Company5 150
1040 1 tables home 2009-07-29 20:17:59 Company5 250
1040 2 Appliances home 2009-04-21 13:17:59 Company6 1500
1040 3 laptops home 2009-05-23 13:17:59 Company7 800
1040 4 chairs home 2009-07-16 11:17:59 Company4 400
1040 4 chairs home1 2009-07-16 11:18:16 Company4 400
1040 4 chairs page1 2009-07-16 11:18:18 Company4 400
1040 5 cellphones home 2009-08-19 22:17:59 Company8 600
1040 5 cellphones home1 2009-08-19 22:18:02 Company8 600
1040 5 cellphones page1 2009-08-19 22:18:05 Company8 600
1040 5 cellphones page2 2009-08-22 04:20:05 Company8 600
1040 5 cellphones checkout 2009-08-24 14:30:05 Company8 600
1040 5 cellphones page2 2009-08-27 23:03:05 Company8 600
... ... ... ... ... ... ...

SQL Call

SELECT * FROM nPath@coprocessor (
  ON aggregate_clicks PARTITION BY sessionid
    ORDER BY clicktime ASC, productname, pagetype, userid
  USING
  Mode (NONOVERLAPPING)
  Pattern ('H+.D*.X*.P1.P2+')
  Symbols (
    'true' AS X,
    pagetype = 'home' AS H,
    pagetype <> 'home' AND pagetype <> 'checkout' AS D,
    pagetype = 'checkout' AS P1,
    pagetype = 'checkout' AND
    productprice > 100 AND
    productprice > LAG (productprice, 1, 100::REAL) AS P2
  )
  Result (
    FIRST (productname OF P1) AS first_product,
    MAX_CHOOSE (productprice, productname OF P2) AS max_product,
    FIRST (sessionid OF P2) AS sessionid
  )
) AS dt;

Output

first_product max_product sessionid
bookcases cellphones 5

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.