nPath Example: Sessions that Checked 3-6 Products - Teradata Vantage

This example finds the data for sessions that checked three to six products.

For sessions where the user checked between three and six products (exclusive), return the names of the most and least expensive products, the maximum price of the most expensive product, and the minimum price of the least expensive product.

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,6}.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 from the attachment in the left sidebar.