16.10 - Example 7: Find Data for Sessions That Checked Out at Least 3 Products - Teradata Database

SQL-MapReduce Call

SELECT * FROM NPATH (
   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 ORDER BY dt.sessionid;

Output