nPath Example | Teradata Vantage - nPath Example: Sessions that Checked at Least 3 Products - Teradata Vantage

This example finds the data for sessions that checked at least three products.

Modify the SQL call in nPath Example: Sessions that Checked 3-6 Products to find sessions where the user checked at least three products by changing the Pattern syntax element.

Input

• aggregate_clicks, as in LAG and LEAD Expressions Example: First and Most Expensive Purchases (under nPath Symbols)

SQL Call

SELECT * FROM NPath@coprocessor (
  ON aggregate_clicks PARTITION BY sessionid ORDER BY clicktime
  USING
    Mode(nonoverlapping)
    Pattern('H+.D*.C{3,}.D')
    Symbols(pagetype = 'home' AS H, pagetype='checkout' AS C,
            pagetype<>'home' AND pagetype<>'checkout' AS D)
    Result(first(sessionid of C) AS sessionid,
           max_choose(productprice, productname of C) AS most_expensive_product,
           max(productprice of C) AS max_price,
           min_choose(productprice, productname of C) AS least_expensive_product,
           min(productprice of C) AS min_price)
) AS dt;

Output

 sessionid most_expensive_product max_price least_expensive_product min_price 
 --------- ---------------------- --------- ----------------------- --------- 
         5 cellphones                 600.0 bookcases                   150.0

Download a zip file of all examples and a SQL script file that creates their input tables.