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

Teradata Analytic Functions

prodname
Teradata Database
vrm_release
15.10
16.10
created_date
November 2017
category
Programming Reference
featnum
B035-1206-151K
Modify the previous query call in Example 6 to find sessions where the user checked out at least three products by changing the Pattern argument to:
PATTERN('H+.D*.C{3,}.D')

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

nPath Range-Matching Example 7 Output Table
sessionid most_expensive_product max_price least_expensive_product min_price
5 cellphones 600 bookcases 150