nPath Example 7: Find Data for Sessions that Checked at Least 3 Products - Teradata Vantage

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

Input

• aggregate_clicks, as in LAG and LEAD Expressions Example 2 (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